Skip site navigation (1) Skip section navigation (2)

Re: Bad Row Count Estimate on View with 8.2

From: "Dave Dutcher" <dave(at)tridecap(dot)com>
To: "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Bad Row Count Estimate on View with 8.2
Date: 2007-01-28 17:02:12
Message-ID: 000401c742fe$0e24fd20$8300a8c0@tridecap.com (view raw or flat)
Thread:
Lists: pgsql-performance
> From: pgsql-performance-owner(at)postgresql(dot)org 
> [mailto:pgsql-performance-owner(at)postgresql(dot)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;




In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-01-28 18:47:58
Subject: Re: Bad Row Count Estimate on View with 8.2
Previous:From: Ryan HolmesDate: 2007-01-28 02:31:45
Subject: Re: IN operator causes sequential scan (vs. multiple OR expressions)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group