Re: Timestamps and performances problems

Jean-Christophe ARNU (JX) <jc.arnu@free.fr> writes:
>   When I use timestamps + interval in where clauses, query performance is
>  slowed down by a factor of 20 or 30!!!! For exemple :
>       select timestamp,value
>       from measure
>       where timestamp<now() and timestamp>(now() - '1 hour'::interval)

>       is 20 to 30 times longer than

>       select timestamp,value
>       from measure
>       where timestamp<'2002-04-10 10:00' and timestamp>='2002-04-10 9:00';

>   So where is the bottleneck?

Did you compare EXPLAIN output?  I suspect that the second query is
using an index on the timestamp column and the first isn't.

The reason it isn't is that now() isn't a constant, and the system is
not smart enough to realize that it's safe to optimize the query into
an indexscan anyway.

For 7.3 we've fixed this by introducing a new concept of "constant within
a query", which now() does satisfy.  In the meantime you could hack
around it by writing a user-defined function that calls now() and is
marked isCachable --- which is a lie, but you can get away with it in
interactive queries.  (But don't try calling such a function in views,
or queries in plpgsql, 'cause you'll get burnt.)

            regards, tom lane