public inbox for [email protected]  

help / color / mirror / Atom feed

From: Richard Guo <[email protected]>
To: David Geier <[email protected]>
Cc: Pg Hackers <[email protected]>
Subject: Re: Convert NOT IN sublinks to anti-joins when safe
Date: Wed, 4 Mar 2026 18:33:24 +0900
Message-ID: <CAMbWs4-EJrNijaS9=KqA0m3hWRQ6ZcCkd75etAGW_SZoPpfEXg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAMbWs495eF=-fSa5CwJS6B-BaEi3ARp0UNb4Lt3EkgUGZJwkAQ@mail.gmail.com>
	<CAMbWs49tBNbW7S7VCbTasJEfPQKKU7fbJT2TMoi9uHMhT6rwqw@mail.gmail.com>
	<[email protected]>
	<CAMbWs49nvNcBaUXTw5_euodb7ONADwDULJ4Cxw5qurDXdurc+Q@mail.gmail.com>
	<[email protected]>

On Mon, Mar 2, 2026 at 9:50 PM David Geier <[email protected]> wrote:
> 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 = 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 = 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 columns.

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

view thread (12+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Convert NOT IN sublinks to anti-joins when safe
  In-Reply-To: <CAMbWs4-EJrNijaS9=KqA0m3hWRQ6ZcCkd75etAGW_SZoPpfEXg@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox