Horton

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

Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>

View thread View on postgr.es
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
To: David Geier <geidav.pg@gmail.com>, PostgreSQL Developers <pgsql-hackers@lists.postgresql.org>
Date: 2026-02-20T13:25:24Z
Lists: pgsql-hackers 
On 2/20/26 12:06, Ilia Evdokimov wrote:

> There is already ARR_HASNULL() which allows us to detect the presence 
> of NULL in ArrayType.
>
I've moved the NULL check higher, placing it immediately after 
DatumGetArrayTypeP(). This allows us to detect the presence of NULL 
elements before decontructing the array.


I tested this with several queries of the form:

WHERE x NOT IN (NULL, ...)

In my tests (with column x having detailed statistics, so selectivity 
estimation performs more work), planning time decreases from *5-200 ms 
before the patch* to *~ 1-2 ms after the patch*, depending on the list size.

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

Commits

  1. Short-circuit row estimation in NOT IN containing NULL consts

  2. Move planner row-estimation tests to new planner_est.sql