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