Subject: Re: Reduce planning time for large NOT IN lists containing NULL

From: Ilia Evdokimov <[email-masked]>
Date: Mon, 23 Feb 2026 20:47:11 +0300

On 2/20/26 17:22, David Geier wrote:

> It seems like we don't have a regression test which has a NULL value in
> the NOT IN list. Maybe you can find a good place to add that one?

There are already regression tests covering NOT IN with NULL values in 
expressions.sql. Do you think we need an additional simple case, or is 
the current coverage sufficient?

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

---

From: Zsolt Parragi <[email-masked]>
Date: Mon, 23 Feb 2026 19:44:07 +0000

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.