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

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