MIME-Version: 1.0
References: <eaa2598c-5356-4e1e-9ec3-5fd6eb1cd704@tantorlabs.com>
 <52596e08-0902-475b-ad0f-265ec6918f19@gmail.com>
 <7791ec74-369d-48c7-969c-cf4ad18024f2@tantorlabs.com>
 <ed61316c-2e48-45d1-9cca-dc539bfd93b9@tantorlabs.com>
 <dd5e91c1-5e59-471c-bd9d-96a11c7928f4@tantorlabs.com>
 <6b3aebb5-f26c-4f50-abd6-e733d452af26@gmail.com>
 <f43547c8-1a21-4337-98f9-f6a1d2b8fd2b@tantorlabs.com>
 <CAN4CZFNJHro_pEVTHggBX43f+AebwKLZdp39+V4pV0EB4BZuZw@mail.gmail.com>
 <52f3c637-465d-4f0e-9546-732ecf61ab87@tantorlabs.com>
 <3fc74079-f429-48c8-ad8c-688b21d4d1c1@gmail.com>
 <4c761b02-5a60-4076-aa0c-9c6fef06e2c1@tantorlabs.com>
 <CAN4CZFOTAyVk9srBp560049Xu655xUDZv5NBTvj7RYND-xKymA@mail.gmail.com>
 <2bdf4a7e-f5b9-4e75-b5a3-1e2608fd3297@tantorlabs.com>
 <CAApHDvphShGABn-3AoE36dTvGHW7gUpFSw0_ZZnH84wGCW3hHw@mail.gmail.com>
 <390a46f3-dbc4-4dc1-b49d-5cc61dd36026@tantorlabs.com>
 <977365f2-2ab7-4008-8d9c-f62ef5271cea@tantorlabs.com>
 <CAApHDvq+Tk=BUwzJZsPsm49_QxAXRe9Vd2iFroMsB_Mnt8LdHg@mail.gmail.com>
 <83464ae9-a79a-43c8-a425-4b0773f9e759@tantorlabs.com>
In-Reply-To: <83464ae9-a79a-43c8-a425-4b0773f9e759@tantorlabs.com>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 19 Mar 2026 17:28:11 +1300
Message-ID: 
 <CAApHDvocmqXDP2fpxAORmOOLRgxEng2OM6+-MOtqN=aDFivzEA@mail.gmail.com>
Subject: Re: Reduce planning time for large NOT IN lists containing NULL
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>,
 David Geier <geidav.pg@gmail.com>,
	PostgreSQL Developers <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/CAApHDvocmqXDP2fpxAORmOOLRgxEng2OM6%2B-MOtqN%3DaDFivzEA%40mail.gmail.com>
Precedence: bulk

On Thu, 19 Mar 2026 at 03:26, Ilia Evdokimov
<ilya.evdokimov@tantorlabs.com> wrote:
> After the new test was committed, I realized that v8 tests relies on sele=
ctivity calculation, which are not guaranteed to remain stable over time an=
d way vary depending on planner heuristics or platform differences. Therefo=
re, it seems better to remove tests from v8.
>
> Instead, we can test the invariant behavior: when NULL is present in a <>=
 ALL clause, the selectivity is always 0.0.
>
> The v9-patch adds three test cases: a degenerate case with only NULL, NUL=
L combined with constants, NULL combined with both constants and non-consta=
nt expression.
>
> Thoughts?

I've now pushed the main patch.

I did end up removing the first test of the v9 tests since it was
exercising the same code path as the 2nd test. I also didn't see the
need to execute the query, so I changed it to run EXPLAIN without
ANALYZE.

For the main patch, I only adjusted the comments a little. I wanted to
make reference to var_eq_const() as the short-circuit really is trying
to follow what that function would have done if the short-circuit path
hadn't been taken.

I also added comments in the tests to explain what we are expecting to
see. I am probably in a minority of people who do this. I find it's
important to understand that when it comes to updating the expected
results of existing tests. I expect we have plenty of tests that no
longer test what the original test was meant to test as a result of
people not doing this.

David