BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      5293
Logged by:          Richard Neill
Email address:      rn214@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