Message-ID: <3fc74079-f429-48c8-ad8c-688b21d4d1c1@gmail.com>
Date: Tue, 24 Feb 2026 09:29:27 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reduce planning time for large NOT IN lists containing NULL
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 Zsolt Parragi <zsolt.parragi@percona.com>
Cc: PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
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>
Content-Language: en-US
From: David Geier <geidav.pg@gmail.com>
In-Reply-To: <52f3c637-465d-4f0e-9546-732ecf61ab87@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/3fc74079-f429-48c8-ad8c-688b21d4d1c1%40gmail.com>
Precedence: bulk

>> 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;

Good catch. The macro name is misleading here. It should have been
called ARR_HASNULLBITMAP().

+1 on adding an explicit test that says why we care about that case.

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


Using array_contains_nulls() seems fine. In case the IN list doesn't
contain NULL, the function can immediately bail thanks to the
!ARR_HASNULL() check in the beginning.

It only needs to iterate over the NULL-bitmap, if it exists. This is the
case if there's actually a NULL element in the array, or if the array
initially contained NULL and all NULLs got removed subsequently.

If we ever find the latter case to matter we could remove the
NULL-bitmap in array_set_element() / array_set_element_expanded(), when
the last NULL element got removed.

> Could you clarify what exactly this additional test meant to verify?

Zsolt's test case creates an array that initially contains NULL. The
NULL element is subsequently replaced by a non-NULL value but
array_set_element_expanded() keeps the NULL-bitmap around. With that,
your ARR_ISNULL() check bails and causes the selectivity estimation to
incorrectly return 0.

> I attached this thread to commitfest: https://commitfest.postgresql.org/
> patch/6519/

I'll assign myself as reviewer.

--
David Geier