Re: Memoize ANTI and SEMI JOIN inner

On 6/3/2025 14:08, Andrei Lepikhov wrote:
> Hi,
> 
> In case of NestLoop with parameterised inner semi-join for each outer 
> tuple requires only a single tuple from its inner relation to produce a 
> result. It seems that the same principle applies to an anti-join. This 
> approach could effectively allow the Memoize node to enhance the 
> performance of pulled-up EXISTS and NOT EXISTS sublinks.
> 
> In attachment see a sketch of the feature. Since we are using single_row 
> mode, adapting this method to cache semi-join inner results should not 
> be extremely complex. However, I am unsure about potential corner cases 
> and would appreciate any feedback or criticisms regarding this approach.
I found a corner case that breaks this approach: when NestLoop has a 
join clause or a filter, it may filter inner tuples, thus scanning more 
than only one time for each outer. You can see the reproduction script 
in the attachment. Each of the reproduction queries throws the error:
ERROR: cache entry already complete

How can we be sure that semi or anti-join needs only one tuple? I think 
it would be enough to control the absence of join clauses and filters in 
the join. Unfortunately, we only have such a guarantee in the plan 
creation stage (maybe even setrefs.c). So, it seems we need to invent an 
approach like AlternativeSubplan.

-- 
regards, Andrei Lepikhov