Skip site navigation (1) Skip section navigation (2)

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

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: "Richard Neill" <rn214(at)cam(dot)ac(dot)uk>
Cc: <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop
Date: 2010-01-20 17:04:09
Message-ID: 4B56E329020000250002E88E@gw.wicourts.gov (view raw or flat)
Thread:
Lists: pgsql-bugs
Richard Neill <rn214(at)cam(dot)ac(dot)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

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-01-20 19:31:03
Subject: Re: BUG #5290: Simple loop with insert into and check to avoid duplicate values fails
Previous:From: Richard NeillDate: 2010-01-20 16:56:16
Subject: Re: BUG #5293: constant function (date_trunc) is repeatedly evaluated inside loop

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group