Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 174231337BCC for ; Wed, 23 Mar 2011 05:51:30 -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 87727-01 for ; Wed, 23 Mar 2011 08:51:11 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f46.google.com (mail-fx0-f46.google.com [209.85.161.46]) by mail.postgresql.org (Postfix) with ESMTP id BFF061337990 for ; Wed, 23 Mar 2011 05:51:10 -0300 (ADT) Received: by fxm17 with SMTP id 17so7129444fxm.19 for ; Wed, 23 Mar 2011 01:51:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:message-id:date:from:user-agent:mime-version:to :cc:subject:references:in-reply-to:content-type; bh=VT0c9cW15is4JmpsIyjKhkAQAaQPxGJGJcdn1Yfd9mU=; b=wjQFdf2oNu5r0LZ3BE2LJDvbQljQiJ9qATVMcntEhfEGB89rDJiPZ15bBdqZRJxJ28 gbKPihfJj7pSTFTMSINAF3o7o85shALgl37xHGr9cOc6cQvNpwnWNPYaz4ATbdHbpd26 fACyYHIFC63X7iQ+i8Yk6eNx0+bL/dwoTlmPM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type; b=C+hb4Jy8/Dwes+xKEwAUsgBFGhOKccnnFM1j1KNfX3MFpG6FrcjVjSTnQp+aEnfWwl CH8CbK5xn/hU4t/j+t/IU424/jdnnUlfdTKLQnynkASvhRIPxddoV0KTbixlssIrZs6M bAJPYvnt56dapfrBgWUz3Slu8moWqHr63WAcs= Received: by 10.223.78.138 with SMTP id l10mr4811250fak.106.1300870269881; Wed, 23 Mar 2011 01:51:09 -0700 (PDT) Received: from [10.1.1.57] (skuns.zoral.com.ua [91.193.166.194]) by mx.google.com with ESMTPS id 14sm1859206fae.47.2011.03.23.01.51.05 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 23 Mar 2011 01:51:06 -0700 (PDT) Message-ID: <4D89B48D.80800@gmail.com> Date: Wed, 23 Mar 2011 10:51:25 +0200 From: Vitalii Tymchyshyn User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.2.14) Gecko/20110223 Lightning/1.0b2 Thunderbird/3.1.8 MIME-Version: 1.0 To: Adarsh Sharma CC: Chetan Suttraway , pgsql-performance@postgresql.org Subject: Re: Reason of Slowness of query References: <4D899301.7070301@orkash.com> <4D89A192.7000407@orkash.com> In-Reply-To: <4D89A192.7000407@orkash.com> Content-Type: multipart/alternative; boundary="------------030403060809050902060005" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.045 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7 X-Spam-Level: X-Archive-Number: 201103/259 X-Sequence-Number: 42938 This is a multi-part message in MIME format. --------------030403060809050902060005 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 23.03.11 09:30, Adarsh Sharma ???????(??): > Thanks Chetan, here is the output of your updated query : > > > *explain 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); > > * > QUERY PLAN > --------------------------------------------------------------------------------------- > HashAggregate (cost=1516749.47..1520576.06 rows=382659 width=8) > -> Hash Anti Join (cost=1294152.41..1515791.80 rows=383071 width=8) > Hash Cond: (p.crawled_page_id = c.source_id) > -> Seq Scan on page_content p (cost=0.00..87132.17 > rows=428817 width=8) > -> Hash (cost=771182.96..771182.96 rows=31876196 width=4) > -> Seq Scan on clause2 c (cost=0.00..771182.96 > rows=31876196 width=4) > (6 rows) > > And my explain analyze output is : > > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=1516749.47..1520576.06 rows=382659 width=8) > (actual time=56666.181..56669.270 rows=72 loops=1) > -> Hash Anti Join (cost=1294152.41..1515791.80 rows=383071 > width=8) (actual time=45740.789..56665.816 rows=74 loops=1) > Hash Cond: (p.crawled_page_id = c.source_id) > -> Seq Scan on page_content p (cost=0.00..87132.17 > rows=428817 width=8) (actual time=0.012..715.915 rows=428467 loops=1) > -> Hash (cost=771182.96..771182.96 rows=31876196 width=4) > (actual time=45310.524..45310.524 rows=31853083 loops=1) > -> Seq Scan on clause2 c (cost=0.00..771182.96 > rows=31876196 width=4) (actual time=0.055..23408.884 rows=31853083 > loops=1) > Total runtime: 56687.660 ms > (7 rows) > > But Is there is any option to tune it further and one more thing > output rows varies from 6 to 7. You need an index on source_id to prevent seq scan, like the next: CREATE INDEX idx_clause2_source_id ON clause2 (source_id);* *Best regards, Vitalii Tymchyshyn --------------030403060809050902060005 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 23.03.11 09:30, Adarsh Sharma написав(ла):
Thanks Chetan, here is the output of your updated query :


explain  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);


                                      QUERY PLAN                                      
---------------------------------------------------------------------------------------
 HashAggregate  (cost=1516749.47..1520576.06 rows=382659 width=8)
   ->  Hash Anti Join  (cost=1294152.41..1515791.80 rows=383071 width=8)
         Hash Cond: (p.crawled_page_id = c.source_id)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8)
         ->  Hash  (cost=771182.96..771182.96 rows=31876196 width=4)
               ->  Seq Scan on clause2 c  (cost=0.00..771182.96 rows=31876196 width=4)
(6 rows)

And my explain analyze output is :

                                                      QUERY PLAN                                                                
--------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=1516749.47..1520576.06 rows=382659 width=8) (actual time=56666.181..56669.270 rows=72 loops=1)
   ->  Hash Anti Join  (cost=1294152.41..1515791.80 rows=383071 width=8) (actual time=45740.789..56665.816 rows=74 loops=1)
         Hash Cond: (p.crawled_page_id = c.source_id)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.012..715.915 rows=428467 loops=1)
         ->  Hash  (cost=771182.96..771182.96 rows=31876196 width=4) (actual time=45310.524..45310.524 rows=31853083 loops=1)
               ->  Seq Scan on clause2 c  (cost=0.00..771182.96 rows=31876196 width=4) (actual time=0.055..23408.884 rows=31853083 loops=1)
 Total runtime: 56687.660 ms
(7 rows)

But Is there is any option to tune it further and one more thing output rows varies from 6 to 7.
You need an index on source_id to prevent seq scan, like the next:
CREATE INDEX idx_clause2_source_id
  ON clause2
  (source_id);

Best regards, Vitalii Tymchyshyn

--------------030403060809050902060005--