From: Adarsh Sharma <adarsh.sharma@orkash.com>
To: tv@fuzzy.cz
Cc: pgsql-performance@postgresql.org
Subject: Re: Re-Reason of Slowness of Query
Date: Thu, 24 Mar 2011 10:22:54 +0530
Message-ID: <4D8ACE26.5070606@orkash.com> (raw)
In-Reply-To: <557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com>
References: <4D89BABF.4080806@orkash.com>
<4D89F6E5.7010803@peak6.com>
<557a8c61e87e5e54ae205daadd8f0ca4.squirrel@sq.gransy.com>
tv@fuzzy.cz wrote:
>> On 03/23/2011 04:17 AM, Adarsh Sharma wrote:
>>
>>
>>> 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);
>>>
>> You know... I'm surprised nobody has mentioned this, but DISTINCT is
>> very slow unless you have a fairly recent version of Postgres that
>> replaces it with something faster. Try this:
>>
>
> Nobody mentioned that because the explain plan already uses hash aggregate
> (instead of the old sort)
>
> HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual
> time=7047.259..7050.261 rows=72 loops=1)
>
> which means this is at least 8.4. Plus the 'distinct' step uses less than
> 1% of total time, so even if you improve it the impact will be minimal.
>
>
Yes, U"r absolutely right I am using Version 8.4SS and i am satisfied
with the below query results:
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=5149.308..5152.251 rows=72 loops=1)
-> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8)
(actual time=0.119..5148.954 rows=74 loops=1)
-> Seq Scan on page_content p (cost=0.00..87132.17
rows=428817 width=8) (actual time=0.021..444.487 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.009..0.009 rows=1
loops=428467)
Index Cond: (c.source_id = p.crawled_page_id)
Total runtime: 5155.874 ms
(6 rows)
I don't think that the above results are optimized further.
Thanks & best Regards,
Adarsh Sharma
> regards
> Tomas
>
>
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, tv@fuzzy.cz
Subject: Re: Re-Reason of Slowness of Query
In-Reply-To: <4D8ACE26.5070606@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