Chris Mayfield <cmayfiel@cs.purdue.edu> writes:
>>> So the long and the short of it is that the COALESCE acts as an
>>> optimization fence in the presence of outer joins. We've seen this
>>> before and there are some rough ideas about fixing it.
> You may already have this rough idea somewhere, but it seems to me that
> the view could be flattened into the upper query as long as the join
> predicates don't depend on coalesced columns. In the examples I sent,
> even if the COALESCE is evaluated at the very end of the query, the
> merge join (on the id columns) would still be correct.
But the output would not be: the join column would fail to go to null
when it was supposed to. See the example that made us put in that
restriction in the first place:
http://archives.postgresql.org/pgsql-bugs/2001-04/msg00223.php
regards, tom lane