Received: from miaou.thefreecat.org ([213.11.8.225]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fADNuvm58032 for ; Tue, 13 Nov 2001 18:56:59 -0500 (EST) (envelope-from cat@thefreecat.org) Received: from bast (bast.thefreecat.local [192.168.0.2]) by miaou.thefreecat.org (Postfix) with ESMTP id 78E0C554004 for ; Wed, 14 Nov 2001 00:56:58 +0100 (CET) Date: Wed, 14 Nov 2001 00:56:56 +0100 From: Jean-Christophe Boggio X-Mailer: The Bat! (v1.53d) Personal Reply-To: Jean-Christophe Boggio X-Priority: 3 (Normal) Message-ID: <72319017913.20011114005656@thefreecat.org> To: pgsql-sql@postgresql.org Subject: Optimization with dates MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200111/177 X-Sequence-Number: 5561 Hello, I really have a problem dealing with dates : I have a big table (~10M rows) like : create table gains ( dategain timestamp, otherfields ); There's an index on dategain and I want to use it to get the last 30 days records : explain select count(*) from gains where dategain>=now()-30; Aggregate (cost=256546.78..256546.78 rows=1 width=0) -> Seq Scan on gains (cost=0.00..250627.68 rows=2367640 width=0) whereas : explain select count(*) from gains where dategain>='now'; Aggregate (cost=27338.47..27338.47 rows=1 width=0) -> Index Scan using ix_gains_dategain on gains (cost=0.00..27320.71 rows=7103 width=0) 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 ? TIA, -- Jean-Christophe Boggio cat@thefreecat.org -o) Independant Consultant and Developer /\\ Delphi, Linux, Perl, PostgreSQL, Debian _\_V