Re: Redundant sub query triggers slow nested loop left join

One interesting other thing to note; if I remove the banners_links.status =
0 condition from the query altogether the execution times improve
dramatically again. The results are not correct right now, but if worse
comes to worst I can always remove the unwanted rows in a procedural
language (it's a simple case of iterating a resultset and omitting rows with
status 1). Of course this would not really be a neat solution.

Anyway, the plan without the status = 0 condition now looks like this:

Sort (cost=6058.87..6058.88 rows=2 width=597) (actual time=305.869..306.138
rows=658 loops=1)
Sort Key: public.banners_links.id
-> Nested Loop Left Join (cost=5051.23..6058.86 rows=2 width=597)
(actual time=69.956..304.259 rows=658 loops=1)
Join Filter: (public.banners_links.id =
public.fetch_banners.banners_links_id)
-> Nested Loop Left Join (cost=5048.26..6051.92 rows=2 width=527)
(actual time=69.715..249.122 rows=658 loops=1)
Join Filter: (public.banners_links.id =
reward_ratings.banner_id)
-> Nested Loop Left Join (cost=3441.91..4441.39 rows=2
width=519) (actual time=57.795..235.954 rows=658 loops=1)
Join Filter: (public.banners_links.id =
ecpc_per_banner_link.banners_links_id)
-> Nested Loop (cost=1563.28..2554.02 rows=2
width=503) (actual time=35.359..42.018 rows=658 loops=1)
-> Hash Left Join (cost=1563.28..2545.93 rows=2
width=124) (actual time=35.351..37.987 rows=658 loops=1)
Hash Cond: (public.banners_links.id =
users_banners_tot_sub.banner_id)
-> Hash Left Join (cost=1546.63..2529.27
rows=2 width=116) (actual time=30.757..32.552 rows=658 loops=1)
Hash Cond: (public.banners_links.id =
banners_banner_types.banner_id)
-> Hash Left Join
(cost=108.08..1090.62 rows=2 width=81) (actual time=6.087..7.085 rows=424
loops=1)
Hash Cond:
(public.banners_links.id = special_deals.id)
Filter:
(special_deals.special_deal IS NULL)
-> Bitmap Heap Scan on
banners_links (cost=11.54..952.02 rows=424 width=73) (actual
time=0.125..0.514 rows=424 loops=1)
Recheck Cond: (merchant_id
= 5631)
-> Bitmap Index Scan on
banners_links_merchant_id_idx (cost=0.00..11.43 rows=424 width=0) (actual
time=0.089..0.089 rows=424 loops=1)
Index Cond:
(merchant_id = 5631)
-> Hash (cost=86.93..86.93
rows=769 width=16) (actual time=5.951..5.951 rows=780 loops=1)
-> Subquery Scan
special_deals (cost=69.62..86.93 rows=769 width=16) (actual
time=4.164..5.389 rows=780 loops=1)
-> HashAggregate
(cost=69.62..79.24 rows=769 width=16) (actual time=4.164..4.670 rows=780
loops=1)
-> Seq Scan
on banner_deals (cost=0.00..53.75 rows=3175 width=16) (actual
time=0.005..1.496 rows=3175 loops=1)
-> Hash (cost=1432.13..1432.13
rows=514 width=43) (actual time=24.661..24.661 rows=658 loops=1)
-> Hash Join
(cost=959.77..1432.13 rows=514 width=43) (actual time=1.780..24.147 rows=658
loops=1)
Hash Cond:
(banners_banner_types.type_id = banner_types.id)
-> Hash IN Join
(cost=957.32..1422.52 rows=540 width=16) (actual time=1.738..23.332 rows=658
loops=1)
Hash Cond:
(banners_banner_types.banner_id = public.banners_links.id)
-> Seq Scan on
banners_banner_types (cost=0.00..376.40 rows=22240 width=16) (actual
time=0.005..10.355 rows=22240 loops=1)
-> Hash
(cost=952.02..952.02 rows=424 width=8) (actual time=0.808..0.808 rows=424
loops=1)
-> Bitmap
Heap Scan on banners_links (cost=11.54..952.02 rows=424 width=8) (actual
time=0.114..0.515 rows=424 loops=1)
Recheck
Cond: (merchant_id = 5631)
->
Bitmap Index Scan on banners_links_merchant_id_idx (cost=0.00..11.43
rows=424 width=0) (actual time=0.085..0.085 rows=424 loops=1)

Index Cond: (merchant_id = 5631)
-> Hash (cost=2.20..2.20
rows=20 width=43) (actual time=0.034..0.034 rows=20 loops=1)
-> Seq Scan on
banner_types (cost=0.00..2.20 rows=20 width=43) (actual time=0.004..0.016
rows=20 loops=1)
-> Hash (cost=16.63..16.63 rows=1
width=24) (actual time=4.582..4.582 rows=424 loops=1)
-> Subquery Scan
users_banners_tot_sub (cost=16.61..16.63 rows=1 width=24) (actual
time=3.548..4.235 rows=424 loops=1)
-> HashAggregate
(cost=16.61..16.62 rows=1 width=24) (actual time=3.547..3.850 rows=424
loops=1)
-> Nested Loop
(cost=0.00..16.60 rows=1 width=24) (actual time=0.031..3.085 rows=424
loops=1)
-> Index Scan using
users_banners_affiliate_id_idx on users_banners (cost=0.00..8.30 rows=1
width=16) (actual time=0.021..0.516 rows=424 loops=1)
Index Cond:
((affiliate_id = 5631) AND (affiliate_id = 5631))
Filter:
((status)::text = '3'::text)
-> Index Scan using
users_banners_id_idx on users_banners_rotation (cost=0.00..8.29 rows=1
width=16) (actual time=0.003..0.004 rows=1 loops=424)
Index Cond:
(users_banners_rotation.users_banners_id = users_banners.id)
-> Index Scan using banners_org_id_banner.idx on
banners_org (cost=0.00..4.03 rows=1 width=387) (actual time=0.003..0.004
rows=1 loops=658)
Index Cond: (public.banners_links.id =
banners_org.id_banner)
-> Materialize (cost=1878.63..1880.57 rows=194
width=20) (actual time=0.034..0.153 rows=290 loops=658)
-> Sort (cost=1876.01..1876.50 rows=194
width=30) (actual time=22.105..22.230 rows=290 loops=1)
Sort Key: CASE WHEN
(precalculated_stats_banners_links.clicks_total > 0) THEN
(((precalculated_stats_banners_links.revenue_total_affiliate /
(precalculated_stats_banners_links.clicks_total)::numeric))::double
precision / 1000::double precision) ELSE 0::double precision END
-> Merge IN Join (cost=1819.78..1868.64
rows=194 width=30) (actual time=16.723..21.832 rows=290 loops=1)
Merge Cond:
(precalculated_stats_banners_links.banners_links_id =
public.banners_links.id)
-> Sort (cost=849.26..869.24
rows=7993 width=30) (actual time=12.474..15.725 rows=7923 loops=1)
Sort Key:
precalculated_stats_banners_links.banners_links_id
-> Index Scan using
pre_calc_banners_status on precalculated_stats_banners_links
(cost=0.00..331.13 rows=7993 width=30) (actual time=0.007..6.220 rows=7923
loops=1)
Index Cond: (status = 4)
-> Sort (cost=970.52..971.58
rows=424 width=8) (actual time=0.862..1.012 rows=366 loops=1)
Sort Key:
public.banners_links.id
-> Bitmap Heap Scan on
banners_links (cost=11.54..952.02 rows=424 width=8) (actual
time=0.121..0.490 rows=424 loops=1)
Recheck Cond: (merchant_id
= 5631)
-> Bitmap Index Scan on
banners_links_merchant_id_idx (cost=0.00..11.43 rows=424 width=0) (actual
time=0.087..0.087 rows=424 loops=1)
Index Cond:
(merchant_id = 5631)
-> Materialize (cost=1606.35..1607.28 rows=93 width=16)
(actual time=0.019..0.019 rows=0 loops=658)
-> Hash IN Join (cost=957.32..1606.25 rows=93
width=16) (actual time=11.916..11.916 rows=0 loops=1)
Hash Cond: (reward_ratings.banner_id =
public.banners_links.id)
-> Seq Scan on reward_ratings (cost=0.00..633.66
rows=3826 width=16) (actual time=0.016..9.190 rows=4067 loops=1)
Filter: ((now() >= period_start) AND (now()
<= period_end))
-> Hash (cost=952.02..952.02 rows=424 width=8)
(actual time=0.738..0.738 rows=424 loops=1)
-> Bitmap Heap Scan on banners_links
(cost=11.54..952.02 rows=424 width=8) (actual time=0.118..0.459 rows=424
loops=1)
Recheck Cond: (merchant_id = 5631)
-> Bitmap Index Scan on
banners_links_merchant_id_idx (cost=0.00..11.43 rows=424 width=0) (actual
time=0.086..0.086 rows=424 loops=1)
Index Cond: (merchant_id = 5631)
-> Materialize (cost=2.97..3.85 rows=88 width=78) (actual
time=0.000..0.037 rows=88 loops=658)
-> Seq Scan on fetch_banners (cost=0.00..2.88 rows=88
width=78) (actual time=0.005..0.052 rows=88 loops=1)
Total runtime: 306.734 ms

_________________________________________________________________
Play online games with your friends with Messenger
http://www.join.msn.com/messenger/overview