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.) 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
