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