Re: Removing unneeded self joins

On 4/4/25 04:53, Richard Guo wrote:
> On Fri, Apr 4, 2025 at 1:02 AM Alexander Korotkov <aekorotkov@gmail.com> wrote:
>> I've got an off-list bug report from Alexander Lakhin involving a
>> placeholder variable.  Alena and Andrei proposed a fix.  It is fairly
>> simple: we just shouldn't remove PHVs during self-join elimination, as
>> they might still be referenced from other parts of a query.  The patch
>> is attached.  I'm going to fix this if no objections.
> 
> Hmm, I'm not sure about the fix.  It seems to me that it simply
> prevents removing any PHVs in the self-join removal case.  My concern
> is that this might result in PHVs that could actually be removed not
> being removed in many cases.
Let's play with use cases:
If a PHV is needed in the inner or outer only, it means we have a clause 
in the baserestrictinfo that will be transferred to the keeping 
relation, and we shouldn't remove the PHV.
Another case is when the PHV is needed in a join clause of the 
self-join. I may imagine such a case:

toKeep.x+toRemove.y=PHV

This clause will be transformed to "toKeep.x+toKeep.y=PHV", pushed to 
baserestrictinfo of keeping relation and should be saved.
I think it is possible to invent quite a narrow case of clause like the 
following:

PHV_evaluated_at_inner = PHV_evaluated_at_outer

It needs to prove reproducibility. But even if it makes sense, it seems 
to have no danger for further selectivity estimation compared to the 
source clause and is a too-narrow case, isn't it?
In other cases, this PHV is needed something else, and we can't remove it.

Maybe I lost the case you keep in mind? I would like to discover it.

> 
> Besides, there's the specific comment above this code explaining the
> logic behind the removal of PHVs.  Shouldn't that comment be updated
> to reflect the changes?
It makes sense: for now, it seems that PHV removal should be used in the 
case of an outer join removal. In the case of SJE, logically we make a 
replacement, not a removal, and we should not reduce the number of 
entities involved.

-- 
regards, Andrei Lepikhov