Message-ID: <52f3c637-465d-4f0e-9546-732ecf61ab87@tantorlabs.com>
Date: Tue, 24 Feb 2026 11:15:54 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reduce planning time for large NOT IN lists containing NULL
To: Zsolt Parragi <zsolt.parragi@percona.com>
Cc: David Geier <geidav.pg@gmail.com>,
 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>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <CAN4CZFNJHro_pEVTHggBX43f+AebwKLZdp39+V4pV0EB4BZuZw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/52f3c637-465d-4f0e-9546-732ecf61ab87%40tantorlabs.com>
Precedence: bulk

Hi,

On 2/23/26 22:44, Zsolt Parragi wrote:
> 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.

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

The current patch only introduces an early exit from the expensive 
per-element selectivity loop in the <> ALL case when a NULL is detected. 
If the goal is to verify the correctness of IN / NOT IN semantics, those 
cases already covered in expressions.sql, including scenarios with NULL 
elements.

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

-- 
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/