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 92DE79FA131 for ; Sun, 21 May 2006 07:39:42 -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 48745-04 for ; Sun, 21 May 2006 07:39:32 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mx1.ipf.is (benz.ipf.is [85.197.192.156]) by postgresql.org (Postfix) with ESMTP id 649B69FA159 for ; Sun, 21 May 2006 07:39:32 -0300 (ADT) Received: (qmail 10299 invoked by uid 1010); 21 May 2006 10:38:00 +0000 Received: from 85.197.216.186 by benz (envelope-from , uid 1008) with qmail-scanner-1.25-st-qms (clamdscan: 0.88/1235. spamassassin: 3.1.1. perlscan: 1.25-st-qms. Clear:RC:1(85.197.216.186):SA:0(-4.2/4.5):. Processed in 0.314003 secs); 21 May 2006 10:38:00 -0000 X-Antivirus-IPF-Mail-From: gnari@hive.is via benz X-Antivirus-IPF: 1.25-st-qms (Clear:RC:1(85.197.216.186):SA:0(-4.2/4.5):. Processed in 0.314003 secs Process 10292) Received: from dsl-216-186.hive.is (HELO ?192.168.1.34?) (85.197.216.186) by mx1.ipf.is with SMTP; 21 May 2006 10:37:59 +0000 Subject: Re: Performs WAY better with enable_seqscan = off From: Ragnar To: Brendan Duddridge Cc: PostgreSQL Performance In-Reply-To: References: Content-Type: text/plain Date: Sun, 21 May 2006 10:50:11 +0000 Message-Id: <1148208611.28240.101.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.4.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/411 X-Sequence-Number: 19198 On sun, 2006-05-21 at 02:21 -0600, Brendan Duddridge wrote: > Hi, > > > I have a query that performs WAY better when I have enable_seqscan = > off: > > > explain analyze select ac.attribute_id, la.name, ac.sort_order from > attribute_category ac, localized_attribute la where ac.category_id = > 1001402 and la.locale_id = 1000001 and ac.is_browsable = 'true' and > la.attribute_id = ac.attribute_id and exists ( select 'x' from > product_attribute_value pav, category_product cp where (pav.product_id > || '.' || pav.attribute_id) = (cp.product_id || '.' || > ac.attribute_id) and pav.status_code is null and (cp.category_id || > '.' || cp.is_visible) = '1001402.true') order by (ac.sort_order is > null), ac.sort_order, la.name asc; is there some reason for the complicated form of the join conditions in the subselect? would this not be clearer: explain analyze select ac.attribute_id, la.name, ac.sort_order from attribute_category ac, localized_attribute la where ac.category_id = 1001402 and la.locale_id = 1000001 and ac.is_browsable = 'true' and la.attribute_id = ac.attribute_id and exists (select 'x' from product_attribute_value pav, category_product cp where pav.product_id = cp.product_id and pav.attribute_id = ac.attribute_id and pav.status_code is null and cp.category_id= '1001402' and cp.is_visible = 'true' ) order by (ac.sort_order is null), ac.sort_order, la.name asc; possibly the planner would have a better time figuring out if any indexes are usable or estimating the subselect rowcount gnari