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
