> From: pgsql-performance-owner@xxxxxxxxxxxxxx > [mailto:pgsql-performance-owner@xxxxxxxxxxxxxx] 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;
