Re: Re-Reason of Slowness of Query

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

tv@xxxxxxxx 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