News Aggregator Query Plan
March 28,2005 query
select s.source_id,
s.link,
s.description,
s.title,
to_char(i.creation_date, 'YYYY-MM-DD HH24:MI:SS') as last_scanned,
to_char(i.creation_date, 'YYYY-MM-DD HH24') as sort_date,
s.feed_url,
i.item_id,
i.title as item_title,
i.link as item_link,
i.description as item_description,
i.content_encoded,
s.last_modified
from na_aggregators a join
na_subscriptions su on (a.aggregator_id = su.aggregator_id) join
na_sources s on (su.source_id = s.source_id) join
na_items i on (s.source_id = i.source_id)
where a.package_id = '15783'
and a.aggregator_id = '26202'
and i.item_id > coalesce(a.aggregator_bottom, 0)
order by i.item_id desc
limit 100
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------\
-------------------------------------------------
Limit (cost=12.27..736.74 rows=100 width=1603) (actual time=9616.603..9616.603 rows=0 loops=1)
-> Nested Loop (cost=12.27..1450672.37 rows=200238 width=1603) (actual time=9616.597..9616.597 rows=0 loops=1)
Join Filter: ("outer".source_id = "inner".source_id)
-> Nested Loop (cost=1.17..939499.58 rows=310600 width=1401) (actual time=0.397..9049.853 rows=275 loops=1)
Join Filter: ("outer".item_id > COALESCE("inner".aggregator_bottom, 0))
-> Index Scan Backward using na_items_pk on na_items i (cost=0.00..918532.96 rows=465899 width=1397) (actual time=0.153..4128.485 rows=334291 loops=1)
-> Materialize (cost=1.17..1.19 rows=2 width=8) (actual time=0.002..0.005 rows=1 loops=334291)
-> Seq Scan on na_aggregators a (cost=0.00..1.17 rows=2 width=8) (actual time=0.188..0.192 rows=1 loops=1)
Filter: ((package_id = 15783) AND (aggregator_id = 26202))
-> Materialize (cost=11.10..11.83 rows=73 width=218) (actual time=0.003..1.902 rows=73 loops=275)
-> Hash Join (cost=2.60..11.10 rows=73 width=218) (actual time=0.525..495.056 rows=73 loops=1)
Hash Cond: ("outer".source_id = "inner".source_id)
-> Seq Scan on na_sources s (cost=0.00..6.23 rows=123 width=210) (actual time=0.036..492.892 rows=126 loops=1)
-> Hash (cost=2.42..2.42 rows=72 width=8) (actual time=0.421..0.421 rows=0 loops=1)
-> Seq Scan on na_subscriptions su (cost=0.00..2.42 rows=72 width=8) (actual time=0.056..0.276 rows=73 loops=1)
Filter: (26202 = aggregator_id)
Total runtime: 9616.943 ms
(17 rows)
New query with join
select s.source_id,
s.link,
s.description,
s.title,
to_char(i.creation_date, 'YYYY-MM-DD HH24:MI:SS') as last_scanned,
to_char(i.creation_date, 'YYYY-MM-DD HH24') as sort_date,
s.feed_url,
i.item_id,
i.title as item_title,
i.link as item_link,
i.description as item_description,
i.content_encoded,
s.last_modified
from na_aggregators a join
na_subscriptions su on (a.aggregator_id = su.aggregator_id) join
na_sources s on (su.source_id = s.source_id) join
na_items i on (s.source_id = i.source_id)
left join na_saved_items nsa on (i.item_id=nsa.item_id and nsa.aggregator_id=:aggregator_id)
where a.package_id = :package_id
and a.aggregator_id = :aggregator_id
and ((i.item_id > coalesce(a.aggregator_bottom, 0)) or nsa.item_id is not null)
order by i.item_id desc
limit $sql_limit
-----
Query Plan
Limit (cost=14.98..302.54 rows=50 width=1603) (actual time=1547.185..146337.059 rows=50 loops=1)
-> Nested Loop Left Join (cost=14.98..3454782.08 rows=600712 width=1603) (actual time=1547.179..146336.885 rows=50 loops=1)
Join Filter: ("outer".item_id = "inner".item_id)
Filter: (("outer".item_id > COALESCE("outer".aggregator_bottom, 0)) OR ("inner".item_id IS NOT NULL))
-> Nested Loop (cost=12.27..1694693.21 rows=600712 width=1607) (actual time=204.012..144120.456 rows=5746 loops=1)
-> Nested Loop (cost=11.10..1683783.17 rows=272722 width=1607) (actual time=203.890..144029.996 rows=5746 loops=1)
Join Filter: ("outer".source_id = "inner".source_id)
-> Index Scan Backward using na_items_pk on na_items i (cost=0.00..918532.96 rows=465899 width=1397) (actual time=0.243\..3061.281 rows=571635 loops=1)
-> Materialize (cost=11.10..11.83 rows=73 width=218) (actual time=0.001..0.103 rows=71 loops=571635)
-> Hash Join (cost=2.60..11.10 rows=73 width=218) (actual time=0.837..107.232 rows=71 loops=1)
Hash Cond: ("outer".source_id = "inner".source_id)
-> Seq Scan on na_sources s (cost=0.00..6.23 rows=123 width=210) (actual time=0.045..105.099 rows=123 loops\=1)
-> Hash (cost=2.42..2.42 rows=72 width=8) (actual time=0.445..0.445 rows=0 loops=1)
-> Seq Scan on na_subscriptions su (cost=0.00..2.42 rows=72 width=8) (actual time=0.068..0.302 rows=7\1 loops=1)
Filter: (26202 = aggregator_id)
-> Materialize (cost=1.17..1.19 rows=2 width=8) (actual time=0.001..0.003 rows=1 loops=5746)
-> Seq Scan on na_aggregators a (cost=0.00..1.17 rows=2 width=8) (actual time=0.077..0.081 rows=1 loops=1)
Filter: ((package_id = 15783) AND (aggregator_id = 26202))
-> Materialize (cost=2.71..3.88 rows=117 width=4) (actual time=0.001..0.162 rows=119 loops=5746)
-> Seq Scan on na_saved_items nsa (cost=0.00..2.71 rows=117 width=4) (actual time=0.045..0.494 rows=119 loops=1)
Filter: (aggregator_id = 26202)
Total runtime: 146337.679 ms
(22 rows)
------
Actual Query
select s.source_id,
s.link,
s.description,
s.title,
to_char(i.creation_date, 'YYYY-MM-DD HH24:MI:SS') as last_scanned,
to_char(i.creation_date, 'YYYY-MM-DD HH24') as sort_date,
s.feed_url,
i.item_id,
i.title as item_title,
i.link as item_link,
i.description as item_description,
i.content_encoded,
s.last_modified
from na_aggregators a join
na_subscriptions su on (a.aggregator_id = su.aggregator_id) join
na_sources s on (su.source_id = s.source_id) join
na_items i on (s.source_id = i.source_id)
where a.package_id = '15783'
and a.aggregator_id = '26202'
and ((i.item_id > coalesce(a.aggregator_bottom, 0)) or
exists (select 1 from na_saved_items na where na.item_id=i.item_id and na.aggregator_id='26202'))
order by i.item_id desc
limit 50
QUERY PLAN \
-----------------------------------------------------------------------------------------------------------------------------------------------\----------------------
Limit (cost=10.91..316.19 rows=50 width=1723) (actual time=109.226..5629.299 rows=50 loops=1)
-> Nested Loop (cost=10.91..1319219.07 rows=216072 width=1723) (actual time=109.220..5629.145 rows=50 loops=1)
Join Filter: (("outer".item_id > COALESCE("inner".aggregator_bottom, 0)) OR (subplan))
-> Nested Loop (cost=9.75..455801.44 rows=147282 width=1727) (actual time=109.018..5626.633 rows=50 loops=1)
Join Filter: ("outer".source_id = "inner".source_id)
-> Index Scan Backward using na_items_pk on na_items i (cost=0.00..45810.19 rows=253075 width=1519) (actual time=0.168..366.11\0 rows=11643 loops=1)
-> Materialize (cost=9.75..10.47 rows=72 width=216) (actual time=0.001..0.239 rows=70 loops=11643)
-> Hash Join (cost=2.59..9.75 rows=72 width=216) (actual time=0.510..21.536 rows=70 loops=1)
Hash Cond: ("outer".source_id = "inner".source_id)
-> Seq Scan on na_sources s (cost=0.00..5.22 rows=122 width=208) (actual time=0.034..20.155 rows=122 loops=1)
-> Hash (cost=2.41..2.41 rows=71 width=8) (actual time=0.414..0.414 rows=0 loops=1)
-> Seq Scan on na_subscriptions su (cost=0.00..2.41 rows=71 width=8) (actual time=0.059..0.280 rows=70 loops\=1)
Filter: (26202 = aggregator_id)
-> Materialize (cost=1.17..1.19 rows=2 width=8) (actual time=0.004..0.005 rows=1 loops=50)
-> Seq Scan on na_aggregators a (cost=0.00..1.17 rows=2 width=8) (actual time=0.088..0.093 rows=1 loops=1)
Filter: ((package_id = 15783) AND (aggregator_id = 26202))
SubPlan
-> Seq Scan on na_saved_items na (cost=0.00..2.91 rows=1 width=0) (never executed)
Filter: ((item_id = $0) AND (aggregator_id = 26202))
Total runtime: 5629.641 ms
(20 rows)