Re: Optimization with dates

Jean-Christophe Boggio <cat(at)thefreecat(dot)org> writes:
> I have tried :
> where dategain>=(select now()-30);
> where dategain+30>='now'
> where date(dategain)>=date('now')-30
> and many other, syntactically absurd :-)

Try
dategain >= ago(30)

where "ago" is a function that computes "date(now()) - n" and is
marked "iscachable". This is a cheat but it keeps the planner from
being distracted by the noncachable nature of "now()". You can find
past discussions of this if you search the archives for "iscachable",
eg
http://fts.postgresql.org/db/mw/msg.html?mid=1037521
http://fts.postgresql.org/db/mw/msg.html?mid=60584
http://fts.postgresql.org/db/mw/msg.html?mid=97823

As several other people pointed out, there's also a question of whether
the system *should* use the index --- you haven't told us how many
rows you expect the query to visit. But until you express the WHERE
condition in the form "column >= something-that-can-be-reduced-to-
a-constant", you're unlikely to get the system to even try.

regards, tom lane