Bad Row Count Estimate on View with 8.2 - Mailing list pgsql-performance

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