Fold NOT IN / <> ALL expressions containing NULL to FALSE

From: Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Fold NOT IN / <> ALL expressions containing NULL to FALSE
Date: 2026-06-16 20:15:51
Message-ID: f0d6944a-d9fb-4d55-a73a-d0e86a85b766@tantorlabs.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi everyone,

In commit c95cd299, we added an early-exit in `scalararraysel()` to
return selectivity 0.0 when a NOT IN / <> ALL list contains a NULL and
the operator is strict. The commit message noted a possible follow-up:

    In the future, it might be better to do something for this case in
    constant folding.  We would need to be careful to only do this for
    strict operators on expressions located in places that don't care about
    distinguishing false from NULL returns. i.e. EXPRKIND_QUAL expressions.
    Doing that requires a bit more thought and effort, so here we just fix
    some needlessly slow selectivity estimations for ScalarArrayOpExpr
    containing many array elements and at least one NULL.

This patch implements that follow-up.

When a <> ALL / NOT IN expression appears in a qual context and its
array contains a NULL element, the expression can never evaluate to
true; it can only return false or NULL. In a qual, both mean the row is
excluded. We can therefore fold the entire SAOP to constant false during
`eval_const_expressions()`, which the planner can then use to eliminate
the scan entirely.

A new `is_qual` flag is added to `eval_const_expressions_context`. A new
function `eval_const_expressions_qual()` sets this flag and is called
from sites that process WHERE/qual expressions. To prevent the flag from
leaking into non-qual contexts (e.g. `func(x NOT IN (NULL, 1))`),
is_qual is saved into a local variable and immediately reset to false at
the start of `eval_const_expressions_mutator`. Only the SAOP case reads
`this_node_is_qual` - after processing its arguments with `is_qual = false`.

Any suggestions?

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

Attachment Content-Type Size
v1-0001-Fold-NOT-IN-ALL-with-NULL-array-element-to-false-.patch text/x-patch 10.3 KB

Browse pgsql-hackers by date

  From Date Subject
Next Message Corey Huinker 2026-06-16 20:19:52 Re: postgres_fdw: Emit message when batch_size is reduced
Previous Message Daniel Gustafsson 2026-06-16 20:11:53 Re: [oauth] Increased CPU usage during device flow with libcurl 8.20.0