On 7/31/26 18:36, Yugo Nagata wrote:
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?
Yes, that's right. The code only checks `!saop->useOr` (ALL semantics)
and `func_strict`. It never inspects which specific operator is
involved. `x op ALL (x1, ..., xn)` is equivalent to `x op x1 AND ... x
op xn`. If `xi = NULL` and `op` is strict, expression can only be FALSE
or NULL. Since a qual treats FALSE and NULL identically, finding one
NULL array element is enough to fold SAOP to FALSE.
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.
I don't think this is a new risk. The docs already say that if an
expression's result can be determined from only part of it, the rest
need not be evaluated at all, and that relying on side effects in
WHERE/HAVING is unsafe for that reason (4.2.14. Expression Evaluation
Rules). The sibling subquery form `expr op ALL (subquery)` even states
this explicitly - "it's unwise to assume that the subquery will be
evaluated completely" - so the array form was simply the one case in
this family where that guarantee wasn't yet being enforced.
--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com/
