Hi everybody.
I'm having an issues with wrong plan for query in PostgreSQL (version
8.3). EXPLAIN ANALYZE shows that there're a lot of places where
planner estimates row count totally wrong, like 1 instead of 12000+.
default_statistics_target variable is set to 100, and I tried to run
VACUUM ANALYZE many times.
Because of wrong estimation query planner uses nested loops instead of
hash joins and it results in very bad performance. Disabling nested
loops helps, but I want to understand what happens there and try to
avoid it in future.
Could you help me with it? Query and plan are below.
Thank you in advance,
Michael Korbakov
SELECT wide_stats.*, revenue * rev_share AS net_revenue, revenue *
(rev_share - partner_rev_share) AS gross_revenue,
(CASE clicks WHEN 0 THEN 0 ELSE revenue * rev_share /
clicks END) as net_rpc, (CASE clicks WHEN 0 THEN 0 ELSE revenue *
(rev_share - partner_rev_share) / clicks END) AS gross_rpc,
rev_share * wide_stats.ecpm AS net_ecpm, (rev_share -
partner_rev_share) * wide_stats.ecpm AS gross_ecpm,
partner_rev_share * revenue AS partner_revenue
FROM
(SELECT stats.id, stats.date, stats.domain_id, (CASE WHEN
stats.partner_id = 1 OR top_subparent = 1 THEN title ELSE real_title
END) AS title,
stats.pageviews, stats.subsequent_searches,
stats.searches, stats.clicks, stats.revenue, stats.country,
stats.approved,
stats.partner_id, shares.rev_share, (CASE top_subparent
WHEN 1 THEN 0 ELSE partners_shares.rev_share END) AS
partner_rev_share, stats.ctr, stats.rpc,
(CASE stats.searches WHEN 0 THEN 0 ELSE 1000 * revenue /
searches END) AS ecpm,
(SELECT name FROM partners WHERE id = top_subparent) AS
owner,
subparents.top_subparent
FROM reports.daily_domain_reports AS stats
LEFT JOIN materialized_top_subparents AS subparents ON
stats.partner_id = subparents.partner_id AND subparents.parent_id = 1
LEFT JOIN reports.monthly_shares_with_parents_materialized AS
shares ON date_part('year'::text, stats.date) = shares.year AND
date_part('month'::text, stats.date) = shares.month AND
shares.partner_id = 1
LEFT JOIN reports.monthly_shares_with_parents_materialized AS
partners_shares ON date_part('year'::text, stats.date) =
partners_shares.year AND date_part('month'::text, stats.date) =
partners_shares.month AND partners_shares.partner_id = top_subparent
WHERE stats.partner_id = 1 OR top_subparent IN (SELECT partners.id
FROM partners WHERE parent_id = 1)
) AS wide_stats WHERE date >= '2009-08-01' AND date < '2009-09-02';
Nested Loop (cost=11.80..172.48 rows=1 width=94) (actual
time=93.792..14485.092 rows=12745 loops=1)
-> Nested Loop (cost=11.80..168.94 rows=1 width=56) (actual
time=93.739..13342.157 rows=12745 loops=1)
-> Nested Loop (cost=11.80..168.62 rows=1 width=60) (actual
time=93.734..13227.265 rows=12745 loops=1)
Join Filter: (COALESCE((domain_stats.date <=
domain_mappings.end_date), true) AND ((shares.year)::double precision
= date_part('year'::text, (domain_stats.date)::timestamp without time
zone)) AND ((shares.month)::double precision = date_part
('month'::text, (domain_stats.date)::timestamp without time zone)))
-> Nested Loop (cost=11.80..31.74 rows=1 width=48)
(actual time=0.258..26.950 rows=6069 loops=1)
Join Filter: ((domain_mappings.partner_id = 1) OR
(hashed subplan))
-> Nested Loop (cost=8.50..27.28 rows=1
width=32) (actual time=0.114..9.298 rows=567 loops=1)
-> Hash Join (cost=8.50..25.11 rows=1
width=28) (actual time=0.092..1.864 rows=560 loops=1)
Hash Cond:
(((partners_shares.year)::double precision = (shares.year)::double
precision) AND ((partners_shares.month)::double precision =
(shares.month)::double precision))
-> Seq Scan on
monthly_shares_with_parents_materialized partners_shares
(cost=0.00..9.60 rows=560 width=16) (actual time=0.009..0.336 rows=560
loops=1)
-> Hash (cost=8.39..8.39 rows=7
width=12) (actual time=0.059..0.059 rows=7 loops=1)
-> Bitmap Heap Scan on
monthly_shares_with_parents_materialized shares (cost=4.30..8.39
rows=7 width=12) (actual time=0.033..0.041 rows=7 loops=1)
Recheck Cond: (partner_id
= 1)
-> Bitmap Index Scan on
monthly_shares_with_parents_materialized_pkey (cost=0.00..4.30 rows=7
width=0) (actual time=0.027..0.027 rows=7 loops=1)
Index Cond:
(partner_id = 1)
-> Index Scan using
materialized_top_subparents_pkey on materialized_top_subparents
subparents (cost=0.00..2.16 rows=1 width=8) (actual time=0.010..0.011
rows=1 loops=560)
Index Cond: ((subparents.parent_id =
1) AND (subparents.top_subparent = partners_shares.partner_id))
-> Index Scan using ix_domain_mappings_partner_id
on domain_mappings (cost=0.00..0.97 rows=11 width=16) (actual
time=0.004..0.012 rows=11 loops=567)
Index Cond: (domain_mappings.partner_id =
subparents.partner_id)
SubPlan
-> Seq Scan on partners (cost=0.00..3.12
rows=71 width=4) (actual time=0.005..0.059 rows=71 loops=1)
Filter: (parent_id = 1)
-> Index Scan using uix_date_domain_country on
domain_stats (cost=0.00..136.65 rows=6 width=36) (actual
time=0.653..2.089 rows=15 loops=6069)
Index Cond: ((domain_stats.date >=
'2009-08-01'::date) AND (domain_stats.date < '2009-09-02'::date) AND
(domain_stats.date >= domain_mappings.start_date) AND
(domain_stats.domain_id = domain_mappings.domain_id))
-> Index Scan using partners_pkey on partners
(cost=0.00..0.31 rows=1 width=4) (actual time=0.006..0.007 rows=1
loops=12745)
Index Cond: (public.partners.id =
domain_mappings.partner_id)
-> Index Scan using domains_pkey on domains (cost=0.00..0.29
rows=1 width=46) (actual time=0.007..0.008 rows=1 loops=12745)
Index Cond: (domains.id = domain_stats.domain_id)
SubPlan
-> Seq Scan on partners (cost=0.00..3.12 rows=1 width=3) (actual
time=0.044..0.064 rows=1 loops=12745)
Filter: (id = $0)
Total runtime: 14491.142 ms
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
