[GENERAL] Why is the comparison between timestamp and date so much slower then between two dates

[Thomas Kellerer]

I came across something strange today.

Consider the following table:

    CREATE TABLE price_history
    (
        product_id integer,
        valid_from date,
        valid_to date,
        price integer
    );

    CREATE INDEX i1 ON price_history (product_id, valid_from, valid_to);

The table contains 5 million rows and 5000 distinct product_ids 
To get the current price for each product I used the following query: 

   select *
   from price_history
   where current_date between valid_from and valid_to;

The execution plan is not really surprising:

    Index Scan using i1 on public.price_history  (cost=0.44..61980.61 rows=5133 
width=16) (actual time=0.177..527.693 rows=5000 loops=1)
      Output: product_id, valid_from, price, valid_to
      Index Cond: ((('now'::cstring)::date >= ph.valid_from) AND 
(('now'::cstring)::date <= ph.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.395 ms
    Execution time: 528.193 ms

Now I tried the same query using now() instead of current_date, which shows 
exactly the same execution plan, but is 3 times slower:

    Index Scan using i1 on public.price_history  (cost=0.44..61980.60 rows=5133 
width=16) (actual time=0.406..1902.241 rows=5000 loops=1)
      Output: product_id, valid_from, price, valid_to
      Index Cond: ((now() >= ph.valid_from) AND (now() <= ph.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.615 ms
    Execution time: 1902.777 ms

The above plans were taken on my Windows laptop with Postgres 9.5.1

On a CentOS server with 9.5.0 I can see the same difference:

Plan using now()

    Index Scan using i1 on public.price_history  (cost=0.44..110570.50 
rows=44944 width=16) (actual time=0.182..837.903 rows=5000 loops=1)
      Output: product_id, valid_from, valid_to, price
      Index Cond: ((now() >= price_history.valid_from) AND (now() <= 
price_history.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.106 ms
    Execution time: 838.529 ms

Plan using current_date:

    Index Scan using i1 on public.price_history  (cost=0.44..110570.51 
rows=44944 width=16) (actual time=0.052..180.856 rows=5000 loops=1)
      Output: product_id, valid_from, valid_to, price
      Index Cond: ((('now'::cstring)::date >= price_history.valid_from) AND 
(('now'::cstring)::date <= price_history.valid_to))
      Buffers: shared hit=24160
    Planning time: 0.115 ms
    Execution time: 181.226 ms

(I don't know why the estimates on the CentOS installation are so different 
from the one on my laptop given that both tables contain exactly the same data 
and were analyzed properly before running explain plan - but that is a 
different question). 

So my question is: why is comparing a timestamp to a date so much slower?

Thomas





-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general