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

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