The following bug has been logged online:
Bug reference: 5293
Logged by: Richard Neill
Email address: rn...@cam.ac.uk
PostgreSQL version: 8.4.2
Operating system: Linux
Description: constant function (date_trunc) is repeatedly evaluated
inside loop
Details:
SUMMARY
-------
If I have a WHERE clause such as this:
WHERE srep_timestamp >= date_trunc('day', timestamp '2010-01-20 10:16:55')
...
then I'd expect the query planner to evaluate the constant function
date_trunc('day', timestamp '2010-01-20 10:16:55')
once, outside the loop.
However, it doesn't do this.
As a result, the query time doubles from 160ms to 340ms
compared to:
WHERE srep_timestamp >= '2010-01-20 00:00:00') ...
DETAILS
-------
Here are some actual results from a 250k row table.
srep_timestamp has times roughly linearly distributed over a 2 day period
(with about 20% nulls).
There is an index tbl_tracker_srepz_timestamp_idx on srep_timestamp WHERE
srep_timestamp is not null.
The measured times are consistent and repeatable.
SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= '2010-01-20
00:00:00') AND (srep_timestamp < '2010-01-21 00:00:00') );
count
--------
198577
(1 row)
Time: 158.084 ms
SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >= date_trunc('day',
timestamp '2010-01-20 10:16:55') AND srep_timestamp < date_trunc('day',
timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' )) ;
count
--------
198577
(1 row)
Time: 341.155 ms
explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
'2010-01-20 00:00:00') AND (srep_timestamp < '2010-01-21 00:00:00') );
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------
Aggregate (cost=3181.17..3181.18 rows=1 width=0) (actual
time=663.651..663.652 rows=1 loops=1)
-> Bitmap Heap Scan on tbl_tracker (cost=29.39..3177.97 rows=1279
width=0) (actual time=101.197..396.428 rows=198577 loops=1)
Recheck Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx
(cost=0.00..29.07 rows=1279 width=0) (actual time=98.417..98.417 rows=198577
loops=1)
Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00+00'::timestamp with time zone) AND (srep_timestamp < '2010-01-21
00:00:00+00'::timestamp with time zone))
Total runtime: 663.769 ms
(6 rows)
Time: 665.087 ms
explain analyze SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp '2010-01-20 10:16:55') AND srep_timestamp <
date_trunc('day', timestamp '2010-01-20 10:16:55') + INTERVAL '24 hour' ))
;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------
Aggregate (cost=3181.17..3181.18 rows=1 width=0) (actual
time=827.424..827.425 rows=1 loops=1)
-> Bitmap Heap Scan on tbl_tracker (cost=29.39..3177.97 rows=1279
width=0) (actual time=276.367..563.503 rows=198577 loops=1)
Recheck Cond: ((srep_timestamp >= '2010-01-20 00:00:00'::timestamp
without time zone) AND (srep_timestamp < '2010-01-21 00:00:00'::timestamp
without time zone))
-> Bitmap Index Scan on tbl_tracker_srepz_timestamp_idx
(cost=0.00..29.07 rows=1279 width=0) (actual time=275.020..275.020
rows=198577 loops=1)
Index Cond: ((srep_timestamp >= '2010-01-20
00:00:00'::timestamp without time zone) AND (srep_timestamp < '2010-01-21
00:00:00'::timestamp without time zone))
Total runtime: 827.534 ms
(6 rows)
Time: 828.763 ms
Thanks very much - Richard
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
