X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 304B99F9F2E for ; Thu, 11 May 2006 19:08:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 97474-02-6 for ; Thu, 11 May 2006 19:08:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 3BC479FA5F8 for ; Thu, 11 May 2006 19:08:04 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 1177E56431; Thu, 11 May 2006 17:08:03 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Thu, 11 May 2006 17:08:02 -0500 Date: Thu, 11 May 2006 17:08:02 -0500 From: "Jim C. Nasby" To: Brendan Duddridge Cc: PostgreSQL Performance Subject: Re: Assistance with optimizing query - same SQL, different category_id = Seq Scan Message-ID: <20060511220802.GU99570@pervasive.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060511:brendan@clickspace.com::rzJd8QjjWmryBdF9:000000000000000 0000000000000000000000000sve X-Hashcash: 1:20:060511:pgsql-performance@postgresql.org::nucCzg65Nrgiy7Sm:00000 0000000000000000000000003oET X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/304 X-Sequence-Number: 19091 On Mon, May 08, 2006 at 07:29:32PM -0600, Brendan Duddridge wrote: > Do you have any suggestions on how I can optimize the query so both > versions of the query come back fast without doing a sequential scan > on the price table? Well, before you do anything you should verify that an index scan in the second case would actually be faster. Set enable_seqscan=off and check that. After that, you can favor an index scan by (in order of effectiveness) increasing the correlation on the appropriate index (by clustering on it), lowering random_page_cost, or increasing effective_cache_size. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461