Back in April I posted some musings about improving the planner's handling of variables that could be forced to null by outer joins: http://archives.postgresql.org/pgsql-hackers/2008-04/msg01063.php
I haven't gotten anything further done on that, and time grows short for 8.4. I am still interested in the concept of fixing the parsetree representation so that equal() Vars must denote the same value. But it seems we're still shy an idea or two needed to make that happen in a clean fashion --- and if it's not clean, there's not much point, because the main argument for changing that is to make things cleaner. However, the main practical problem that was to be solved was to fix things so that we could flatten sub-selects that have non-nullable targetlist entries, even when they're below an outer join. I think that this could still get done for 8.4, along the following lines: * When we have a non-nullable expression in a sub-select's targetlist, and it's below an outer join, replace the expression by CASE WHEN flag_var THEN original_expression ELSE NULL END and then flatten as normal. * The "flag_var" will be a boolean variable that is always computed as "true" at the semantic level of the original sub-select. It then bubbles up through joins like any other variable. If an upper outer join emits a null-extended row, this variable will be nulled just like any other. Thus, when we get to a point where the sub-select output expression is needed, the flag_var will be TRUE if it's okay to compute the expression, and NULL if we should force it to null. Which is exactly what the above CASE does. * Potentially we need a flag_var for each subselect that gets flattened. I'm inclined to represent these as Vars with varno equal to the sub-select's relid and varattno equal to a "system attribute number" that isn't used anywhere else. Since a sub-select that's been flattened out of the query is not going to be one of the "base rels" to be joined, this will require a little bit of klugery in a couple of places in the planner that assume every Var corresponds to a base rel. It doesn't look too terribly awful though. * It might also be a good idea to use a special node type instead of a general CASE expression for the expression control node. This is not for efficiency but just so that the planner can understand the semantics of the expression a bit better (for instance, such a node could still be considered "strict" whereas CASE in general is not). Thoughts, objections? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers