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