From: Richard Guo <rguo@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Simplify COALESCE expressions using non-nullable arguments
Date: Wed, 24 Dec 2025 09:02:02 +0000
Message-ID: <E1vYKkr-002PHB-14@gemulon.postgresql.org> (raw)
Simplify COALESCE expressions using non-nullable arguments
The COALESCE function returns the first of its arguments that is not
null. When an argument is proven non-null, if it is the first
non-null-constant argument, the entire COALESCE expression can be
replaced by that argument. If it is a subsequent argument, all
following arguments can be dropped, since they will never be reached.
Currently, we perform this simplification only for Const arguments.
This patch extends the simplification to support any expression that
can be proven non-nullable.
This can help avoid the overhead of evaluating unreachable arguments.
It can also lead to better plans when the first argument is proven
non-nullable and replaces the expression, as the planner no longer has
to treat the expression as non-strict, and can also leverage index
scans on the resulting expression.
There is an ensuing plan change in generated_virtual.out, and we have
to modify the test to ensure that it continues to test what it is
intended to.
Author: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Tender Wang <tndrwang@gmail.com>
Reviewed-by: Dagfinn Ilmari Mannsåker <ilmari@ilmari.org>
Reviewed-by: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com>
Discussion: https://postgr.es/m/CAMbWs49UhPBjm+NRpxerjaeuFKyUZJ_AjM3NBcSYK2JgZ6VTEQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/10c4fe074a92eccf9fa6f7a39fe4d3f97b9b87e2
Modified Files
--------------
src/backend/optimizer/util/clauses.c | 18 +++++++---
src/test/regress/expected/generated_virtual.out | 48 +++++++++++++------------
src/test/regress/expected/predicate.out | 33 +++++++++++++++++
src/test/regress/sql/generated_virtual.sql | 11 +++---
src/test/regress/sql/predicate.sql | 20 +++++++++++
5 files changed, 97 insertions(+), 33 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: rguo@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Simplify COALESCE expressions using non-nullable arguments
In-Reply-To: <E1vYKkr-002PHB-14@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox