Re: Re-Reason of Slowness of Query

tv(at)fuzzy(dot)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
>
>