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

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Why is the comparison between timestamp and date so much slower then between two dates
Date: 2016-04-13 12:16:50
Message-ID: neldbi$d40$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2016-04-13 13:45:22 Re: Why is the comparison between timestamp and date so much slower then between two dates
Previous Message Alex Ignatov 2016-04-13 11:17:03 Re: Freezing localtimestamp and other time function on some value