Обсуждение: Bad Row Count Estimate on View with 8.2
Hello, I discovered a query which is taking 70 seconds on 8.2.1 which used to take under a second on 8.1.2. I was digging into what was causing it and I believe the problem is a view which the planner estimates will return 1 row when it actually returns 3500. When I join with the view, the planner ends up using a nested loop because it thinks the right branch will run once instead of 3500 times. I've analyzed all the tables and played around with the default_statistics_target, but still the planner estimates 1 row. I was wondering if anybody else has any other ideas? Here is the query the view is defined as: SELECT foo.fund_id, foo.owner_trader_id, foo.strategy_id, foo.cf_account_id, foo.instrument_id, sum(foo.pos) AS pos, sum(foo.cost) AS cost FROM ( SELECT om_position.fund_id, om_position.owner_trader_id, om_position.strategy_id, om_position.cf_account_id, om_position.instrument_id, om_position.pos, om_position.cost FROM om_position WHERE om_position.as_of_date = date(now()) UNION ALL SELECT om_trade.fund_id, om_trade.owner_trader_id, om_trade.strategy_id, om_trade.cf_account_id, om_trade.instrument_id, om_trade.qty::numeric(22,9) AS pos, om_trade.cost FROM om_trade WHERE om_trade.process_state = 0 OR om_trade.process_state = 2 ) foo GROUP BY foo.fund_id, foo.owner_trader_id, foo.strategy_id, foo.cf_account_id, foo.instrument_id; Here is explain analyze from both 8.1.2 and 8.2.1 with default_statistics_target=10 and tables freshly analyzed: 8.1.2 HashAggregate (cost=4760.33..4764.95 rows=308 width=168) (actual time=56.873..71.293 rows=3569 loops=1) -> Append (cost=0.00..4675.85 rows=3072 width=54) (actual time=0.037..38.261 rows=3715 loops=1) -> Index Scan using as_of_date_om_position_index on om_position (cost=0.00..4637.10 rows=3071 width=54) (actual time=0.031..14.722 rows=3559 loops=1) Index Cond: (as_of_date = date(now())) -> Bitmap Heap Scan on om_trade (cost=4.01..8.03 rows=1 width=48) (actual time=0.118..0.917 rows=156 loops=1) Recheck Cond: ((process_state = 0) OR (process_state = 2)) -> BitmapOr (cost=4.01..4.01 rows=1 width=0) (actual time=0.079..0.079 rows=0 loops=1) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.060..0.060 rows=156 loops=1) Index Cond: (process_state = 0) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..2.00 rows=1 width=0) (actual time=0.008..0.008 rows=0 loops=1) Index Cond: (process_state = 2) Total runtime: 82.398 ms 8.2.1 HashAggregate (cost=6912.51..6912.53 rows=1 width=200) (actual time=19.005..24.137 rows=3569 loops=1) -> Append (cost=0.00..6406.73 rows=28902 width=200) (actual time=0.037..11.569 rows=3715 loops=1) -> Index Scan using as_of_date_om_position_index on om_position (cost=0.00..4333.82 rows=2964 width=53) (actual time=0.035..4.884 rows=3559 loops=1) Index Cond: (as_of_date = date(now())) -> Bitmap Heap Scan on om_trade (cost=464.40..1783.89 rows=25938 width=49) (actual time=0.060..0.380 rows=156 loops=1) Recheck Cond: ((process_state = 0) OR (process_state = 2)) -> BitmapOr (cost=464.40..464.40 rows=308 width=0) (actual time=0.041..0.041 rows=0 loops=1) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..225.72 rows=154 width=0) (actual time=0.032..0.032 rows=156 loops=1) Index Cond: (process_state = 0) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..225.72 rows=154 width=0) (actual time=0.003..0.003 rows=0 loops=1) Index Cond: (process_state = 2) Total runtime: 27.193 ms Here is explain analyze from 8.2.1 with default_statistics_target=1000 and tables freshly analyzed: HashAggregate (cost=5344.36..5344.37 rows=1 width=200) (actual time=18.826..23.950 rows=3569 loops=1) -> Append (cost=0.00..5280.01 rows=3677 width=200) (actual time=0.031..11.606 rows=3715 loops=1) -> Index Scan using as_of_date_om_position_index on om_position (cost=0.00..5224.44 rows=3502 width=54) (actual time=0.029..4.903 rows=3559 loops=1) Index Cond: (as_of_date = date(now())) -> Bitmap Heap Scan on om_trade (cost=9.91..18.79 rows=175 width=49) (actual time=0.069..0.394 rows=156 loops=1) Recheck Cond: ((process_state = 0) OR (process_state = 2)) -> BitmapOr (cost=9.91..9.91 rows=2 width=0) (actual time=0.050..0.050 rows=0 loops=1) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..5.57 rows=2 width=0) (actual time=0.039..0.039 rows=156 loops=1) Index Cond: (process_state = 0) -> Bitmap Index Scan on om_trade_partial_process_state_index (cost=0.00..4.26 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=1) Index Cond: (process_state = 2) Total runtime: 27.055 ms Thanks, Dave Dutcher Telluride Asset Management 952.653.6411
"Dave Dutcher" <dave@tridecap.com> writes: > I discovered a query which is taking 70 seconds on 8.2.1 which used to take > under a second on 8.1.2. I was digging into what was causing it and I > believe the problem is a view which the planner estimates will return 1 row > when it actually returns 3500. This is evidently a failure of estimate_num_groups(). However, I do not see any difference in that code between 8.1 and 8.2 branch tips. I do notice a possibly-relevant change that was applied in 8.1.4: 2006-05-02 00:34 tgl * src/backend/: optimizer/path/allpaths.c, utils/adt/selfuncs.c (REL8_1_STABLE): Avoid assuming that statistics for a parent relation reflect the properties of the union of its child relations as well. This might have been a good idea when it was originally coded, but it's a fatally bad idea when inheritance is being used for partitioning. It's better to have no stats at all than completely misleading stats. Per report from Mark Liberman. The bug arguably exists all the way back, but I've only patched HEAD and 8.1 because we weren't particularly trying to support partitioning before 8.1. Eventually we ought to look at deriving union statistics instead of just punting, but for now the drop kick looks good. I think this was only meant to apply to table inheritance situations, but on reflection it might affect UNION queries too. The question is whether the numbers it was using before really mean anything --- they seem to have been better-than-nothing in your particular case, but I'm not sure that translates to a conclusion that we should depend on 'em. In fact, since there isn't any "parent relation" in a UNION, I'm not sure that this patch actually changed your results ... but I'm not seeing what else would've ... regards, tom lane
> From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane > > > In fact, since there isn't any "parent relation" in a UNION, I'm not > sure that this patch actually changed your results ... but I'm not > seeing what else would've ... > Thanks for looking into it. I thought I might actually test if it was the patch you mentioned which changed my results, but I haven't had time. Because you mentioned it was grouping on the results of a UNION ALL which was throwing off the row estimate I changed my query from a UNION ALL/GROUP BY to a GROUP BY/FULL OUTER JOIN. The view runs a hair slower by itself, but the better estimate of rows makes it work much better for joining with. If anybody is curious, this is what I changed too: SELECT coalesce(pos_set.fund_id, trade_set.fund_id) as fund_id, coalesce(pos_set.owner_trader_id, trade_set.owner_trader_id) as owner_trader_id, coalesce(pos_set.strategy_id, trade_set.strategy_id) as strategy_id, coalesce(pos_set.cf_account_id, trade_set.cf_account_id) as cf_account_id, coalesce(pos_set.instrument_id, trade_set.instrument_id) as instrument_id, coalesce(pos_set.pos, 0) + coalesce(trade_set.pos, 0) as pos, coalesce(pos_set.cost, 0) + coalesce(trade_set.cost, 0) as cost FROM ( SELECT om_position.fund_id, om_position.owner_trader_id, om_position.strategy_id, om_position.cf_account_id, om_position.instrument_id, om_position.pos, om_position.cost FROM om_position WHERE om_position.as_of_date = date(now()) ) as pos_set full outer join ( SELECT om_trade.fund_id, om_trade.owner_trader_id, om_trade.strategy_id, om_trade.cf_account_id, om_trade.instrument_id, sum(om_trade.qty::numeric(22,9)) AS pos, sum(om_trade.cost) as cost FROM om_trade WHERE om_trade.process_state = 0 OR om_trade.process_state = 2 GROUP BY om_trade.fund_id, om_trade.owner_trader_id, om_trade.strategy_id, om_trade.cf_account_id, om_trade.instrument_id ) as trade_set ON pos_set.fund_id = trade_set.fund_id and pos_set.owner_trader_id = trade_set.owner_trader_id and pos_set.strategy_id = trade_set.strategy_id and pos_set.cf_account_id = trade_set.cf_account_id and pos_set.instrument_id = trade_set.instrument_id;
"Dave Dutcher" <dave@tridecap.com> writes: > Thanks for looking into it. I thought I might actually test if it was the > patch you mentioned which changed my results, but I haven't had time. > Because you mentioned it was grouping on the results of a UNION ALL which > was throwing off the row estimate I changed my query from a UNION ALL/GROUP > BY to a GROUP BY/FULL OUTER JOIN. The view runs a hair slower by itself, > but the better estimate of rows makes it work much better for joining with. I took another look and think I found the problem: 8.2's new code for flattening UNION ALL subqueries into "append relations" is failing to initialize all the fields of the appendrel, which confuses estimate_num_groups (and perhaps other places). I think this will fix it for you. regards, tom lane Index: allpaths.c =================================================================== RCS file: /cvsroot/pgsql/src/backend/optimizer/path/allpaths.c,v retrieving revision 1.154 diff -c -r1.154 allpaths.c *** allpaths.c 4 Oct 2006 00:29:53 -0000 1.154 --- allpaths.c 28 Jan 2007 18:44:01 -0000 *************** *** 384,389 **** --- 384,395 ---- } /* + * Set "raw tuples" count equal to "rows" for the appendrel; needed + * because some places assume rel->tuples is valid for any baserel. + */ + rel->tuples = rel->rows; + + /* * Finally, build Append path and install it as the only access path for * the parent rel. (Note: this is correct even if we have zero or one * live subpath due to constraint exclusion.)
> From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Tom Lane > > I took another look and think I found the problem: 8.2's new code for > flattening UNION ALL subqueries into "append relations" is failing to > initialize all the fields of the appendrel, which confuses > estimate_num_groups (and perhaps other places). I think this will fix > it for you. > I gave this a try on our test machine yesterday and it worked. The planner was estimating that the group by on the union would return about 300 rows which is very similar to what 8.1.2 thought. Actually it returned about 3000 rows, but still it is a good enough estimate to pick a plan which takes 100ms instead of a plan which takes 100 seconds. Thanks, Dave