Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id DA8AE1337BBB for ; Wed, 23 Mar 2011 11:16:10 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 87819-03 for ; Wed, 23 Mar 2011 14:16:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from elizabeth.gransy.com (elizabeth.gransy.com [89.187.132.199]) by mail.postgresql.org (Postfix) with ESMTP id 67B601337B92 for ; Wed, 23 Mar 2011 11:16:03 -0300 (ADT) Received: from sq.gransy.com (localhost [127.0.0.1]) by elizabeth.gransy.com (Postfix) with ESMTP id 6063B15E4143; Wed, 23 Mar 2011 15:16:01 +0100 (CET) Received: from 85.160.232.114 (SquirrelMail authenticated user tv@fuzzy.cz) by sq.gransy.com with HTTP; Wed, 23 Mar 2011 15:16:01 +0100 Message-ID: <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com> In-Reply-To: <4D89F6E5.7010803@peak6.com> References: <4D89BABF.4080806@orkash.com> <4D89F6E5.7010803@peak6.com> Date: Wed, 23 Mar 2011 15:16:01 +0100 Subject: Re: Re-Reason of Slowness of Query From: tv@fuzzy.cz To: sthomas@peak6.com Cc: "Adarsh Sharma" , "pgsql-performance@postgresql.org" User-Agent: SquirrelMail/1.4.21 MIME-Version: 1.0 Content-Type: text/plain;charset=utf-8 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) Importance: Normal X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.001 tagged_above=-10 required=5 tests=BAYES_20=-0.001 X-Spam-Level: X-Archive-Number: 201103/280 X-Sequence-Number: 42959 > On 03/23/2011 04:17 AM, Adarsh Sharma wrote: > >> explain analyze select distinct(p.crawled_page_id) from page_content >> p where NOT EXISTS (select 1 from clause2 c where c.source_id = >> p.crawled_page_id); > > You know... I'm surprised nobody has mentioned this, but DISTINCT is > very slow unless you have a fairly recent version of Postgres that > replaces it with something faster. Try this: Nobody mentioned that because the explain plan already uses hash aggregate (instead of the old sort) HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7047.259..7050.261 rows=72 loops=1) which means this is at least 8.4. Plus the 'distinct' step uses less than 1% of total time, so even if you improve it the impact will be minimal. regards Tomas