Re: Improving planner variable handling

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> * 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.

> I don't understand how this gets you any further ahead.

After a lot of thought I'm beginning to come round to your point of
view :-(. This just isn't working out.

What I'm now considering is a more frontal attack on the problem:
invent a type of Var that can be evaluated lower in the plan tree
than where its value gets used. The idea would be to replace a
non-nullable expression coming out of a flattened sub-select by
one of these special Vars, which would be marked so that it gets
evaluated (by executing the original expression) just below the
lowest outer join that can null the original sub-select. The Var
then bubbles up the plan tree normally (and possibly gets set to
null by various outer joins). Above the evaluation level we have
just ordinary references to the Var, not to the original expression.

There might also be references to the sub-select result in join ON
clauses below that lowest outer join. I'm inclined to make these
just expand to the original expression, rather than trying to combine
uses (at least for the first pass; maybe we could get smarter later).
Note that the expression won't be volatile --- else we'd refuse to
flatten the sub-select in the first place --- so this is just an
efficiency not a correctness issue.

This mechanism might have other uses later, since it gives us some
flexibility about where in the tree an expression gets evaluated.
We might be able to re-introduce something like Hellerstein's
expensive-function optimization. But for 8.4 I'd be happy if we can
fix the problem with not being able to flatten subselects with
non-nullable outputs.

Thoughts, objections?

BTW, can anyone think of a good name for these animals? I've only
come up with things like "PseudoVar" or "ExpressionVar", which seem
rather content-free ...

regards, tom lane