Re: left join + case - how is it processed?

Tom Lane wrote:
> Chris <dmagick@gmail.com> 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/