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

On Tue, 16 Jun 2026 23:15:51 +0300
Ilia Evdokimov <ilya.evdokimov@tantorlabs.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@sraoss.co.jp>

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/

Attached v2-patch after applying v1 error.

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

The following review has been posted through the commitfest application:
make installcheck-world: tested, passed
Implements feature: tested, failed
Spec compliant: tested, passed
Documentation: tested, passed

Hi Ilia,

Denis Smirnov and I reviewed v2 and found these issues:

1. Multidimensional arrays

CREATE TEMP TABLE mda (a int[]);
INSERT INTO mda VALUES (NULL::int[]);
SELECT * FROM mda WHERE 1 <> ALL (ARRAY[NULL::int[], a]);

master: 1 row
v2: 0 rows

2. ON CONFLICT with a partial index

CREATE TEMP TABLE t (a int, b int);
CREATE UNIQUE INDEX ti ON t (a) WHERE b <> ALL (ARRAY[1, NULL]);
INSERT INTO t VALUES (1, 5)
ON CONFLICT (a) WHERE b <> ALL (ARRAY[1, NULL]) DO NOTHING;

master: succeeds
v2: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification

3. No folding under AND/OR

CREATE TEMP TABLE s (x int);

-- Plans with v2:
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL); -- One-Time Filter: false
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) AND x = 1; -- Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) OR false; -- Seq Scan

Perhaps this could be handled in canonicalize_qual().

Regards,
--
Rustam Allakov
Denis Smirnov

The new status of this patch is: Waiting on Author

On 9/13/26 21:12, Rustam ALLAKOV wrote:

1. Multidimensional arrays

CREATE TEMP TABLE mda (a int[]);
INSERT INTO mda VALUES (NULL::int[]);
SELECT * FROM mda WHERE 1 <> ALL (ARRAY[NULL::int[], a]);

master: 1 row
v2: 0 rows

Nice catch. I didn't consider multidimensional arrays. The
non-const-array branch in clauses.c walked ArrayExpr->elements and
folded to false whenever it saw a NULL Const there, without checking
multidims. For a multidimensional ArrayExpr each elements entry is a
sub-array, not a scalar - a NULL sub-array doesn't imply a NULL scalar
element of the result.

So we should skip the fold when ((ArrayExpr *) arrayarg)->multidims is true.

2. ON CONFLICT with a partial index

CREATE TEMP TABLE t (a int, b int);
CREATE UNIQUE INDEX ti ON t (a) WHERE b <> ALL (ARRAY[1, NULL]);
INSERT INTO t VALUES (1, 5)
ON CONFLICT (a) WHERE b <> ALL (ARRAY[1, NULL]) DO NOTHING;

master: succeeds
v2: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification

onConflict->arbiterWhere was preprocessed as an ordinary EXPRKIND_QUAL,
so the new folding introduced by this patch reduced it to constant
false. But arbiterWhere is never evaluated at runtime -
infer_arbiter_indexes() only uses it to check, at plan time, whether a
candidate index's predicate is implied by it via predicate_implied_by(),
which has no notion of a bare "false" clause vacuously implying
anything. So the folded arbiterWhere stopped matching the (correctly
unfolded) index predicate, even though the two are logically identical.

Fix: a new preprocess_expression() kind, EXPRKIND_ARBITER_WHERE, that
gets the same qual-shaped treatment as EXPRKIND_QUAL (AND/OR flattening,
canonicalize_qual, make_ands_implicit, etc.) but is routed through plain
eval_const_expressions() instead of eval_const_expressions_qual(), so
it's exempt from the new folding. An alternative would be teaching
predicate_implied_by() that a literal "false" clause vacuously implies
anything - didn't go that route since it's a general-purpose proof
routine used well beyond ON CONFLICT, but open to it if preferred.

I added this fix in v3-0002 patch. If anyone sees a better way to fix
this, happy to hear it.

3. No folding under AND/OR

CREATE TEMP TABLE s (x int);

-- Plans with v2:
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL); -- One-Time Filter: false
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) AND x = 1; -- Seq Scan
EXPLAIN (COSTS OFF) SELECT * FROM s
WHERE x NOT IN (42, NULL) OR false; -- Seq Scan

Perhaps this could be handled in canonicalize_qual().

eval_const_expressions_mutator() clears context->is_qual unconditionally
at entry, so it never reaches past the first node. When the qual is
itself an AND/OR, its arguments - processed via
simplify_and/or_arguments() - never see is_qual = true. Propagating it
into AND/OR arguments is safe, but must not propogate into NOT, CASE, or
non-qual contexts.

simplify_and/or_arguments() now take an is_qual parameter and set
context->is_qual before each of their own recursive calls.

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

Hi Ilia,

I checked v3. The direct NOT IN form is folded to false, but some
equivalent forms still scan the table:

create table t(a int);
insert into t values (1), (42), (null);

-- folded to false
explain (costs off)
select * from t where a not in (42, null);

-- these still scan the table
explain (costs off)
select * from t where not (a in (42, null));

explain (costs off)
select * from t where not (a = any (array[42, null]));

explain (costs off)
select * from t where not not (a not in (42, null));

explain (costs off)
select * from t where (a not in (42, null)) = true;

create function not_in_null(integer)
returns boolean
language sql immutable
as $$ select $1 not in (42, null) $$;

explain (costs off)
select * from t where not_in_null(a);

It looks like the expression produced after removing NOT or inlining
the function does not get another chance to use the new folding.

There are also CASE WHEN conditions and aggregate FILTER clauses,
where false and null have the same effect:

explain (costs off, verbose)
select case when a not in (42, null) then 1 else 0 end
from t;

explain (costs off, verbose)
select count(*) filter (where a not in (42, null))
from t;

explain (costs off, verbose)
select a, count(*) filter (where a not in (42, null)) over ()
from t;

These plans still contain the array comparison. The CASE expression
could become 0, and the filters could become false. This does not
necessarily mean that the table scan can be removed.

Could you also add regression tests, at least for the issues fixed
in v3: multidimensional arrays, ON CONFLICT with a partial index,
and folding under AND/OR? Currently, the patch only updates the
expected output of two existing queries. Tests checking both results
and plans would help prevent these issues from coming back.

Best regards,
Denis Smirnov

Hi Denis,

Thanks for review.

On 9/23/26 15:07, Denis Smirnov wrote:

I checked v3. The direct NOT IN form is folded to false, but some
equivalent forms still scan the table:

create table t(a int);
insert into t values (1), (42), (null);

-- folded to false
explain (costs off)
select * from t where a not in (42, null);

-- these still scan the table
explain (costs off)
select * from t where not (a in (42, null));

explain (costs off)
select * from t where not (a = any (array[42, null]));

explain (costs off)
select * from t where not not (a not in (42, null));

explain (costs off)
select * from t where (a not in (42, null)) = true;

create function not_in_null(integer)
returns boolean
language sql immutable
as $$ select $1 not in (42, null) $$;

explain (costs off)
select * from t where not_in_null(a);

It looks like the expression produced after removing NOT or inlining
the function does not get another chance to use the new folding.

Yes, that's exactly the cause. The fold was only tried while simplifying
the SAOP node itself, but negate_clause(), simplify_boolean_equality()
and SQL function inlining produce the <> ALL node only after that step.

in new v4 patch the fold is a separate pass, simplify_qual_null_saops(),
run over the already simplified expression. It looks through AND/OR
only: an AND with a FALSE or NULL arguments becomes FALSE, and such
arguments are dropped from an OR. It does not look like through NOT or
into other expressions. This replaces the is_qual flag in
eval_const_expressions_context, so the patch is simpler now. All five of
your examples now produce "One-Time Filter: false".

There are also CASE WHEN conditions and aggregate FILTER clauses,
where false and null have the same effect:

explain (costs off, verbose)
select case when a not in (42, null) then 1 else 0 end
from t;

explain (costs off, verbose)
select count(*) filter (where a not in (42, null))
from t;

explain (costs off, verbose)
select a, count(*) filter (where a not in (42, null)) over ()
from t;

These plans still contain the array comparison. The CASE expression
could become 0, and the filters could become false. This does not
necessarily mean that the table scan can be removed.

Agreed. The v4-patch the same pass to CASE WHEN conditions and to the
FILTER clauses of aggregates and window functions. Your examples now give:

    Output: 0
    Output: count(*) FILTER (WHERE false)
    Output: a, count(*) FILTER (WHERE false) OVER w1

As you noted, the scan itself is kept in these cases.

Could you also add regression tests, at least for the issues fixed
in v3: multidimensional arrays, ON CONFLICT with a partial index,
and folding under AND/OR?Currently, the patch only updates the
expected output of two existing queries. Tests checking both results
and plans would help prevent these issues from coming back.

Done.

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

Hi Ilia,

I checked v4. A few more cases could be simplified:

create table t(a int);
insert into t values (1), (42), (null);

explain (costs off)
select * from t where (a not in (42, null)) is true;

explain (costs off)
select * from t where (a not in (42, null)) is not true;

Both plans still contain the array comparison. The first condition
could be folded to false, and the second to true.

A null array is another case:

explain (costs off)
select * from t where a <> all (null::int[]);

explain (costs off)
select * from t where a = any (null::int[]);

Both plans still contain the array comparison. These comparisons
always return null, so in a where clause they could be folded
to false.

The comment in saop_never_true() says that ordinary constant folding
handles a null array, but this does not happen when the left argument
is a column.

Could you cover these cases and add regression tests?

Best regards,
Denis Smirnov

Hi folks,
sharing some output of my saturday session with an agent.
hope this will somehow help.

Tested v4 on master (3c5d9d9). The issues from v2 review are
fixed, but It found some new ones.

The CASE WHEN folding in eval_const_expressions_mutator also runs at
DDL time via expression_planner(). Because of that, a partition key
that master accepts is now rejected as a constant:

CREATE TABLE pk (a int, b int) PARTITION BY LIST
((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));

master: CREATE TABLE
v4: ERROR: cannot use constant expression as partition key

So a cluster that has such a table can't be moved to v4.

pg_dump from master and restore into v4 fails:

ERROR: cannot use constant expression as partition key
ERROR: relation "public.pk" does not exist

pg_upgrade from master to v4 fails in "Restoring database schemas in
the new cluster":

pg_restore: error: could not execute query: ERROR: cannot use
constant expression as partition key
...
CREATE TABLE "public"."pk" (
"a" integer,
"b" integer
)
PARTITION BY LIST ((
CASE
WHEN ("a" <> ALL (ARRAY[42, NULL::integer])) THEN 1
ELSE 0
END));

Regards,
--
Rustam Allakov

On 9/26/26 08:13, Denis Smirnov wrote:

create table t(a int);
insert into t values (1), (42), (null);

explain (costs off)
select * from t where (a not in (42, null)) is true;

explain (costs off)
select * from t where (a not in (42, null)) is not true;

Both plans still contain the array comparison. The first condition
could be folded to false, and the second to true.

Fixed. Both treat NULL the same as FALSE, so their argument now gets the
same simplification as a qual:
(a NOT IN (42, NULL)) IS TRUE folds to false, and IS NOT TRUE folds to
true. This also works when the SAOP sits under AND/OR inside the
argument. IS FALSE and the other tests are left alone, becuase their
result depends on the row.

A null array is another case:

explain (costs off)
select * from t where a <> all (null::int[]);

explain (costs off)
select * from t where a = any (null::int[]);

Both plans still contain the array comparison. These comparisons
always return null, so in a where clause they could be folded
to false.

The comment in saop_never_true() says that ordinary constant folding
handles a null array, but this does not happen when the left argument
is a column.

You're right. The comment is wrong. ExecEvalScalarArrayOp returns NULL
for a NULL array whatever the operator's strictness, and for ANY as well
as ALL. saop_never_true() now treats that case as never true, so both a
<> ALL (NULL::int[]) and a = ANY (NULL::int[]) fold to false in WHERE.

On 9/27/26 02:09, Rustam ALLAKOV wrote:

The CASE WHEN folding in eval_const_expressions_mutator also runs at
DDL time via expression_planner(). Because of that, a partition key
that master accepts is now rejected as a constant:

CREATE TABLE pk (a int, b int) PARTITION BY LIST
((CASE WHEN a NOT IN (42, NULL) THEN 1 ELSE 0 END));

master: CREATE TABLE
v4: ERROR: cannot use constant expression as partition key

So a cluster that has such a table can't be moved to v4.

pg_dump from master and restore into v4 fails:

ERROR: cannot use constant expression as partition key
ERROR: relation "public.pk" does not exist

pg_upgrade from master to v4 fails in "Restoring database schemas in
the new cluster":

pg_restore: error: could not execute query: ERROR: cannot use
constant expression as partition key
...
CREATE TABLE "public"."pk" (
"a" integer,
"b" integer
)
PARTITION BY LIST ((
CASE
WHEN ("a" <> ALL (ARRAY[42, NULL::integer])) THEN 1
ELSE 0
END));

Good catch. The CASE WHEN simplification runs inside
eval_const_expressions_mutator(), which is also reached through
expression_planner() when a partition key is checked for being constant.
Master already rejects e.g. CASE WHEN a = NULL THEN 1 ELSE 0 END for the
same reason, but v4 widened that set, which breaks dump/restore and
pg_upgrade. In v5 the extra simplifications of CASE WHEN conditions,
FILTER clauses and IS [NOT] TRUE run only when planning a query (root !=
NULL), the same condition already used for simplify_aggref().
Expressions reduced at DDL time therefore fold exactly as they do on master.

Tests: the new tests cases are in predicate.sql. Two existing unique1 =
ANY(NULL) in create_index.sql would now fold to a One-Time Filter and
stop exercising the btree code for a NULL array key, so it now passes
the NULL array as a parameter of a generic plan. . The partition_prune
test with a = any(null::timestamptz[]) simply folds now.

v5 attached.

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

Hi Ilia,

I checked v5 and found two regressions.

1. ON CONFLICT no longer matches an expression index.

create temp table t (a int, b int);
create unique index ti on t
((case when b not in (42, null) then 1 else a end));

insert into t values (1, 1)
on conflict ((case when b not in (42, null) then 1 else a end))
do nothing;

master: INSERT 0 1
v5: ERROR: there is no unique or exclusion constraint matching
the ON CONFLICT specification

The CASE becomes a plain column in ON CONFLICT, while the index
is still classified as an expression index.

2. Partition pruning stops working.

create temp table p (a int, b int) partition by list
((case when a not in (42, null) then 1 else b end));
create temp table p1 partition of p for values in (1);
create temp table p2 partition of p for values in (2);

explain (costs off)
select * from p
where (case when a not in (42, null) then 1 else b end) = 1;

master scans only p1. v5 scans both p1 and p2.

The query condition folds to b = 1, but the partition key keeps
the CASE because it is simplified with root == NULL. The
expressions no longer match.

Best regards,
Denis Smirnov

Hi Ilia, Denis, folks

I tested v5 on master (3c5d9d9). The partition key / dump / upgrade
problem from my last mail is fixed. In addition to Denis's two cases,
I found three more regressions. Each shows master vs v5.

1. Extended statistics are no longer used

create table es (a int, b int, c int);
insert into es select i % 100, i % 7, i % 100
from generate_series(1, 10000) i;
create statistics es_s (mcv)
on (case when b not in (42, null) then 1 else a end), c from es;
analyze es;
explain analyze select * from es
where (case when b not in (42, null) then 1 else a end) = 1
and c = 1;

master: Seq Scan on es (rows=100) (actual rows=100)
v5: Seq Scan on es (rows=1) (actual rows=100)
Filter: ((a = 1) AND (c = 1))

get_relation_statistics() simplifies the stats expression with root,
so it becomes the bare column "a". The clause "a = 1" is then matched
against the attnums in the stats object, not against its
expressions, so the MCV list is ignored. With "else a + 0" instead
of "else a" the estimate stays at 100.

2. Partitionwise aggregate is lost

create table pw (a int, b int) partition by list
((case when b not in (42, null) then 0 else a end));
create table pw0 partition of pw for values in (0);
create table pw1 partition of pw for values in (1);
insert into pw select i % 2, i from generate_series(1, 1000) i;
analyze pw;
set enable_partitionwise_aggregate = on;
explain (costs off)
select (case when b not in (42, null) then 0 else a end), count(*)
from pw group by 1;

master: Append -> HashAggregate per partition (full partitionwise)
v5: Finalize GroupAggregate -> Sort -> Append
-> Partial HashAggregate per partition

3. Constraint exclusion on a partition no longer works

set constraint_exclusion = on;
explain (costs off) select * from pw1
where (case when b not in (42, null) then 0 else a end) = 0;

master: Result, One-Time Filter: false (pw1 excluded)
v5: Seq Scan on pw1, Filter: (a = 0)

Issue 2 uses rel->partexprs, like pruning does, so it gets fixed only
if the fix goes where rel->partexprs is built
(set_baserel_partition_key_exprs()), not into partprune.c.
Issue 3 uses rel->partition_qual instead. That is built separately,
by the expression_planner() call in set_baserel_partition_constraint(),
without root, so it needs a fix of its own.

Regards,
--
Rustam Allakov

Hi Denis, Rustam

Thank you both for the careful testing. I reproduced all five cases on
v5, and the attached v6 fixes them.

All five have the same cause. v5 applied the NULL/FALSE folding not only
to quals but also, when root != NULL, to CASE WHEN conditions and to the
argument of IS [NOT] TRUE. That doesn't change the value of the
enclosing expression, but it does change its structure. The planner then
compares that structure against expressions from the catalogs that were
simplified without the folding:

- ON CONFLICT: the CASE collapses to the Var "a", which is matched
against the index's plain columns, and the index has none.
- pruning and partitionwise aggregate: rel->partexprs is built with root
== NULL.
- extended statistics: the stats expression is simplified with root and
becomes the bare column "a", so it gets matched by attnum.
- constraint exclusion: partition_qual comes from expression_planner()
without root.

Rustam, you're right that a fix in partprune.c alone wouldn't be enough.
I also don't think fixing it where rel->partexprs and partition_qual are
built is the right direction, because the problem isn't limited to
partitioning. Index expressions, statistics expressions and ON CONFLICT
inference all depend on the same thing: a scalar expression has to look
the same however it was simplified. We would have to apply the folding
consistently to every catalog expression. We can't do that, because a
partition key is checked for being constant when it is defined, and that
is exactly the dump/upgrade breakage from the previous round.

So v6 restricts the folding to positions where the result is the truth
value of a qual:

- WHERE/JOIN/HAVING quals and the other places that use
eval_const_expressions_qual(), as before, looking through AND and OR only.
- "expr IS TRUE" and "expr IS NOT TRUE" in those positions. They are
replaced by constant FALSE and TRUE only when expr reduces entirely to
FALSE. Their argument is never partially rewritten.
- aggregate and window function FILTER clauses, as before. An Aggref or
WindowFunc cannot appear in an index expression, a partition key or a
statistics expression, so these can't be matched against catalog
expressions.

CASE WHEN conditions and IS [NOT] TRUE outside a qual are no longer
folded. Folding CASE WHEN was requested earlier in the thread. I don't
see a way to do it without these regressions, so I've dropped it.

v6 adds regression tests for all five of your cases (predicate.sql,
insert_conflict.sql, stats_ext.sql). Each of them fails on v5, and with
v6 the plans and estimates match master

Hi Ilia, folks.

I tested v6 on master (3c5d9d9), and make check passes. All five cases
from Denis's and my last mails now give the same plans and estimates as
master, and the partition key / dump case still works. I also ran
about 3200 random queries against both master and v6 (WHERE, JOIN ON,
HAVING, CASE, FILTER, nested IS [NOT] TRUE/FALSE, NOT/AND/OR, NULL
arrays) and got the same results.

I found one new regression. It is caused by the new IS NOT TRUE
handling: the qual now differs from a partial index predicate written
the same way.

create table pi (a int, b int, c int);
insert into pi select i, i % 100, i % 1000
from generate_series(1, 100000) i;
create index pi_c on pi (c) where (b not in (42, null)) is not true;
analyze pi;
explain (costs off)
select * from pi where (b not in (42, null)) is not true and c = 5;

master: Bitmap Heap Scan on pi
Recheck Cond: ((c = 5) AND ((b <> ALL (...)) IS NOT TRUE))
-> Bitmap Index Scan on pi_c
Index Cond: (c = 5)
v6: Seq Scan on pi
Filter: (c = 5)

This is not a costing choice. With enable_seqscan = off, v6 still
picks the seq scan ("Disabled: true"), so the index can't be used at
all. With EXPLAIN (ANALYZE, BUFFERS) on a warm cache, the query reads
102 buffers on master and 541 on v6, and the gap grows with the
table. The results are the same. It also happens when the
IS NOT TRUE is an arm of an OR, because the whole OR becomes TRUE and
is dropped:

create index pi_c2 on pi (a)
where b = 1 or (b not in (42, null)) is not true;
explain (costs off) select * from pi
where (b = 1 or (b not in (42, null)) is not true) and a = 5;

master: Index Scan using pi_c2 on pi
v6: Seq Scan on pi

simplify_qual_null_saops() turns the IS NOT TRUE into TRUE and removes
it from the qual. get_relation_info() simplifies the index predicate
with plain eval_const_expressions(), so the predicate keeps the IS NOT
TRUE, and predicate_implied_by() can't prove it from the remaining
quals. Folding a NOT IN arm out of an OR doesn't have this problem.
"b = 1 or b not in (42, null)" as both predicate and qual still uses
the index on v6, because the remaining arm implies the predicate.

Regards,
--
Rustam Allakov

Hi Rustam,

On Mon, Oct 19, 2026 at 1:15 PM Rustam ALLAKOV <rustamallakov@gmail.com> wrote:

I found one new regression. It is caused by the new IS NOT TRUE
handling: the qual now differs from a partial index predicate written
the same way.

You're right. Folding IS NOT TRUE to TRUE removes the qual needed to
prove the partial index predicate. The OR case has the same cause: the
whole OR becomes TRUE and disappears, while the index predicate
retains its original structure.

The attached v7 leaves IS NOT TRUE expressions unchanged. IS TRUE can
still fold to FALSE when its argument can never be TRUE, but otherwise
BooleanTest arguments are left unchanged.

With this change, your first example uses a Bitmap Index Scan on the
partial index, and the OR example uses an Index Scan. Direct NOT IN
expressions and their IS TRUE forms still fold to a One-Time Filter:
false.

I've added regression tests for both partial index cases, checking
index availability with enable_seqscan disabled and checking the query
results.

There are also a few additional changes in v7:
- Tests for a non-strict operator that returns TRUE for NULL
arguments. A NULL array element must not trigger folding for such an
operator, whereas a NULL array still produces NULL for both ANY and
ALL
- A test ensuring that strict ANY with a matching element and a NULL
element retains the matching row
- A stack-depth check in simplify_qual_null_saops()
- AND/OR argument lists are now copied only when a rewrite is needed,
avoiding unnecessary list allocation for unchanged expressions

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

Hi Ilia,

Tested on master at 0497318e442, with and without v7:

create temp table t (a int, b int);
insert into t
select i, i from generate_series(1, 100000) i;

create index ti on t (((a not in (42, null)) or b = 1));
analyze t;

set enable_seqscan = off;

explain (costs off)
select * from t where (a not in (42, null)) or b = 1;

master: Index Scan using ti on t
v7: Seq Scan on t
Disabled: true
Filter: (b = 1)

Best regards,
Denis Smirnov