Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 7C32FB5DBCE for ; Mon, 27 Jun 2011 11:30:40 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 37625-03 for ; Mon, 27 Jun 2011 14:30:34 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from gw.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by mail.postgresql.org (Postfix) with ESMTP id 03370B5DBDA for ; Mon, 27 Jun 2011 11:30:33 -0300 (ADT) Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Mon, 27 Jun 2011 09:30:33 -0500 Message-Id: <4E084DAF020000250003EC54@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.1 Date: Mon, 27 Jun 2011 09:30:23 -0500 From: "Kevin Grittner" To: "Jens Hoffrichter" , Subject: Re: Getting rid of a seq scan in query on a large table References: In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.91 tagged_above=-10 required=5 tests=BAYES_00=-1.9, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201106/241 X-Sequence-Number: 44174 Jens Hoffrichter wrote: > I'm having trouble getting rid of a sequential scan on a table > with roughly 120k entries it. Please post your configuration information and some information about your hardware and OS. http://wiki.postgresql.org/wiki/SlowQueryQuestions Since the table scan went through about 120000 rows in 60 ms, it is clear that your data is heavily cached, so random_page_cost should probably be close to or equal to seq_page_cost, and that value should probably be somewhere around 0.1 to 0.5. You should have effective_cache_size set to the sum of shared_buffers plus whatever your OS cache is. I have sometimes found that I get faster plans with cpu_tuple_cost increased. If such tuning does cause it to choose the plan you expect, be sure to time it against what you have been getting. If the new plan is slower, you've taken the adjustments too far. -Kevin