Re: Re-Reason of Slowness of Query

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