Received: from charon.simplot.com (jrsmailhub.simplot.com [12.18.144.212]) by postgresql.org (8.11.3/8.11.4) with ESMTP id fAE0njm60599 for ; Tue, 13 Nov 2001 19:49:45 -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:49:47 -0700 Received: from npa01zz001.simplot.com by xanadu.simplot.COM; (5.65v4.0/1.1.8.2/20Aug97-1116AM) id AA14181; Tue, 13 Nov 2001 17:49:47 -0700 Received: from earlj by npa01zz001.simplot.com with local (Exim 3.32 #1 (Debian)) id 163oFN-00015J-00; Tue, 13 Nov 2001 17:49:45 -0700 To: "Josh Berkus" CC: Jean-Christophe Boggio , pgsql-sql@postgresql.org Subject: Re: Optimization with dates References: From: Jason Earl Date: 13 Nov 2001 17:49:45 -0700 In-Reply-To: Message-ID: <87bsi6jhw6.fsf@npa01zz001.simplot.com> Lines: 39 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/181 X-Sequence-Number: 5565 Yikes! Good catch. My example database returns on ~.6M for the last 30 days, and an index scan still turns out to be a win (of course, it turned out to be a bigger win to have a separate table with 15 minute summarizations of the data :). Josh is right, chances are good that a sequential scan will actually perform better for you than an index scan if you are returning 20% of your entire table. Jason "Josh Berkus" writes: > Jean-Christophe, > > > Aggregate (cost=256546.78..256546.78 rows=1 width=0) > > -> Seq Scan on gains (cost=0.00..250627.68 rows=2367640 width=0) > > > > whereas : > > Hmmm... if the number of rows is actually accurate (2M out of 10M in the > last 30 days) then a Seq Scan seems like a good plan to me. If the > numbers aren't accurate, it's time to run a VACUUM ANALYZE. > > Regardless, if you're actually querying for 2M recordsets, you'd better > throw some hardware at the problem, and learn the .conf parameters. > > -Josh > > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org