BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop

The following bug has been logged online:

Bug reference: 5293
Logged by: Richard Neill
Email address: rn214(at)cam(dot)ac(dot)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