Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Teach expr_is_nonnullable() to handle more expression types

  2. Optimize ROW(...) IS [NOT] NULL using non-nullable fields

  3. Simplify COALESCE expressions using non-nullable arguments

  1. Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-11-25T10:50:51Z

    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

  2. Re: Some optimizations for COALESCE expressions during constant folding

    Tender Wang <tndrwang@gmail.com> — 2025-11-25T12:07:17Z

    Richard Guo <guofenglinux@gmail.com> 于2025年11月25日周二 18:51写道：

> 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.
>

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.


> 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.)
>

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.


-- 
Thanks,
Tender Wang

  3. Re: Some optimizations for COALESCE expressions during constant folding

    Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2025-11-25T12:59:07Z

    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

  4. Re: Some optimizations for COALESCE expressions during constant folding

    David Rowley <dgrowleyml@gmail.com> — 2025-11-25T13:10:45Z

    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

  5. Re: Some optimizations for COALESCE expressions during constant folding

    David Rowley <dgrowleyml@gmail.com> — 2025-11-25T13:16:12Z

    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

  6. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-11-26T06:33:31Z

    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

  7. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-11-26T11:55:22Z

    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

  8. Re: Some optimizations for COALESCE expressions during constant folding

    David Rowley <dgrowleyml@gmail.com> — 2025-11-26T22:55:52Z

    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

  9. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-12-01T08:11:25Z

    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

  10. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-12-03T06:39:04Z

    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

  11. Re: Some optimizations for COALESCE expressions during constant folding

    Matheus Alcantara <matheusssilv97@gmail.com> — 2025-12-11T14:54:04Z

    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

  12. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-12-12T02:17:07Z

    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

  13. Re: Some optimizations for COALESCE expressions during constant folding

    Matheus Alcantara <matheusssilv97@gmail.com> — 2025-12-15T22:09:06Z

    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

  14. Re: Some optimizations for COALESCE expressions during constant folding

    Richard Guo <guofenglinux@gmail.com> — 2025-12-24T10:05:46Z

    On Tue, Dec 16, 2025 at 7:09 AM Matheus Alcantara
<matheusssilv97@gmail.com> wrote:
> 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.

Thanks for all the reviews.  I've pushed this patchset.

- Richard