On 6/3/2025 14:08, Andrei Lepikhov wrote:
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: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.
ERROR: cache entry already completeHow 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
repro.sql
Description: application/sql
