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

On Tue, 16 Jun 2026 23:15:51 +0300
Ilia Evdokimov <ilya(dot)evdokimov(at)tantorlabs(dot)com> wrote:

> 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.

If I understand the patch correctly, the optimization seems to be applied
not only to <> ALL/NOT IN, but to any op ALL expression whose operator is
strict. Is that right?

> 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?

This could improve performance when the array contains many elements including
NULL, especially if one of the elements takes a long time to evaluate.
For example, after applying the patch, the following query returns immediately
without evaluating pg_sleep(3):

postgres=# explain analyze select * from tbl where i not in (null, (select 1 from pg_sleep(3)));
QUERY PLAN
---------------------------------------------------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.004..0.004 rows=0.00 loops=1)
Replaces: Scan on tbl
One-Time Filter: false
Planning Time: 0.088 ms
Execution Time: 0.033 ms
(5 rows)

This seems like a nice optimization. However, I wonder whether skipping the
evaluation of subqueries or function calls in the array could cause compatibility
issues, especially if they have side effects.

Regards,
Yugo Nagata

--
Yugo Nagata <nagata(at)sraoss(dot)co(dot)jp>