Re: Optimization with dates

cat@thefreecat.org (Jean-Christophe Boggio) wrote in message news:<72319017913.20011114005656@thefreecat.org>...
> I have tried :
>   where dategain>=(select now()-30);
>   where dategain+30>='now'
>   where date(dategain)>=date('now')-30
>   and many other, syntactically absurd :-)
>
> Anyone can help me use this index ?

From my brief experiments, it _looks_ like the optimizer uses index
scan on dates only when those dates are constant (in my case, anyway -
as always, YMMV).

I tried functions, variables, and using both upper and lower limits.

The only time index scanning was used (according to "explain") was
when I used constant values for the dates.

So ... do you _have_ to do your querying in psql?  Or will your
application permit you to create queries with embedded constant date
values in the Planguage (PHP, Python, Perl) of your choice?


Calvin