Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA56759 for ; Mon, 6 Sep 1999 11:29:55 -0400 (EDT) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.8/8.8.8) with ESMTP id RAA11613; Mon, 6 Sep 1999 17:23:07 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: <9681.935887600@sss.pgh.pa.us> References: Your message of Fri, 27 Aug 1999 19:10:19 -0300 (ADT) Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Mon, 6 Sep 1999 18:25:13 +0300 To: Michael Richards From: Herouth Maoz Subject: Re: [SQL] indexes using datetime Cc: pgsql-sql@postgreSQL.org At 03:46 +0300 on 29/08/1999, Tom Lane wrote: > Michael Richards writes: > > explain select * from logins where logintime>'now'::datetime-'40 > > days'::timespan; > > NOTICE: QUERY PLAN: > > Seq Scan on logins (cost=5839.78 rows=44958 width=44) > > > Very bad query plan :( > > Yah. It's got nothing to do with datetime though. Problem is that the > system can only make indexscans work with WHERE clauses of the form > "field op constant" (for "op"s related to the sort ordering of the > index of course). Your righthand side is not a constant. A tip: use SELECT 'now'::datetime - '40 days'::timespan; Get the result on your client side, say in a variable named $x, and use it in the "real" query: SELECT * FROM logins WHERE logintime > $x; (Actual embedding protocol depends, of course, on the language you use). Then it's a constant, as far as Postgres is concerned, and the time wasted for the small query is really not an issue. Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma