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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
