From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: Chetan Suttraway <chetan.suttraway@enterprisedb.com>
Cc: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Re-Reason of Slowness of Query
Date: Wed, 23 Mar 2011 17:09:18 +0530
Message-ID: <4D89DBE6.10305@orkash.com> (raw)
In-Reply-To: <AANLkTik-iwokJfwGpcrc=D7h+N9E=vU4Lr+AsnT69WJ0@mail.gmail.com>
References: <4D89BABF.4080806@orkash.com>
<4D89BCD6.6040604@gmail.com>
<4D89C704.5080603@orkash.com>
<c054efdf315e1df9376b2646077ef805.squirrel@sq.gransy.com>
<AANLkTinQTQ+g=fOokh6i7ZqBLoTSVpX_Mfy-7Lo8ndX4@mail.gmail.com>
<4D89D7AD.4050503@orkash.com>
<4D89D7C2.3040307@gmail.com>
<AANLkTik-iwokJfwGpcrc=D7h+N9E=vU4Lr+AsnT69WJ0@mail.gmail.com>
> 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.
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: adarsh.sharma@orkash.com, chetan.suttraway@enterprisedb.com, tv@fuzzy.cz
Subject: Re: Re-Reason of Slowness of Query
In-Reply-To: <4D89DBE6.10305@orkash.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox