From: "Dave Dutcher" <dave@tridecap.com>
To: <pgsql-performance@postgresql.org>
Subject: Bad Row Count Estimate on View with 8.2
Date: Tue, 23 Jan 2007 16:08:47 -0600
Message-ID: <008e01c73f3b$0e509190$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Acc/Ow2rYaH8ECDkRm6efut9BIZmBA==
Importance: Normal

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? =20

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=20
(=20
	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 =3D date(now())
	UNION ALL=20
	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 =3D 0 OR om_trade.process_state =3D 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=3D10 and tables freshly analyzed:


8.1.2
HashAggregate  (cost=3D4760.33..4764.95 rows=3D308 width=3D168) (actual
time=3D56.873..71.293 rows=3D3569 loops=3D1)
  ->  Append  (cost=3D0.00..4675.85 rows=3D3072 width=3D54) (actual
time=3D0.037..38.261 rows=3D3715 loops=3D1)
        ->  Index Scan using as_of_date_om_position_index on om_position
(cost=3D0.00..4637.10 rows=3D3071 width=3D54) (actual =
time=3D0.031..14.722 rows=3D3559
loops=3D1)
              Index Cond: (as_of_date =3D date(now()))
        ->  Bitmap Heap Scan on om_trade  (cost=3D4.01..8.03 rows=3D1 =
width=3D48)
(actual time=3D0.118..0.917 rows=3D156 loops=3D1)
              Recheck Cond: ((process_state =3D 0) OR (process_state =3D =
2))
              ->  BitmapOr  (cost=3D4.01..4.01 rows=3D1 width=3D0) =
(actual
time=3D0.079..0.079 rows=3D0 loops=3D1)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0)
(actual time=3D0.060..0.060 rows=3D156 loops=3D1)
                          Index Cond: (process_state =3D 0)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..2.00 rows=3D1 =
width=3D0)
(actual time=3D0.008..0.008 rows=3D0 loops=3D1)
                          Index Cond: (process_state =3D 2)
Total runtime: 82.398 ms

8.2.1
HashAggregate  (cost=3D6912.51..6912.53 rows=3D1 width=3D200) (actual
time=3D19.005..24.137 rows=3D3569 loops=3D1)
  ->  Append  (cost=3D0.00..6406.73 rows=3D28902 width=3D200) (actual
time=3D0.037..11.569 rows=3D3715 loops=3D1)
        ->  Index Scan using as_of_date_om_position_index on om_position
(cost=3D0.00..4333.82 rows=3D2964 width=3D53) (actual =
time=3D0.035..4.884 rows=3D3559
loops=3D1)
              Index Cond: (as_of_date =3D date(now()))
        ->  Bitmap Heap Scan on om_trade  (cost=3D464.40..1783.89 =
rows=3D25938
width=3D49) (actual time=3D0.060..0.380 rows=3D156 loops=3D1)
              Recheck Cond: ((process_state =3D 0) OR (process_state =3D =
2))
              ->  BitmapOr  (cost=3D464.40..464.40 rows=3D308 width=3D0) =
(actual
time=3D0.041..0.041 rows=3D0 loops=3D1)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..225.72 rows=3D154 =
width=3D0)
(actual time=3D0.032..0.032 rows=3D156 loops=3D1)
                          Index Cond: (process_state =3D 0)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..225.72 rows=3D154 =
width=3D0)
(actual time=3D0.003..0.003 rows=3D0 loops=3D1)
                          Index Cond: (process_state =3D 2)
Total runtime: 27.193 ms


Here is explain analyze from 8.2.1 with default_statistics_target=3D1000 =
and
tables freshly analyzed:


HashAggregate  (cost=3D5344.36..5344.37 rows=3D1 width=3D200) (actual
time=3D18.826..23.950 rows=3D3569 loops=3D1)
  ->  Append  (cost=3D0.00..5280.01 rows=3D3677 width=3D200) (actual
time=3D0.031..11.606 rows=3D3715 loops=3D1)
        ->  Index Scan using as_of_date_om_position_index on om_position
(cost=3D0.00..5224.44 rows=3D3502 width=3D54) (actual =
time=3D0.029..4.903 rows=3D3559
loops=3D1)
              Index Cond: (as_of_date =3D date(now()))
        ->  Bitmap Heap Scan on om_trade  (cost=3D9.91..18.79 rows=3D175
width=3D49) (actual time=3D0.069..0.394 rows=3D156 loops=3D1)
              Recheck Cond: ((process_state =3D 0) OR (process_state =3D =
2))
              ->  BitmapOr  (cost=3D9.91..9.91 rows=3D2 width=3D0) =
(actual
time=3D0.050..0.050 rows=3D0 loops=3D1)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..5.57 rows=3D2 =
width=3D0)
(actual time=3D0.039..0.039 rows=3D156 loops=3D1)
                          Index Cond: (process_state =3D 0)
                    ->  Bitmap Index Scan on
om_trade_partial_process_state_index  (cost=3D0.00..4.26 rows=3D1 =
width=3D0)
(actual time=3D0.004..0.004 rows=3D0 loops=3D1)
                          Index Cond: (process_state =3D 2)
Total runtime: 27.055 ms


Thanks,


Dave Dutcher
Telluride Asset Management
952.653.6411
=20