MIME-Version: 1.0
References: 
 <CAMbWs495eF=-fSa5CwJS6B-BaEi3ARp0UNb4Lt3EkgUGZJwkAQ@mail.gmail.com>
 <CAMbWs49tBNbW7S7VCbTasJEfPQKKU7fbJT2TMoi9uHMhT6rwqw@mail.gmail.com>
 <2df5912f-8888-4f27-9384-4c69ba22105c@gmail.com>
 <CAMbWs49nvNcBaUXTw5_euodb7ONADwDULJ4Cxw5qurDXdurc+Q@mail.gmail.com>
 <0dd7bed2-2a1d-4a12-bddf-e00013744eab@gmail.com>
In-Reply-To: <0dd7bed2-2a1d-4a12-bddf-e00013744eab@gmail.com>
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 4 Mar 2026 18:33:24 +0900
Message-ID: 
 <CAMbWs4-EJrNijaS9=KqA0m3hWRQ6ZcCkd75etAGW_SZoPpfEXg@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/CAMbWs4-EJrNijaS9%3DKqA0m3hWRQ6ZcCkd75etAGW_SZoPpfEXg%40mail.gmail.com>
Precedence: bulk

On Mon, Mar 2, 2026 at 9:50=E2=80=AFPM David Geier <geidav.pg@gmail.com> wr=
ote:
> The very last rewrite combines both cases. The rewritten query then
> looks like:
>
> SELECT t1.c1 FROM T1 WHERE
>   t1.c1 IS NOT NULL AND
>   NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 =3D t2.c1) AND
>   NOT EXISTS (SELECT 1 FROM t2 WHERE t2.c1 IS NULL)

I'm still not convinced this rewrite is correct.  As I mentioned
earlier, it breaks down if t2 is empty while t1 contains NULL rows.
For example:

CREATE TABLE t1 (c1 int);
CREATE TABLE t2 (c1 int);
INSERT INTO t1 VALUES (1), (NULL);

SELECT t1.c1 FROM t1 WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2);
 c1
----
  1

(2 rows)

SELECT t1.c1 FROM T1 WHERE
  t1.c1 IS NOT NULL AND
  NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c1 =3D t2.c1) AND
  NOT EXISTS (SELECT 1 FROM t2 WHERE t2.c1 IS NULL);
 c1
----
  1
(1 row)

> Seems reasonable to start with the non-NULL variant, though there are
> certainly cases where there's no PK / unique index on the relevant column=
s.

Yeah.  I don't know how to optimize nullable NOT IN clauses.  It seems
quite difficult to handle safely purely via query transformations.
Maybe we can explore adding a dedicated Null-Aware Anti-Join execution
node, much like Oracle's approach.  But that is definitely beyond the
scope of this current patch.

- Richard