Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAE2i1m66366 for ; Tue, 13 Nov 2001 21:44:01 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id fAE2hrc04599; Tue, 13 Nov 2001 21:43:53 -0500 (EST) To: Jean-Christophe Boggio cc: pgsql-sql@postgresql.org Subject: Re: Optimization with dates In-reply-to: <72319017913.20011114005656@thefreecat.org> References: <72319017913.20011114005656@thefreecat.org> Comments: In-reply-to Jean-Christophe Boggio message dated "Wed, 14 Nov 2001 00:56:56 +0100" Date: Tue, 13 Nov 2001 21:43:52 -0500 Message-ID: <4596.1005705832@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200111/183 X-Sequence-Number: 5567 Jean-Christophe Boggio 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