Re-Reason of Slowness of Query - Mailing list pgsql-performance
From | Adarsh Sharma |
---|---|
Subject | Re-Reason of Slowness of Query |
Date | |
Msg-id | 4D89BABF.4080806@orkash.com Whole thread Raw |
Responses |
Re: Re-Reason of Slowness of Query
Re: Re-Reason of Slowness of Query |
List | pgsql-performance |
Thanks Chetan, After my Lunch Break, I tried the below steps :
My original query was :
explain analyze select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id != c.source_id
which hangs because it is wrong query to fetch the desired output .
Next Updated Query be Chetan Suttraway :
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);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7192.843..7195.923 rows=72 loops=1)
-> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.040..7192.426 rows=74 loops=1)
-> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.009..395.599 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.014..0.014 rows=1 loops=428467)
Index Cond: (c.source_id = p.crawled_page_id)
Total runtime: 7199.748 ms
(6 rows)
I think it is very much faster but I don't understand the query :
explain select distinct(b) from t1,t2 where t1.b >t2.d union all select distinct(b) from t1,t2 where t1.b <t2.d;
As i transform it into my format as:
explain select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id > c.source_id union all select distinct(p.crawled_page_id) from page_content p,clause2 c where p.crawled_page_id < c.source_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..296085951076.34 rows=765318 width=8)
-> Unique (cost=0.00..148042971711.58 rows=382659 width=8)
-> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8)
-> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8)
-> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4)
Index Cond: (p.crawled_page_id > c.source_id)
-> Unique (cost=0.00..148042971711.58 rows=382659 width=8)
-> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8)
-> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8)
-> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4)
Index Cond: (p.crawled_page_id < c.source_id)
(11 rows)
I don't think this is correct because it produce 11 rows output.
Any further suggestions, Please guide.
Thanks & best Regards,
Adarsh Sharma
My original query was :
explain analyze select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id != c.source_id
which hangs because it is wrong query to fetch the desired output .
Next Updated Query be Chetan Suttraway :
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);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=100278.16..104104.75 rows=382659 width=8) (actual time=7192.843..7195.923 rows=72 loops=1)
-> Nested Loop Anti Join (cost=0.00..99320.46 rows=383079 width=8) (actual time=0.040..7192.426 rows=74 loops=1)
-> Seq Scan on page_content p (cost=0.00..87132.17 rows=428817 width=8) (actual time=0.009..395.599 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.014..0.014 rows=1 loops=428467)
Index Cond: (c.source_id = p.crawled_page_id)
Total runtime: 7199.748 ms
(6 rows)
I think it is very much faster but I don't understand the query :
explain select distinct(b) from t1,t2 where t1.b >t2.d union all select distinct(b) from t1,t2 where t1.b <t2.d;
As i transform it into my format as:
explain select distinct(p.crawled_page_id) from page_content p , clause2 c where p.crawled_page_id > c.source_id union all select distinct(p.crawled_page_id) from page_content p,clause2 c where p.crawled_page_id < c.source_id;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
Append (cost=0.00..296085951076.34 rows=765318 width=8)
-> Unique (cost=0.00..148042971711.58 rows=382659 width=8)
-> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8)
-> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8)
-> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4)
Index Cond: (p.crawled_page_id > c.source_id)
-> Unique (cost=0.00..148042971711.58 rows=382659 width=8)
-> Nested Loop (cost=0.00..136655213119.84 rows=4555103436696 width=8)
-> Index Scan using idx_page_id on page_content p (cost=0.00..174214.02 rows=428817 width=8)
-> Index Scan using idx_clause2_source_id on clause2 c (cost=0.00..185898.05 rows=10622488 width=4)
Index Cond: (p.crawled_page_id < c.source_id)
(11 rows)
I don't think this is correct because it produce 11 rows output.
Any further suggestions, Please guide.
Thanks & best Regards,
Adarsh Sharma
pgsql-performance by date: