2

Test table and indexes (PostgreSQL 12.1):

CREATE TABLE t (dt timestamp with time zone);
CREATE INDEX ind ON t USING btree (dt);

INSERT
INTO t(dt)
    SELECT
    (
        timestamp '2020-01-01 00:00:00' +
        random() * (
            timestamp '2020-02-29 00:00:00' -
            timestamp '2020-01-01 00:00:00'
        )
    )
    FROM generate_series(1, 10000)

In the first query, everything is ok, appropriate index "ind" is used:

explain (analyze, buffers)
SELECT *
FROM t
WHERE
   ('2020-02-08')::date IS NULL
   OR
   dt > '2020-02-08'
ORDER BY dt
LIMIT 1

"Limit  (cost=0.29..0.37 rows=1 width=8) (actual time=0.186..0.188 rows=1 loops=1)"
"  Buffers: shared hit=3"
"  ->  Index Only Scan using ind on t  (cost=0.29..303.75 rows=3627 width=8) (actual time=0.184..0.184 rows=1 loops=1)"
"        Index Cond: (dt > '2020-02-08 00:00:00+05'::timestamp with time zone)"
"        Heap Fetches: 1"
"        Buffers: shared hit=3"
"Planning Time: 2.365 ms"
"Execution Time: 0.239 ms"

Why with '::text' type casting of '2020-02-08' (which is already text) query perfomance is very low

explain (analyze, buffers)
SELECT *
FROM t
WHERE
   ('2020-02-08'::text)::date IS NULL
   OR
   dt > '2020-02-08'
ORDER BY dt
LIMIT 1

"Limit  (cost=0.29..0.44 rows=1 width=8) (actual time=45.306..45.307 rows=1 loops=1)"
"  Buffers: shared hit=6232"
"  ->  Index Only Scan using ind on t  (cost=0.29..561.28 rows=3658 width=8) (actual time=45.304..45.304 rows=1 loops=1)"
"        Filter: ((('2020-02-08'::cstring)::date IS NULL) OR (dt > '2020-02-08 00:00:00+05'::timestamp with time zone))"
"        Rows Removed by Filter: 6367"
"        Heap Fetches: 6368"
"        Buffers: shared hit=6232"
"Planning Time: 0.348 ms"
"Execution Time: 45.343 ms"
Dima Pavlov
  • 115
  • 9
  • When is a non null constant ever null? It looks like pg realizes it isn't in the first case and omits all the data type conversions from the filter, but in the second case it doesn't (because of the double conversion?) and hence does a large amount of data conversion, which will introduce some extra work. What sort of milliseconds slow down would you have deemed acceptable? – Caius Jard Feb 08 '20 at 07:26
  • @CaiusJard if rewrite the whole example with int column instead of timestamp then both queries work well, so problem is not double conversion - ::text::int double conversion works fine. – Dima Pavlov Feb 08 '20 at 07:42
  • I think you are committing logical fallacy of [False equivalence](https://en.wikipedia.org/wiki/List_of_fallacies). You contend that double conversion ::text::int works fine therefore ::text::date does also. But these conversion are NOT the same. – Belayer Feb 08 '20 at 19:42

0 Answers0