Skip site navigation (1) Skip section navigation (2)

Re: Re-Reason of Slowness of Query

From: Adarsh Sharma <adarsh(dot)sharma(at)orkash(dot)com>
To: Chetan Suttraway <chetan(dot)suttraway(at)enterprisedb(dot)com>
Cc: tv(at)fuzzy(dot)cz, pgsql-performance(at)postgresql(dot)org
Subject: Re: Re-Reason of Slowness of Query
Date: 2011-03-23 11:39:18
Message-ID: 4D89DBE6.10305@orkash.com (view raw or flat)
Thread:
Lists: pgsql-performance
>     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 <http://www.enterprisedb.com/>, The Enterprise PostgreSQL 
> <http://www.enterprisedb.com/> company.
>
>
>

In response to

Responses

pgsql-performance by date

Next:From: tvDate: 2011-03-23 11:51:53
Subject: Re: Re-Reason of Slowness of Query
Previous:From: Adarsh SharmaDate: 2011-03-23 11:31:26
Subject: Re: Re-Reason of Slowness of Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group