Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 3B7411337B83 for ; Thu, 24 Mar 2011 02:05:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 78281-01-3 for ; Thu, 24 Mar 2011 05:04:41 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from smtp120.dfw.emailsrvr.com (smtp120.dfw.emailsrvr.com [67.192.241.120]) by mail.postgresql.org (Postfix) with ESMTP id 4E8D613381D2 for ; Thu, 24 Mar 2011 01:47:55 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id BA76F2A813F; Thu, 24 Mar 2011 00:47:53 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 840D82A80DD; Thu, 24 Mar 2011 00:47:52 -0400 (EDT) Message-ID: <4D8ACE26.5070606@orkash.com> Date: Thu, 24 Mar 2011 10:22:54 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: tv@fuzzy.cz CC: pgsql-performance@postgresql.org Subject: Re: Re-Reason of Slowness of Query References: <4D89BABF.4080806@orkash.com> <4D89F6E5.7010803@peak6.com> <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com> In-Reply-To: <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com> Content-Type: multipart/alternative; boundary="------------080307050502000400060700" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201103/301 X-Sequence-Number: 42980 This is a multi-part message in MIME format. --------------080307050502000400060700 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit tv@fuzzy.cz wrote: >> 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. > > Yes, U"r absolutely right I am using Version 8.4SS and i am satisfied with the below query results: pdc_uima=# explain analyze select distinct(p.crawled_page_id) from page_content p pdc_uima-# where NOT EXISTS (select 1 from clause2 c where c.source_id = p.crawled_page_id); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=5149.308..5152.251 rows=72 loops=1) -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.119..5148.954 rows=74 loops=1) -> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.021..444.487 rows=428467 loops=1) -> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..18.18 rows=781 width=4) (actual time=0.009..0.009 rows=1 loops=428467) Index Cond: (c.source_id = p.crawled_page_id) Total runtime: 5155.874 ms (6 rows) I don't think that the above results are optimized further. Thanks & best Regards, Adarsh Sharma > regards > Tomas > > --------------080307050502000400060700 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit tv@fuzzy.cz wrote: 
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.

Yes, U"r absolutely right I am using Version 8.4SS and i am satisfied with the below query results:

pdc_uima=# explain analyze select distinct(p.crawled_page_id) from page_content p
pdc_uima-#  where NOT EXISTS (select 1 from  clause2 c where c.source_id = p.crawled_page_id);
                                                                     QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) (actual time=5149.308..5152.251 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.119..5148.954 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.021..444.487 rows=428467 loops=1)
         ->  Index Scan using idx_clause2_source_id on clause2 c  (cost=0.00..18.18 rows=781 width=4) (actual time=0.009..0.009 rows=1 loops=428467)
               Index Cond: (c.source_id = p.crawled_page_id)
 Total runtime: 5155.874 ms
(6 rows)

I don't think that the above results are optimized further.


Thanks & best Regards,
Adarsh Sharma
regards
Tomas

--------------080307050502000400060700--