Re: Redundant sub query triggers slow nested loop left join

>Well, that's darn odd. It should not be getting that so far wrong.
>What's the datatype of the status column exactly (I'm guessing varchar
>but maybe not)? Would you show us the pg_stats row for the status column?

It has been created as a char(1) in fact. The pg_stats row for the status
column is:

public|banners_links|status|0|5|2|{0,1}|{0.626397,0.373603}||0.560611

>I'm not sure why, but I think it must have something to do
>with the subquery structure of your query. Were you showing us the
>whole truth about your query, or were there details you left out?

The query I gave in the opening post was just a small part, the part that I
initially identified as the 'slow path'. The last plan I gave was from the
whole query, without any details left out. I didn't gave the SQL of that
yet, so here it is:

SELECT
id,
status,
merchant_id,
description,
org_text,
users_banners_id,
banner_url,
cookie_redirect,
type,

CASE WHEN special_deal IS null THEN
''
ELSE
'special deal'
END AS special_deal,

CASE WHEN url_of_banner IS null THEN
''
ELSE
url_of_banner
END AS url_of_banner,

CASE WHEN period_end IS NULL THEN
'not_active'
ELSE
'active'
END AS active_not_active,

CASE WHEN ecpc IS NULL THEN
0.00
ELSE
ROUND(ecpc::numeric,2)
END AS ecpc,

CASE WHEN ecpc_merchant IS NULL THEN
0.00
ELSE
ROUND(ecpc_merchant::numeric,2)
END AS ecpc_merchant

FROM
/* SUBQUERY grand_total_fetch_banners */ (
/* SUBQUERY grand_total */(
/* SUBQUERY banners_special_deals */ (

/* SUBQUERY banners */ (
SELECT
*
FROM
/* SUBQUERY banners_links */ (
SELECT
banners_links.id,
merchant_id,
banners_org.banner_text AS org_text,
description,
status,
banner_url,
ecpc,
ecpc_merchant,
COALESCE(cookie_redirect,0) AS cookie_redirect
FROM
/* SUBQUERY banners_links */ (

/* subselect tot join ecpc_per_banner_links on banners_links*/
/* SUBQUERY banners_links */ (
SELECT
*
FROM
banners_links
WHERE
merchant_id = 5631
) AS banners_links

LEFT OUTER JOIN

/* SUBQUERY ecpc_per_banner_link */ (
SELECT
CASE WHEN clicks_total > 0 THEN
(revenue_total_affiliate/clicks_total)::float/1000.0
ELSE
0.0
END AS ecpc,
CASE WHEN clicks_total > 0 THEN
(revenue_total/clicks_total)::float/1000.0
ELSE
0.0
END AS ecpc_merchant,
banners_links_id
FROM
precalculated_stats_banners_links
WHERE
status = 4 AND
banners_links_id IN /* SUBQUERY */ (
SELECT
id
FROM
banners_links
WHERE
merchant_id = 5631
)
ORDER BY
ecpc DESC
) AS ecpc_per_banner_link

ON (banners_links.id = ecpc_per_banner_link.banners_links_id)
) AS banners_links

,

banners_org

WHERE
merchant_id = 5631 AND
banners_links.id = banners_org.id_banner AND
(banners_links.id = -1 OR -1 = -1) AND
(banners_links.status = 0 OR 0 = -1)
) AS banners_links

LEFT OUTER JOIN

/* SUBQUERY users_banners_tot_sub */(
SELECT
MAX (users_banners_id) AS users_banners_id,
merchant_users_banners_id,
banner_id
FROM
/* SUBQUERY users_banners_rotations_sub */(
SELECT
affiliate_id AS merchant_users_banners_id,
users_banners.id AS users_banners_id,
users_banners_rotation.banner_id
FROM
users_banners, users_banners_rotation
WHERE
affiliate_id = 5631 AND
users_banners_rotation.users_banners_id = users_banners.id AND
users_banners.status = 3
) AS users_banners_rotations_sub
GROUP BY
merchant_users_banners_id,banner_id
) AS users_banners_tot_sub

ON (
banners_links.id = users_banners_tot_sub.banner_id AND
banners_links.merchant_id =
users_banners_tot_sub.merchant_users_banners_id
)
) AS banners

LEFT OUTER JOIN

/* SUBQUERY special_deals */(
SELECT
banner_deals.banner_id AS id,
MAX(affiliate_id) AS special_deal
FROM
banner_deals
GROUP BY
banner_deals.banner_id
) AS special_deals

USING (id)

) AS banners_special_deals

LEFT OUTER JOIN

/* SUBQUERY types */ (
SELECT
banner_types.id AS type_id,
banner_types.type AS type,
banners_banner_types.banner_id AS id
FROM
banner_types,banners_banner_types
WHERE
banners_banner_types.banner_id IN /* SUBQUERY */ (
SELECT
id
FROM
banners_links
WHERE
merchant_id = 5631
) AND
banners_banner_types.type_id = banner_types.id
) AS types

USING (id)

) as grand_total

LEFT OUTER JOIN

/* SUBQUERY fetch_banners */ (
SELECT
banners_links_id AS id,
url_of_banner
FROM
fetch_banners
) AS fetch_banners

USING (id)
) AS grand_total_fetch_banners

LEFT OUTER JOIN

/* SUBQUERY active_banners */ (
SELECT
banner_id AS id,
period_end
FROM
reward_ratings
WHERE
now() BETWEEN period_start AND period_end
AND
banner_id IN /* SUBQUERY */ (
SELECT
id
FROM
banners_links
WHERE
merchant_id = 5631
)
) AS active_banners

USING (id)

WHERE
(type_id = -1 OR -1 = -1 ) AND
(special_deal IS null)

ORDER BY
id DESC

This is the original query without even the earlier mentioned redundant
check removed. For this query, PG 8.2 creates the following plan:

Sort (cost=5094.40..5094.41 rows=1 width=597) (actual
time=15282.503..15282.734 rows=553 loops=1)
Sort Key: public.banners_links.id
-> Nested Loop Left Join (cost=3883.68..5094.39 rows=1 width=597)
(actual time=64.066..15280.773 rows=553 loops=1)
Join Filter: (public.banners_links.id = reward_ratings.banner_id)
-> Nested Loop Left Join (cost=2926.37..3486.98 rows=1 width=589)
(actual time=51.992..9231.245 rows=553 loops=1)
Join Filter: (public.banners_links.id =
public.fetch_banners.banners_links_id)
-> Nested Loop Left Join (cost=2926.37..3483.00 rows=1
width=519) (actual time=51.898..9183.007 rows=553 loops=1)
Join Filter: (public.banners_links.id =
ecpc_per_banner_link.banners_links_id)
-> Nested Loop (cost=1050.35..1602.14 rows=1
width=503) (actual time=29.585..9015.077 rows=553 loops=1)
-> Nested Loop Left Join (cost=1050.35..1593.86
rows=1 width=124) (actual time=29.577..9010.273 rows=553 loops=1)
Join Filter: (public.banners_links.id =
users_banners_tot_sub.banner_id)
-> Nested Loop Left Join
(cost=1033.74..1577.21 rows=1 width=116) (actual time=25.904..8738.006
rows=553 loops=1)
Join Filter: (public.banners_links.id
= special_deals.id)
Filter: (special_deals.special_deal IS
NULL)
-> Nested Loop Left Join
(cost=964.12..1480.67 rows=1 width=108) (actual time=20.905..8259.497
rows=553 loops=1)
Join Filter:
(public.banners_links.id = banners_banner_types.banner_id)
-> Bitmap Heap Scan on
banners_links (cost=4.35..42.12 rows=1 width=73) (actual time=0.160..1.122
rows=359 loops=1)
Recheck Cond:
((merchant_id = 5631) AND (merchant_id = 5631))
Filter: ((status)::text =
'0'::text)
-> Bitmap Index Scan on
banners_links_merchant_id_idx (cost=0.00..4.35 rows=10 width=0) (actual
time=0.123..0.123 rows=424 loops=1)
Index Cond:
((merchant_id = 5631) AND (merchant_id = 5631))
-> Hash Join
(cost=959.77..1432.13 rows=514 width=43) (actual time=0.899..22.685 rows=658
loops=359)
Hash Cond:
(banners_banner_types.type_id = banner_types.id)
-> Hash IN Join
(cost=957.32..1422.52 rows=540 width=16) (actual time=0.897..21.946 rows=658
loops=359)
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.004..10.164 rows=22240 loops=359)
-> Hash
(cost=952.02..952.02 rows=424 width=8) (actual time=0.790..0.790 rows=424
loops=1)
-> Bitmap
Heap Scan on banners_links (cost=11.54..952.02 rows=424 width=8) (actual
time=0.108..0.503 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.078..0.078 rows=424 loops=1)

Index Cond: (merchant_id = 5631)
-> Hash (cost=2.20..2.20
rows=20 width=43) (actual time=0.033..0.033 rows=20 loops=1)
-> Seq Scan on
banner_types (cost=0.00..2.20 rows=20 width=43) (actual time=0.004..0.017
rows=20 loops=1)
-> HashAggregate (cost=69.62..79.24
rows=769 width=16) (actual time=0.008..0.498 rows=780 loops=553)
-> Seq Scan on banner_deals
(cost=0.00..53.75 rows=3175 width=16) (actual time=0.004..1.454 rows=3175
loops=1)
-> HashAggregate (cost=16.61..16.62 rows=1
width=24) (actual time=0.007..0.291 rows=424 loops=553)
-> Nested Loop (cost=0.00..16.60
rows=1 width=24) (actual time=0.056..3.123 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.046..0.555 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..8.27 rows=1 width=387) (actual time=0.005..0.006
rows=1 loops=553)
Index Cond: (public.banners_links.id =
banners_org.id_banner)
-> Sort (cost=1876.01..1876.50 rows=194 width=30)
(actual time=0.041..0.161 rows=290 loops=553)
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.769..21.879 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.486..15.740 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.291 rows=7923
loops=1)
Index Cond: (status = 4)
-> Sort (cost=970.52..971.58 rows=424
width=8) (actual time=0.879..1.023 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.123..0.509 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)
-> Seq Scan on fetch_banners (cost=0.00..2.88 rows=88
width=78) (actual time=0.003..0.042 rows=88 loops=553)
-> Hash IN Join (cost=957.32..1606.24 rows=93 width=16) (actual
time=10.933..10.933 rows=0 loops=553)
Hash Cond: (reward_ratings.banner_id =
public.banners_links.id)
-> Seq Scan on reward_ratings (cost=0.00..633.66 rows=3822
width=16) (actual time=0.007..8.955 rows=4067 loops=553)
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.475 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)
Total runtime: 15283.225 ms

If I change 1 of the redundant checks:

/* SUBQUERY banners_links */ (
SELECT
*
FROM
banners_links
WHERE
merchant_id = 5631
) AS banners_links

into just banner_links, PG comes up with the (large) plan I posted earlier.

_________________________________________________________________
Live Search, for accurate results! http://www.live.nl