Re: LATERAL quals revisited

On 07/04/2013 06:11 PM, Antonin Houska wrote:
> On 07/03/2013 08:32 PM, Tom Lane wrote:
>> Another possibility would be to keep the optimization, but disable it in
>> queries that use LATERAL. I don't much care for that though --- seems
>> too Rube Goldbergish, and in any case I have a lot less faith in the
>> whole concept now than I had before I started digging into this issue.
>>
>> Thoughts?
>>
> I noticed EXPLAIN in some regression tests. So if they all pass after
> removal of this optimization, it might indicate that it was really
> insignificant. But alternatively it may just be a lack of focus on
> this feature in the test queries. Digging for (non-LATERAL) queries or
> rather patterns where the ph_may_need optimization clearly appears to
> be important sounds to me like a good SQL exercise, but I'm afraid I
> won't have time for it in the next few days.
>

I constructed a query that triggers the optimization - see attachment
with comments. (Note that the relid sets are derived from my current
knowledge of the logic. I haven't figured out how to check them easily
in gdb session.)

The intention was that the top-level OJ references LHS of the join below
rather than the RHS. That should increase the likelihood that the PHV
becomes the only obstacle for join commuting. And therefore the
ph_may_need optimization should unblock some combinations that would be
impossible otherwise.

However I could not see the condition

if (bms_is_subset(phinfo->ph_may_need, min_righthand))
continue;

met for the top-level join even though the supposed ph_may_need did not
contain tab1. Then it struck me that min_righthand can be the problem.
So I changed the join clause to reference RHS of j1, hoping that it
should make min_righthand bigger. And that really triggered the condition.

EXPLAIN shows the same plan with or without the ph_may_need
optimization, but that might be data problem (my tables are empty).

More important is the fact that I could only avoid addition of the PHV's
eval_at to min_righthand at the cost of adding the whole j1 join (i.e.
more than just eval_at).

Although the idea behind ph_may_need is clever, I can now imagine that
other techniques of the planner can substitute for it. There might be
examples showing the opposite but such are beyond my imagination.

// Antonin Houska (Tony)