Thread: 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@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

"Richard Neill" <rn214@cam.ac.uk> wrote:

> date_trunc('day', timestamp '2010-01-20 10:16:55')

What happens with a "timestamp with time zone" literal?

-Kevin

Richard Neill <rn214@cam.ac.uk> wrote:

> #fast
> WHERE column <  '2010-010-20 00:00:00'
>
> #fast
> WHERE column <  date_trunc('day', timestamp with time zone
>                               '2010-01-20 10:16:55')
>
> #slow
> WHERE column <  date_trunc('day', timestamp
>                               '2010-01-20 10:16:55')
>
>
> Why is that, I wonder?

The column is timestamp with time zone, and the fast options
generate a value of the same type (the first because the literal is
treated as UNKNOWN type until the comparison).  It's generally a
good idea to use literals which match the type of the column.  How
much work it would be to optimize the slow case to insert a cast of
the date_trunc function return value to a different type before
entering the loop where the value is tested, I don't know offhand.

-Kevin

Kevin Grittner wrote:
> "Richard Neill" <rn214@cam.ac.uk> wrote:
>
>> date_trunc('day', timestamp '2010-01-20 10:16:55')
>
> What happens with a "timestamp with time zone" literal?
>
> -Kevin
>


Good call!

This query is fast:

SELECT count(1) FROM tbl_tracker WHERE ((srep_timestamp >=
date_trunc('day', timestamp with time zone '2010-01-20 10:16:55') AND
srep_timestamp <  date_trunc('day', timestamp with time zone '2010-01-20
10:16:55') + INTERVAL '24 hour' )) ;


In other words:

#fast
WHERE column <  '2010-010-20 00:00:00'

#fast
WHERE column <  date_trunc('day', timestamp with time zone
                              '2010-01-20 10:16:55')

#slow
WHERE column <  date_trunc('day', timestamp
                              '2010-01-20 10:16:55')


Why is that, I wonder?

Richard