On Tue, 16 Jun 2026 23:15:51 +0300
Ilia Evdokimov <[email protected]> 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 <[email protected]>
