Kim Bisgaard <kib+pg@dmi.dk> writes:
> The reason the first query is not performing is because the query
> optimizer does not push the conditions down into the sub-queries - right??
Well, it's not the same condition: the WHERE clause is constraining
the output variable of the FULL JOIN, which is logically and
implementationally distinct from either input table's variable.
I suppose it is arguable that we could transform the WHERE clause into
constraints on the input variables --- but we'd have to think carefully
about the conditions under which such a transformation is valid.
Offhand it seems like it might work for strict non-volatile constraint
expressions that refer only to output variables of the JOIN; if they
refer to other tables too then I'm not sure.
This is by no means a "bug fix", but if I have time over the next week
I'll see whether it is feasible to write for 8.1.
regards, tom lane