Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 732381337B9C for ; Wed, 23 Mar 2011 06:13:01 -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 36040-05 for ; Wed, 23 Mar 2011 09:12:52 +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 6660C13364DE for ; Wed, 23 Mar 2011 06:12:51 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 99BD82A8149; Wed, 23 Mar 2011 05:12:51 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 24D942A80F4 for ; Wed, 23 Mar 2011 05:12:49 -0400 (EDT) Message-ID: <4D89BABF.4080806@orkash.com> Date: Wed, 23 Mar 2011 14:47:51 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re-Reason of Slowness of Query Content-Type: multipart/alternative; boundary="------------050402020602010404050906" 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/260 X-Sequence-Number: 42939 This is a multi-part message in MIME format. --------------050402020602010404050906 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Thanks Chetan, After my Lunch Break, I tried the below steps : *My original query was : *explain analyze select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id != c.source_id which hangs because it is wrong query to fetch the desired output . *Next Updated Query be Chetan Suttraway :* 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); QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7192.843..7195.923 rows=72 loops=1) -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.040..7192.426 rows=74 loops=1) -> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.009..395.599 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.014..0.014 rows=1 loops=428467) Index Cond: (c.source_id = p.crawled_page_id) Total runtime: 7199.748 ms (6 rows) I think it is very much faster but I don't understand the query : *explain select distinct(b) from t1,t2 where t1.b >t2.d union all select distinct(b) from t1,t2 where t1.b c.source_id union all select distinct(p.crawled_page_id) from page_content p,clause2 c where p.crawled_page_id < c.source_id; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..296085951076.34 rows=765318 width=8) -> Unique (cost=0.00..148042971711.58 rows=382659 width=8) -> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8) -> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8) -> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4) Index Cond: (p.crawled_page_id > c.source_id) -> Unique (cost=0.00..148042971711.58 rows=382659 width=8) -> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8) -> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8) -> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4) Index Cond: (p.crawled_page_id < c.source_id) (11 rows) I don't think this is correct because it produce 11 rows output. Any further suggestions, Please guide. Thanks & best Regards, Adarsh Sharma --------------050402020602010404050906 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Thanks Chetan, After my Lunch Break, I tried the below steps :

My original query was :
explain analyze select distinct(p.crawled_page_id) from page_content p , clause2  c where p.crawled_page_id != c.source_id

which hangs because it is wrong query to fetch the desired output .

Next Updated Query be Chetan Suttraway :

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

                                                                    QUERY PLAN                                                                     
-----------------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7192.843..7195.923 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.040..7192.426 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.009..395.599 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.014..0.014 rows=1 loops=428467)
               Index Cond: (c.source_id = p.crawled_page_id)
 Total runtime: 7199.748 ms
(6 rows)

I think it is very much faster but I don't understand the query :

explain select distinct(b) from t1,t2 where t1.b >t2.d union all  select distinct(b) from t1,t2 where  t1.b <t2.d;

As i transform it into my format as:

explain select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id > c.source_id union all  select distinct(p.crawled_page_id) from page_content p,clause2 c where p.crawled_page_id < c.source_id;

                                                     QUERY PLAN                                                     
---------------------------------------------------------------------------------------------------------------------
 Append  (cost=0.00..296085951076.34 rows=765318 width=8)
   ->  Unique  (cost=0.00..148042971711.58 rows=382659 width=8)
         ->  Nested Loop  (cost=0.00..136655213119.84 rows=4555103436696 width=8)
               ->  Index Scan using idx_page_id on page_content p  (cost=0.00..174214.02 rows=428817 width=8)
               ->  Index Scan using idx_clause2_source_id on clause2 c  (cost=0.00..185898.05 rows=10622488 width=4)
                     Index Cond: (p.crawled_page_id > c.source_id)
   ->  Unique  (cost=0.00..148042971711.58 rows=382659 width=8)
         ->  Nested Loop  (cost=0.00..136655213119.84 rows=4555103436696 width=8)
               ->  Index Scan using idx_page_id on page_content p  (cost=0.00..174214.02 rows=428817 width=8)
               ->  Index Scan using idx_clause2_source_id on clause2 c  (cost=0.00..185898.05 rows=10622488 width=4)
                     Index Cond: (p.crawled_page_id < c.source_id)
(11 rows)

I don't think this is correct because it produce 11 rows output.

Any further suggestions, Please guide.

Thanks & best Regards,
Adarsh Sharma

--------------050402020602010404050906--