Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id D64751337B9C for ; Wed, 23 Mar 2011 08:34:27 -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 38116-06 for ; Wed, 23 Mar 2011 11:34:19 +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 F274013364DE for ; Wed, 23 Mar 2011 08:34:18 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by smtp2.relay.dfw1a.emailsrvr.com (SMTP Server) with ESMTP id 3C3DE2A814F; Wed, 23 Mar 2011 07:34:19 -0400 (EDT) X-Virus-Scanned: OK Received: by smtp2.relay.dfw1a.emailsrvr.com (Authenticated sender: adarsh.sharma-AT-orkash.com) with ESMTPSA id CBB952A8116; Wed, 23 Mar 2011 07:34:16 -0400 (EDT) Message-ID: <4D89DBE6.10305@orkash.com> Date: Wed, 23 Mar 2011 17:09:18 +0530 From: Adarsh Sharma User-Agent: Thunderbird 2.0.0.22 (X11/20090625) MIME-Version: 1.0 To: Chetan Suttraway CC: tv@fuzzy.cz, 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: Content-Type: multipart/alternative; boundary="------------010203070406030109010908" 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/273 X-Sequence-Number: 42952 This is a multi-part message in MIME format. --------------010203070406030109010908 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit > 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. > > yeah. maybe the easiest way, is to start a fresh session and fire the > queries. After the fresh start , the results obtained are : 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=7725.132..7728.341 rows=72 loops=1) -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.115..7724.713 rows=74 loops=1) -> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.021..472.199 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: 7731.840 ms (6 rows) 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=6192.249..6195.368 rows=72 loops=1) -> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.036..6191.838 rows=74 loops=1) -> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.008..372.489 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.012..0.012 rows=1 loops=428467) Index Cond: (c.source_id = p.crawled_page_id) Total runtime: 6198.567 ms (6 rows) > This seems a slight upper hand of the second query . Would it be possible to tune it further. My postgresql.conf parameters are as follows : ( Total RAM = 16 GB ) shared_buffers = 4GB max_connections=700 effective_cache_size = 6GB work_mem=16MB maintenance_mem=64MB I think to change work_mem=64MB maintenance_mem=256MB Does it has some effects now. Thanks & best Regards, Adarsh Sharma > > Best regards, Vitalii Tymchyshyn > > > > > -- > Regards, > Chetan Suttraway > EnterpriseDB , The Enterprise PostgreSQL > company. > > > --------------010203070406030109010908 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
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.

yeah. maybe the easiest way, is to start a fresh session and fire the queries.


After the fresh start , the results obtained are :

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=7725.132..7728.341 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.115..7724.713 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.021..472.199 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: 7731.840 ms
(6 rows)

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=6192.249..6195.368 rows=72 loops=1)
   ->  Nested Loop Anti Join  (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.036..6191.838 rows=74 loops=1)
         ->  Seq Scan on page_content p  (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.008..372.489 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.012..0.012 rows=1 loops=428467)
               Index Cond: (c.source_id = p.crawled_page_id)
 Total runtime: 6198.567 ms
(6 rows)

This seems a slight upper hand of the second query .

Would it be possible to tune it further.
My postgresql.conf parameters are as follows : ( Total RAM = 16 GB )

shared_buffers = 4GB
max_connections=700
effective_cache_size = 6GB
work_mem=16MB
maintenance_mem=64MB

I think to change

work_mem=64MB
maintenance_mem=256MB

Does it has some effects now.


Thanks & best Regards,
Adarsh Sharma


Best regards, Vitalii Tymchyshyn



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




--------------010203070406030109010908--