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>
In-Reply-To: <f43547c8-1a21-4337-98f9-f6a1d2b8fd2b@tantorlabs.com>
From: Zsolt Parragi <zsolt.parragi@percona.com>
Date: Mon, 23 Feb 2026 19:44:07 +0000
Message-ID: <CAN4CZFNJHro_pEVTHggBX43f+AebwKLZdp39+V4pV0EB4BZuZw@mail.gmail.com>
Subject: Re: Reduce planning time for large NOT IN lists containing NULL
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: David Geier <geidav.pg@gmail.com>, 
	PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAN4CZFNJHro_pEVTHggBX43f%2BAebwKLZdp39%2BV4pV0EB4BZuZw%40mail.gmail.com>
Precedence: bulk

Hello

I think it would be a good idea to add a test, I think there's a
regression with this patch:

CREATE TABLE notin_test AS SELECT generate_series(1, 1000) AS x;
ANALYZE notin_test;

CREATE FUNCTION replace_elem(arr int[], idx int, val int)
RETURNS int[] AS $$
BEGIN
    arr[idx] := val;
    RETURN arr;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

EXPLAIN SELECT * FROM notin_test WHERE x <> ALL(ARRAY[1,99,3]);
-- same array, constructed from an array with a NULL
EXPLAIN SELECT * FROM notin_test WHERE x <>
ALL(replace_elem(ARRAY[1,NULL,3], 2, 99));
DROP TABLE notin_test;
DROP FUNCTION replace_elem;

ARR_HASNULL probably should be array_contains_nulls, as ARR_HASNULL
simply checks for the existence of a NULL bitmap.