Tom Lane wrote:
Chris <dmagick@xxxxxxxxx> writes:
I can see it's doing the extra filter step at the start (4th line) which
is not present without the coalesce/case statement. I just don't
understand why it's being done at that stage.
It's not that hard to understand. With the original view formulation
(or the COALESCE version), the fully expanded form of the query looks
like
select ... from p left join r ...
where expression_involving_both_p_and_r = constant
If you make the view output be just p.assetid then you have
select ... from p left join r ...
where p.assetid = constant
In the first case the planner cannot apply the WHERE restriction until
it's formed the p+r join; so you see the condition applied as a filter
on the join node's output. In the second case, the planner can push the
WHERE restriction down into the scan of p, since the left join doesn't
affect it. (If a p row doesn't pass the restriction, then no join row
formed from it can either; ergo there is no need to form those join rows
at all.)
So because the CASE is on (some of) the fields I'm joining on, in effect
it's made part of the join condition. If the fields are outside that
(r.userid/p.userid), then it's evaluated after.
Thanks!
--
Postgresql & php tutorials
http://www.designmagick.com/
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
