From: "Dave Dutcher" <dave@tridecap.com>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Bad Row Count Estimate on View with 8.2 
Date: Sun, 28 Jan 2007 11:02:12 -0600
Message-ID: <000401c742fe$0e24fd20$8300a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit
Importance: Normal
Thread-Index: Acc/i7OgqOLu2YFhR5y8hZTIG3G1vgDcNrpg
In-reply-to: <23451.1169624674@sss.pgh.pa.us>

> 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;