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
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 |