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

Chris <dmagick(at)gmail(dot)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.)

In general a WHERE or JOIN/ON clause cannot be applied below the point
at which all the relations mentioned in it have been joined. There are
a few special cases where the planner can transform clauses into some
other form that's more optimizable, but you can pretty much bet that a
CASE will never be one of them --- CASE is more or less *defined* to
defeat optimization.

regards, tom lane