Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAE0i2m60464 for ; Tue, 13 Nov 2001 19:44:03 -0500 (EST) (envelope-from earlj@xanadu.simplot.com) Received: from xanadu.simplot.COM (151.113.0.132) by charon.simplot.com (MX V5.1-A AnGp) with SMTP; Tue, 13 Nov 2001 17:44:04 -0700 Received: from npa01zz001.simplot.com by xanadu.simplot.COM; (5.65v4.0/1.1.8.2/20Aug97-1116AM) id AA14124; Tue, 13 Nov 2001 17:44:03 -0700 Received: from earlj by npa01zz001.simplot.com with local (Exim 3.32 #1 (Debian)) id 163o9p-00015D-00; Tue, 13 Nov 2001 17:44:01 -0700 To: Jean-Christophe Boggio CC: pgsql-sql@postgresql.org Subject: Re: Optimization with dates References: <72319017913.20011114005656@thefreecat.org> From: Jason Earl Date: 13 Nov 2001 17:44:00 -0700 In-Reply-To: <72319017913.20011114005656@thefreecat.org> Message-ID: <87g07iji5r.fsf@npa01zz001.simplot.com> Lines: 70 User-Agent: Gnus/5.0808 (Gnus v5.8.8) Emacs/21.1 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200111/180 X-Sequence-Number: 5564 I have a similar table (~16M rows) with an indexed timestamp, and have had similar problems. I have found that even when I am using a constant timestamp like in this query. SELECT * FROM caseweights1 WHERE dt > '2001-11-01'; I start getting sequential scans with 7.1.3 long before they are faster than index based queries. I believe that there is a constant that can be fiddled to modify this behavior, and it seems like I have also read that this constant has been modified in the new 7.2 release. However, for queries that you *know* will be faster using the index you can always issue: set enable_seqscan to off; before running your query. This will force PostgreSQL to use the index even in queries like this one: SELECT * FROM caseweights1 WHERE dt > (SELECT now() - '30 days'::interval); Jason Jean-Christophe Boggio writes: > 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster