Content-Type: multipart/alternative;
 boundary="------------gqsCI3Hyil0YEqrZ7CL04qJp"
Message-ID: <ed61316c-2e48-45d1-9cca-dc539bfd93b9@tantorlabs.com>
Date: Fri, 20 Feb 2026 16:25:24 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Reduce planning time for large NOT IN lists containing NULL
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
To: 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>
Content-Language: en-US
In-Reply-To: <7791ec74-369d-48c7-969c-cf4ad18024f2@tantorlabs.com>
Archived-At: <https://www.postgresql.org/message-id/ed61316c-2e48-45d1-9cca-dc539bfd93b9%40tantorlabs.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------gqsCI3Hyil0YEqrZ7CL04qJp
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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/

--------------gqsCI3Hyil0YEqrZ7CL04qJp
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>On 2/20/26 12:06, Ilia Evdokimov wrote:</p>
    <blockquote type="cite"
      cite="mid:7791ec74-369d-48c7-969c-cf4ad18024f2@tantorlabs.com">
      <p>There is already ARR_HASNULL() which allows us to detect the
        presence of NULL in ArrayType.</p>
    </blockquote>
    <p>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.</p>
    <p><br>
    </p>
    <p>I tested this with several queries of the form:</p>
    <p>WHERE x NOT IN (NULL, ...)</p>
    <p>In my tests (with column x having detailed statistics, so
      selectivity estimation performs more work), planning time
      decreases from <b>5-200 ms before the patch</b> to <b>~ 1-2 ms
        after the patch</b>, depending on the list size.</p>
    <p>-- <br>
      Best regards. <br>
      Ilia Evdokimov, <br>
      Tantor Labs LLC, <br>
      <a class="moz-txt-link-freetext" href="https://tantorlabs.com/">https://tantorlabs.com/</a></p>
  </body>
</html>

--------------gqsCI3Hyil0YEqrZ7CL04qJp--