Re: Timestamps and performances problems

Jean-Christophe ARNU (JX) <jc(dot)arnu(at)free(dot)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