Memoize ANTI and SEMI JOIN inner

Andrei Lepikhov <lepihov@gmail.com>

From: Andrei Lepikhov <lepihov@gmail.com>

To: PostgreSQL-development <pgsql-hackers@postgresql.org>

Date: 2025-03-06T13:08:59Z

Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Enable use of Memoize for ANTI joins
- 0da29e4cb161 19 (unreleased) landed

Attachments

v0-0001-Memoise-the-inner-of-SEMI-and-ANTI-join.patch (text/plain) patch v0-0001

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.

-- 
regards, Andrei Lepikhov