X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id CDFE39FB2F8 for ; Thu, 10 Aug 2006 12:50:48 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 95507-04 for ; Thu, 10 Aug 2006 15:50:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from lists.commandprompt.com (host-130.commandprompt.net [207.173.203.130]) by postgresql.org (Postfix) with ESMTP id 29CD99FB25E for ; Thu, 10 Aug 2006 12:50:41 -0300 (ADT) Received: from alvh.no-ip.org (201-221-202-139.bk11-dsl.surnet.cl [201.221.202.139]) (authenticated bits=0) by lists.commandprompt.com (8.13.7/8.13.6) with ESMTP id k7AFoaPk004645; Thu, 10 Aug 2006 08:50:37 -0700 Received: by alvh.no-ip.org (Postfix, from userid 1000) id 9EB4DC45036; Thu, 10 Aug 2006 11:50:17 -0400 (CLT) Date: Thu, 10 Aug 2006 11:50:17 -0400 From: Alvaro Herrera To: Michal Taborsky - Internet Mall Cc: pgsql-performance@postgresql.org Subject: Re: 3-table query optimization Message-ID: <20060810155017.GI23053@alvh.no-ip.org> Mail-Followup-To: Michal Taborsky - Internet Mall , pgsql-performance@postgresql.org References: <44D9EBEB.9010704@mall.cz> <20367.1155136139@sss.pgh.pa.us> <44DAE09B.20009@mall.cz> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <44DAE09B.20009@mall.cz> User-Agent: Mutt/1.5.12-2006-07-14 X-Virus-Scanned: ClamAV version 0.88.3, clamav-milter version 0.88.3 on projects.commandprompt.com X-Virus-Status: Clean X-Greylist: Sender succeded SMTP AUTH authentication, not delayed by milter-greylist-1.6 (lists.commandprompt.com [192.168.2.159]); Thu, 10 Aug 2006 08:50:38 -0700 (PDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.856 tagged_above=0 required=5 tests=FORGED_RCVD_HELO, RCVD_IN_NJABL_PROXY X-Spam-Level: X-Archive-Number: 200608/189 X-Sequence-Number: 20374 Michal Taborsky - Internet Mall wrote: > Tom Lane napsal(a): > >Michal Taborsky - Internet Mall writes: > >>SELECT product.product_id > >> FROM action > >> JOIN product ON (product.product_id=action.product_id) > >> WHERE action.shop_group_id=1 > >> AND EXISTS (SELECT 1 > >> FROM catalog.product_program > >> WHERE product_id=product.product_id > >> AND product_program.program_id =1104322 > >> ) > > > >Try converting the EXISTS subquery to an IN. > > The performance is roughly the same. That's strange -- IN is usually much more amenable to better plans than EXISTS. Please post an EXPLAIN ANALYZE of the queries to see what's going on. It may be that the query is bound to be "slow" for some cases (depending on the program_id I guess?) -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.