MIME-Version: 1.0
References: <CAMbWs495eF=-fSa5CwJS6B-BaEi3ARp0UNb4Lt3EkgUGZJwkAQ@mail.gmail.com>
 <CAMbWs49tBNbW7S7VCbTasJEfPQKKU7fbJT2TMoi9uHMhT6rwqw@mail.gmail.com> <2df5912f-8888-4f27-9384-4c69ba22105c@gmail.com>
In-Reply-To: <2df5912f-8888-4f27-9384-4c69ba22105c@gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Thu, 5 Feb 2026 15:09:17 +0900
Message-ID: <CAMbWs49nvNcBaUXTw5_euodb7ONADwDULJ4Cxw5qurDXdurc+Q@mail.gmail.com>
Subject: Re: Convert NOT IN sublinks to anti-joins when safe
To: David Geier <geidav.pg@gmail.com>
Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAMbWs49nvNcBaUXTw5_euodb7ONADwDULJ4Cxw5qurDXdurc%2BQ%40mail.gmail.com>
Precedence: bulk

On Wed, Feb 4, 2026 at 11:59=E2=80=AFPM David Geier <geidav.pg@gmail.com> w=
rote:
> If the sub-select can yield NULLs, the rewrite can be fixed by adding an
> OR t2.c1 IS NULL clause, such as:
>
> SELECT t1.c1 FROM t1 WHERE
>   NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 =3D t2.c1 OR t2.c1 IS NULL)

I'm not sure if this rewrite results in a better plan.  The OR clause
would force a nested loop join, which could be much slower than a
hashed-subplan plan.

> If the outer expression can yield NULLs, the rewrite can be fixed by
> adding a t1.c1 IS NOT NULL clause, such as:
>
> SELECT t1.c1 FROM T1 WHERE
>   t1.c1 IS NOT NULL AND
>   NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 =3D t2.c1)

This rewrite doesn't seem correct to me.  If t2 is empty, you would
incorrectly lose the NULL rows from t1 in the final result.

> What's our today's take on doing more involved transformations inside
> the planner to support such cases? It would greatly open up the scope of
> the optimization.

As mentioned in my initial email, the goal of this patch is not to
handle every possible case, but rather only to handle the basic form
where both sides of NOT IN are provably non-nullable.  This keeps the
code complexity to a minimum, and I believe this would cover the most
common use cases in real world.

- Richard