Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 6CEE11337B6E for ; Wed, 23 Mar 2011 08:16:40 -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 62214-02 for ; Wed, 23 Mar 2011 11:16:21 +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 1EE9C13364DE for ; Wed, 23 Mar 2011 08:16:20 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 227F82A8117; Wed, 23 Mar 2011 07:16:21 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id 8386D2A8139; Wed, 23 Mar 2011 07:16:18 -0400 (EDT) Message-ID: <4D89D7AD.4050503@orkash.com> Date: Wed, 23 Mar 2011 16:51:17 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Chetan Suttraway CC: pgsql-performance@postgresql.org, tv@fuzzy.cz Subject: Re: Re-Reason of Slowness of Query References: <4D89BABF.4080806@orkash.com> <4D89BCD6.6040604@gmail.com> <4D89C704.5080603@orkash.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------000506090103010306070105" 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/269 X-Sequence-Number: 42948 This is a multi-part message in MIME format. --------------000506090103010306070105 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit 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) Thanks & best Regards, Adarsh Sharma Chetan Suttraway wrote: > > > On Wed, Mar 23, 2011 at 4:08 PM, > wrote: > > > I just want to retrieve that id 's from page_content which do > not have > > any entry in clause2 table. > > In that case the query probably does not work (at least the query > you've > sent in the first post) as it will return even those IDs that have at > least one other row in 'clause2' (not matching the != condition). > At least > that's how I understand it. > > true. > > > So instead of this > > select distinct(p.crawled_page_id) > from page_content p, clause2 c where p.crawled_page_id != > c.source_id ; > > you should probably do this > > 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); > > I guess this will be much more efficient too. > > > This looks like to give expected results. Also note that the where > clause "is null" is really required and is not an > optional predicate. > > > > regards > Tomas > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org > ) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > > > > > -- > Regards, > Chetan Suttraway > EnterpriseDB , The Enterprise PostgreSQL > company. > > > --------------000506090103010306070105 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit 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)


Thanks & best Regards,
Adarsh Sharma




Chetan Suttraway wrote:


On Wed, Mar 23, 2011 at 4:08 PM, <tv@fuzzy.cz> wrote:
> I just want to retrieve that id 's from page_content which do not have
> any entry in clause2 table.

In that case the query probably does not work (at least the query you've
sent in the first post) as it will return even those IDs that have at
least one other row in 'clause2' (not matching the != condition). At least
that's how I understand it.

true.
 
So instead of this

select distinct(p.crawled_page_id)
from page_content p, clause2 c where p.crawled_page_id != c.source_id ;

you should probably do this

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

I guess this will be much more efficient too.


This looks like to give expected results. Also note that the where clause "is null" is really required and is not an
optional predicate.

 
regards
Tomas


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



--
Regards,
Chetan Suttraway
EnterpriseDB, The Enterprise PostgreSQL company.




--------------000506090103010306070105--