Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id E13361337BA3 for ; Wed, 23 Mar 2011 08:26:37 -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 20705-04 for ; Wed, 23 Mar 2011 11:26:29 +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 3AD0E1337B6E for ; Wed, 23 Mar 2011 08:26:27 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 8C6FA2A817A; Wed, 23 Mar 2011 07:26:27 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 847C22A8161; Wed, 23 Mar 2011 07:26:25 -0400 (EDT) Message-ID: <4D89DA0E.4060908@orkash.com> Date: Wed, 23 Mar 2011 17:01:26 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Vitalii Tymchyshyn CC: 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> <4D89D7C2.3040307@gmail.com> In-Reply-To: <4D89D7C2.3040307@gmail.com> Content-Type: multipart/alternative; boundary="------------050808040306080703060708" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201103/272 X-Sequence-Number: 42951 This is a multi-part message in MIME format. --------------050808040306080703060708 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Vitalii Tymchyshyn wrote: > 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) from >> page_content p left join clause2 c on (p.crawled_page_id = >> 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. Yes U 'r absolutely right, if I run it again, it display the output as : pdc_uima=# explain analyze select distinct(p.crawled_page_id) from page_content p left join clause2 c on (p.crawled_page_id = c.source_id) where (c.source_id is null); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7618.452..7621.427 rows=72 loops=1) -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.131..7618.043 rows=74 loops=1) -> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.020..472.811 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.015..0.015 rows=1 loops=428467) Index Cond: (p.crawled_page_id = c.source_id) Total runtime: 7637.132 ms (6 rows) I let U know after a fresh start (session ). Then the true result comes and if further tuning required can be performed. Best Regards, Adarsh > > Best regards, Vitalii Tymchyshyn --------------050808040306080703060708 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Vitalii Tymchyshyn wrote:
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) from page_content p left join clause2 c on (p.crawled_page_id = 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.

Yes U 'r absolutely right, if I run it again, it display the output as :

pdc_uima=# explain analyze select distinct(p.crawled_page_id) from page_content p left join clause2 c on (p.crawled_page_id = c.source_id) where (c.source_id is null);

                                                                    QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7618.452..7621.427 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.131..7618.043 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.020..472.811 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.015..0.015 rows=1 loops=428467)
               Index Cond: (p.crawled_page_id = c.source_id)
 Total runtime: 7637.132 ms
(6 rows)

I let U know after a fresh start (session ).
Then the true result comes and if further tuning required can be performed.

Best Regards, Adarsh

Best regards, Vitalii Tymchyshyn

--------------050808040306080703060708--