Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 9EB691337BA6 for ; Wed, 23 Mar 2011 08:21:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 17732-08 for ; Wed, 23 Mar 2011 11:21:22 +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 570431337B6E for ; Wed, 23 Mar 2011 08:21:20 -0300 (ADT) Received: by fxm17 with SMTP id 17so7227452fxm.19 for ; Wed, 23 Mar 2011 04:21:19 -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 :subject:references:in-reply-to:content-type; bh=iLmvXDIhVJsCBSyYY/uI1YzTCrmp74h7i5sGtwGqFoA=; b=Gu3hOBi3f97ACii2TQUOU8F9CMjcnAJsRloPI+gM673kXKFzOjFFPli2zkgwCUkirU hlV3ITDMXC7jXdnxv3Z6VxNjDfWtr1GUlDhLMU6ffPOyTfJppKogFeRt7GEbCdbThNJn QN3liupJ1WiA6HE34OSpZG7fefdhFe6ZJCbgM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:subject:references :in-reply-to:content-type; b=RHEq5I5CiAjTj1qq2TZdrGuJp3nE8P7H8UQRCB3B8THqi4uNzUslebI+Tr4POK3I63 wF8tckPt0Fr6M9f4zfhhCVP499O5vAnQlJAYICR7l5ZEUwVaR6jDaIV4yjDONMDxGWhj LY8s+jQCBYxnc82zeWWzjeZVMD3n1aYxX1E44= Received: by 10.223.6.198 with SMTP id a6mr1322423faa.128.1300879279697; Wed, 23 Mar 2011 04:21:19 -0700 (PDT) Received: from [10.1.1.57] (skuns.zoral.com.ua [91.193.166.194]) by mx.google.com with ESMTPS id e23sm3384226faa.42.2011.03.23.04.21.17 (version=TLSv1/SSLv3 cipher=OTHER); Wed, 23 Mar 2011 04:21:18 -0700 (PDT) Message-ID: <4D89D7C2.3040307@gmail.com> Date: Wed, 23 Mar 2011 13:21:38 +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: pgsql-performance@postgresql.org Subject: Re: Re-Reason of Slowness of Query References: <4D89BABF.4080806@orkash.com> <4D89BCD6.6040604@gmail.com> <4D89C704.5080603@orkash.com> <4D89D7AD.4050503@orkash.com> In-Reply-To: <4D89D7AD.4050503@orkash.com> Content-Type: multipart/alternative; boundary="------------010806090908000300080409" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.335 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201103/270 X-Sequence-Number: 42949 This is a multi-part message in MIME format. --------------010806090908000300080409 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 23.03.11 13:21, Adarsh Sharma ???????(??): > Thank U all, for U'r Nice Support. > > Let me Conclude the results, below results are obtained after finding > the needed queries : > > *First Option : > > *pdc_uima=# explain analyze select distinct(p.crawled_page_id) > pdc_uima-# from page_content p left join clause2 c on (p.crawled_page_id = > pdc_uima(# c.source_id) where (c.source_id is null); > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------------- > HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) > (actual time=87927.000..87930.084 rows=72 loops=1) > -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 > width=8) (actual time=0.191..87926.546 rows=74 loops=1) > -> Seq Scan on page_content p (cost=0.00..87132.17 > rows=428817 width=8) (actual time=0.027..528.978 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.202..0.202 rows=1 > loops=428467) > Index Cond: (p.crawled_page_id = c.source_id) > Total runtime: 87933.882 ms:-( > (6 rows) > > *Second Option : > > *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=7047.259..7050.261 rows=72 loops=1) > -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 > width=8) (actual time=0.039..7046.826 rows=74 loops=1) > -> Seq Scan on page_content p (cost=0.00..87132.17 > rows=428817 width=8) (actual time=0.008..388.976 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.013..0.013 rows=1 > loops=428467) > Index Cond: (c.source_id = p.crawled_page_id) > Total runtime: 7054.074 ms :-) > (6 rows) > Actually the plans are equal, so I suppose it depends on what were run first :). Slow query operates with data mostly on disk, while fast one with data in memory. Best regards, Vitalii Tymchyshyn --------------010806090908000300080409 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 23.03.11 13:21, Adarsh Sharma написав(ла):
Thank U all, for U'r Nice Support.

Let me Conclude the results, below results are obtained after finding the needed queries :

First Option :

pdc_uima=# explain analyze select distinct(p.crawled_page_id)
pdc_uima-# from page_content p left join clause2 c on (p.crawled_page_id =
pdc_uima(# c.source_id) where (c.source_id is null);
                                                                     QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) (actual time=87927.000..87930.084 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.191..87926.546 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.027..528.978 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.202..0.202 rows=1 loops=428467)
               Index Cond: (p.crawled_page_id = c.source_id)
 Total runtime: 87933.882 ms :-(
(6 rows)

Second Option :

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=7047.259..7050.261 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.039..7046.826 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.008..388.976 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.013..0.013 rows=1 loops=428467)
               Index Cond: (c.source_id = p.crawled_page_id)
 Total runtime: 7054.074 ms :-)
(6 rows)


Actually the plans are equal, so I suppose it depends on what were run first :). Slow query operates with data mostly on disk, while fast one with data in memory.

Best regards, Vitalii Tymchyshyn
--------------010806090908000300080409--