pgsql: Reduce "Var IS [NOT] NULL" quals during constant folding
Reduce "Var IS [NOT] NULL" quals during constant folding
In commit b262ad440, we introduced an optimization that reduces an IS
[NOT] NULL qual on a NOT NULL column to constant true or constant
false, provided we can prove that the input expression of the NullTest
is not nullable by any outer joins or grouping sets. This deduction
happens quite late in the planner, during the distribution of quals to
rels in query_planner. However, this approach has some drawbacks: we
can't perform any further folding with the constant, and it turns out
to be prone to bugs.
Ideally, this deduction should happen during constant folding.
However, the per-relation information about which columns are defined
as NOT NULL is not available at that point. This information is
currently collected from catalogs when building RelOptInfos for base
or "other" relations.
This patch moves the collection of NOT NULL attribute information for
relations before pull_up_sublinks, storing it in a hash table keyed by
relation OID. It then uses this information to perform the NullTest
deduction for Vars during constant folding. This also makes it
possible to leverage this information to pull up NOT IN subqueries.
Note that this patch does not get rid of restriction_is_always_true
and restriction_is_always_false. Removing them would prevent us from
reducing some IS [NOT] NULL quals that we were previously able to
reduce, because (a) the self-join elimination may introduce new IS NOT
NULL quals after constant folding, and (b) if some outer joins are
converted to inner joins, previously irreducible NullTest quals may
become reducible.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Robert Haas <robertmhaas@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Discussion: /messages/by-id/CAMbWs4-bFJ1At4btk5wqbezdu8PLtQ3zv-aiaY3ry9Ymm=jgFQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/e2debb64380ebcf0979708a0fa88d9c8d924005b
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 8 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 2 +-
src/backend/optimizer/plan/initsplan.c | 24 +----
src/backend/optimizer/plan/planner.c | 12 ++-
src/backend/optimizer/plan/subselect.c | 20 ++--
src/backend/optimizer/prep/prepjointree.c | 19 +++-
src/backend/optimizer/util/clauses.c | 92 ++++++++++++++++-
src/backend/optimizer/util/inherit.c | 10 +-
src/backend/optimizer/util/plancat.c | 127 ++++++++++++++++++++----
src/include/nodes/pathnodes.h | 12 ++-
src/include/optimizer/optimizer.h | 2 +
src/include/optimizer/plancat.h | 4 +
src/test/regress/expected/generated_virtual.out | 6 +-
src/test/regress/expected/join.out | 6 +-
src/test/regress/expected/predicate.out | 54 +++++++++-
src/test/regress/sql/predicate.sql | 18 ++++
src/tools/pgindent/typedefs.list | 1 +
17 files changed, 336 insertions(+), 81 deletions(-)
On Mon, Jul 21, 2025 at 10:23 PM Richard Guo <rguo@postgresql.org> wrote:
Reduce "Var IS [NOT] NULL" quals during constant folding
I feel like this commit message might have missed a fairly important
point here, which is that this commit made it necessary, in some set
of circumstances, for eval_const_expressions() to happen with a valid
PlannerInfo *root and with varnos already fixed. In addition to the
cases covered by 317c117d6d2 and c925ad30b04, there seems to be issues
with partition keys and partition constraints: see
set_baserel_partition_key_exprs and set_baserel_partition_constraint.
Attached are a pair of Claude-written test case that demonstrate
partition pruning failing as a result.
I'm afraid this isn't going to be all the bugs. We have expression
trees in a lot of places, and we call eval_const_expressions()
directly or indirectly in a lot of places, and it doesn't seem easy to
know what we might still have missed.
--
Robert Haas
Databricks
On Tue, Oct 6, 2026 at 8:53 AM Robert Haas <robertmhaas@gmail.com> wrote:
I feel like this commit message might have missed a fairly important
point here, which is that this commit made it necessary, in some set
of circumstances, for eval_const_expressions() to happen with a valid
PlannerInfo *root and with varnos already fixed. In addition to the
cases covered by 317c117d6d2 and c925ad30b04, there seems to be issues
with partition keys and partition constraints: see
set_baserel_partition_key_exprs and set_baserel_partition_constraint.
Attached are a pair of Claude-written test case that demonstrate
partition pruning failing as a result.
I'm afraid this isn't going to be all the bugs. We have expression
trees in a lot of places, and we call eval_const_expressions()
directly or indirectly in a lot of places, and it doesn't seem easy to
know what we might still have missed.
Here is a fix for both, done the same way as c925ad30b04. A plain int
key is enough to see it:
create table pt (a int not null, b int) partition by list (coalesce(a, b));
create table pt1 partition of pt for values in (1);
create table pt2 partition of pt for values in (2);
explain (costs off) select * from pt where coalesce(a, b) = 1;
18.3 scans only pt1. REL_19_STABLE 4b1abd678223 scans pt1 and pt2,
with Filter: (a = 1): the qual is reduced, the key is not, so they no
longer match. The CASE WHEN a IS NULL THEN b ELSE a END shape breaks
since e2debb64380 [1]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e2debb64380ebcf0979708a0fa88d9c8d924005b, the COALESCE shape since 10c4fe074a9 [2]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10c4fe074a92eccf9fa6f7a39fe4d3f97b9b87e2.
Run-time pruning with a generic plan and partitionwise join on such
keys are lost the same way, and come back with the patch.
The patch runs each partition key expression through
eval_const_expressions() again with the root after ChangeVarNodes(),
like c925ad30b04 does for index expressions [3]https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c925ad30b047ec1388247539e86729d584c34b8d. For the partition
constraint it fixes the varnos first and then const-simplifies with
the root instead of expression_planner(), so the constraint is still
simplified only once. Your finding8.sql gives One-Time Filter: false
again, and finding6.sql scans only p1.
AI prepared the attached patch and tests; I haven't manually reviewed
the code yet. The new tests in partition_prune.sql cover the COALESCE
and CASE keys, run-time pruning with a prepared statement, and the
partition constraint case; each fails without the plancat.c change.
Tested on REL_19_STABLE 4b1abd678223 and master b6178c942ea7 (the
patch applies to both), assertions on: the regress and isolation
suites pass.
[1]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e2debb64380ebcf0979708a0fa88d9c8d924005b
[2]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=10c4fe074a92eccf9fa6f7a39fe4d3f97b9b87e2
[3]: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c925ad30b047ec1388247539e86729d584c34b8d
Nik