Обсуждение: Some optimizations for COALESCE expressions during constant folding

Currently, we perform some simplification for Const arguments of a
COALESCE expression.  For instance, if the first argument is a
non-null constant, we use that constant as the result for the entire
expression.  If a subsequent argument is a non-null constant, all
following arguments are dropped since they will never be reached.

We can extend this simplification to Var arguments since the NOT NULL
attribute information is now available during constant folding.  0001
implements this.

Another optimization that can be done for a COALESCE expression is
when it is used in a NullTest.  We can determine that a COALESCE
expression is non-nullable by checking if at least one of its
arguments is proven non-null.  This information can then be used to
reduce the NullTest qual to a constant true or false.  0002 implements
this.  (I'm wondering whether it'd be better to consolidate the
non-null check for Const, Var, and CoalesceExpr into one helper
function to simplify the code in eval_const_expressions.)

- Richard

Richard Guo <guofenglinux@gmail.com> writes:

> +                    ListCell   *lc;
> +
> +                    foreach(lc, coalesceexpr->args)
> +                    {
> +                        Node       *coalescearg = (Node *) lfirst(lc);

I have no comment on the rest of the patch, but this could be simplifed
using the foreach_ptr macro:

    foreach_ptr(Node, coalescearg, coalesceexpr->args)
    {

- ilmari

On Tue, 25 Nov 2025 at 23:51, Richard Guo <guofenglinux@gmail.com> wrote:
> (I'm wondering whether it'd be better to consolidate the
> non-null check for Const, Var, and CoalesceExpr into one helper
> function to simplify the code in eval_const_expressions.)

uhh, of course it is. That's what I did in [1] for Consts and expand
expr_is_nonnullable() to support COALESCE exprs then modify
eval_const_expressions_mutator() to use that rather than using
var_is_nonnullable(). That way we'll not need to modify the constant
folding code every time we think of something new that we can prove
can't be NULL.

David

[1]
https://www.postgresql.org/message-id/attachment/184166/v3-0001-Have-the-planner-replace-COUNT-ANY-with-COUNT-whe.patch

On Wed, 26 Nov 2025 at 02:10, David Rowley <dgrowleyml@gmail.com> wrote:
>
> On Tue, 25 Nov 2025 at 23:51, Richard Guo <guofenglinux@gmail.com> wrote:
> > (I'm wondering whether it'd be better to consolidate the
> > non-null check for Const, Var, and CoalesceExpr into one helper
> > function to simplify the code in eval_const_expressions.)
>
> uhh, of course it is. That's what I did in [1] for Consts and expand
> expr_is_nonnullable() to support COALESCE exprs then modify
> eval_const_expressions_mutator() to use that rather than using
> var_is_nonnullable(). That way we'll not need to modify the constant
> folding code every time we think of something new that we can prove
> can't be NULL.

That one failed the copy/edit pass. Here's another try at getting my
point across:

uhh, of course it is. That's what I did in [1] for Consts. Doing it
this way means we'll not need to modify the constant folding code (or
whichever other code wants to know when an Expr can't be NULL) every
time we think of something new that we can prove can't be NULL.

David

[1]
https://www.postgresql.org/message-id/attachment/184166/v3-0001-Have-the-planner-replace-COUNT-ANY-with-COUNT-whe.patch

On Tue, Nov 25, 2025 at 9:07 PM Tender Wang <tndrwang@gmail.com> wrote:
> I took a quick look at the 0001. It seems correct to me.
> One thing I want to confirm is that if var_is_nonnullable() returns true, we can make sure that
> the Var is 100% nonnullable, no matter what kind of join reorder happens.

This is a good question.  The answer is NO: A Var that is non-nullable
in the original query tree might become nullable due to join
reordering.  For instance, consider when we transform

  A leftjoin (B leftjoin C on (Pbc)) on (Pab)

to

  (A leftjoin B on (Pab)) leftjoin C on (Pbc)

In the first form, the B Vars in Pbc are non-nullable, assuming they
are defined NOT NULL.  But in the second form they become nullable by
the A/B join.

However, this doesn't introduce correctness hazards when simplifying
expressions based on NOT NULL constraints.  For instance, if we
simplify COALESCE(b.id, 1) to just b.id based on var_is_nonnullable()
returning TRUE in the original tree, the query results remain correct
even after the transformation: if A fails to match B, both query trees
return (A, NULL, NULL).

BTW, if we do not simplify COALESCE(b.id, 1) to b.id, the above
transformation would not happen because Pbc fails the strictness
requirement.  This is what I meant in the commit message that the
change in 0001 can lead to better plans.

> I have no objections to the 0002 code logic.
> But I wonder how often users write "COALECE()  is not null" in their query.
> Before this patch, I didn't find the case in the regression test cases.

While it might be true that humans rarely write COALESCE(...) IS NULL
by hand, this pattern is likely not uncommon after view expansion,
function inlining, and ORM query generation.  Besides, this
optimization doesn't seem to cost too much, so I think the benefit
justifies the cost.

- Richard

On Tue, Nov 25, 2025 at 10:16 PM David Rowley <dgrowleyml@gmail.com> wrote:
> uhh, of course it is. That's what I did in [1] for Consts. Doing it
> this way means we'll not need to modify the constant folding code (or
> whichever other code wants to know when an Expr can't be NULL) every
> time we think of something new that we can prove can't be NULL.

OK.  Here is an updated patch that does that.  (There is some overlap
in changes to expr_is_nonnullable with the patch you mentioned.)

- Richard

On Thu, 27 Nov 2025 at 00:55, Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Tue, Nov 25, 2025 at 10:16 PM David Rowley <dgrowleyml@gmail.com> wrote:
> > uhh, of course it is. That's what I did in [1] for Consts. Doing it
> > this way means we'll not need to modify the constant folding code (or
> > whichever other code wants to know when an Expr can't be NULL) every
> > time we think of something new that we can prove can't be NULL.
>
> OK.  Here is an updated patch that does that.  (There is some overlap
> in changes to expr_is_nonnullable with the patch you mentioned.)

I've pushed 42473b3b3 now. I think you should maybe do this as 2
commits. 0001 to make eval_const_expressions_mutator() use
expr_is_nonnullable() instead of var_is_nonnullable(). That'll not
really do anything aside from the additional Const support for
NULLability checks. Otherwise, it's nearly a refactor.  0002 is to add
the COALESCE code to expr_is_nonnullable(). That way you can sell this
one for a bit more than your initial use case, as it'll also then
handle converting things like COUNT(COALESCE(nullable, notnullable))
into COUNT(*). I think doing it this way means you don't need to argue
that optimising COALESCE(...) IS NOT NULL is worthwhile since you're
really just teaching expr_is_nonnullable() about COALESCE Nodes.

David

Attached is the patch set rebased on current master.  I have split the
patch into two parts: 0001 teaches eval_const_expressions to simplify
COALESCE arguments using NOT NULL constraints, and 0002 teaches
expr_is_nonnullable to handle COALESCE expressions.

In addition, 0003 is a WIP patch that extends expr_is_nonnullable to
handle more expression types.  I suspect there are additional cases
beyond those covered in this patch that can be proven non-nullable.

- Richard

On Mon, Dec 1, 2025 at 5:11 PM Richard Guo <guofenglinux@gmail.com> wrote:
> Attached is the patch set rebased on current master.  I have split the
> patch into two parts: 0001 teaches eval_const_expressions to simplify
> COALESCE arguments using NOT NULL constraints, and 0002 teaches
> expr_is_nonnullable to handle COALESCE expressions.
>
> In addition, 0003 is a WIP patch that extends expr_is_nonnullable to
> handle more expression types.  I suspect there are additional cases
> beyond those covered in this patch that can be proven non-nullable.

Here is an updated patchset.  I have reorganized the code changes as:
0001 simplifies COALESCE expressions based on non-nullable arguments.
0002 simplifies NullTest expressions for RowExprs based on
non-nullable component fields.  It also replaces the existing use of
var_is_nonnullable() with expr_is_nonnullable() for NullTests.  0003
teaches expr_is_nonnullable() to handle more expression types.

- Richard

On Wed Dec 3, 2025 at 3:39 AM -03, Richard Guo wrote:
> On Mon, Dec 1, 2025 at 5:11 PM Richard Guo <guofenglinux@gmail.com> wrote:
>> Attached is the patch set rebased on current master.  I have split the
>> patch into two parts: 0001 teaches eval_const_expressions to simplify
>> COALESCE arguments using NOT NULL constraints, and 0002 teaches
>> expr_is_nonnullable to handle COALESCE expressions.
>>
>> In addition, 0003 is a WIP patch that extends expr_is_nonnullable to
>> handle more expression types.  I suspect there are additional cases
>> beyond those covered in this patch that can be proven non-nullable.
>
> Here is an updated patchset.  I have reorganized the code changes as:
> 0001 simplifies COALESCE expressions based on non-nullable arguments.
> 0002 simplifies NullTest expressions for RowExprs based on
> non-nullable component fields.  It also replaces the existing use of
> var_is_nonnullable() with expr_is_nonnullable() for NullTests.  0003
> teaches expr_is_nonnullable() to handle more expression types.
>
Hi,

I think that this patch needs a rebase due to the changes on
predicate.sql introduced by c925ad30b04.

--
Matheus Alcantara
EDB: http://www.enterprisedb.com

On Thu, Dec 11, 2025 at 11:54 PM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
> I think that this patch needs a rebase due to the changes on
> predicate.sql introduced by c925ad30b04.

Right.  Here it is.

- Richard

On Thu Dec 11, 2025 at 11:17 PM -03, Richard Guo wrote:
> On Thu, Dec 11, 2025 at 11:54 PM Matheus Alcantara
> <matheusssilv97@gmail.com> wrote:
>> I think that this patch needs a rebase due to the changes on
>> predicate.sql introduced by c925ad30b04.
>
> Right.  Here it is.
>
Thanks for the new version.

The patches seems all in a good shape. I've checked the code coverage
for the new tests added and all of then seems to exercice the new code.

I've also performed some manual tests and it's looks good. I don't see
any issue or regression.

--
Matheus Alcantara
EDB: http://www.enterprisedb.com