Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id UAA37524 for ; Sat, 28 Aug 1999 20:50:14 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id UAA09684; Sat, 28 Aug 1999 20:46:41 -0400 (EDT) To: Michael Richards cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] indexes using datetime In-reply-to: Your message of Fri, 27 Aug 1999 19:10:19 -0300 (ADT) Date: Sat, 28 Aug 1999 20:46:40 -0400 Message-ID: <9681.935887600@sss.pgh.pa.us> From: Tom Lane 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. Eventually, maybe for 6.6 or 6.7, we will have the smarts in place to reduce constant expressions to simple constants during preprocessing of a query. For now, you are going to have to structure your application to precompute the value that's going to be compared against index entries. regards, tom lane