From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Have the planner replace COUNT(ANY) with COUNT(*), when possible
Date: Wed, 26 Nov 2025 21:44:05 +0000
Message-ID: <E1vONIy-001Ye2-34@gemulon.postgresql.org> (raw)
Have the planner replace COUNT(ANY) with COUNT(*), when possible
This adds SupportRequestSimplifyAggref to allow pg_proc.prosupport
functions to receive an Aggref and allow them to determine if there is a
way that the Aggref call can be optimized.
Also added is a support function to allow transformation of COUNT(ANY)
into COUNT(*). This is possible to do when the given "ANY" cannot be
NULL and also that there are no ORDER BY / DISTINCT clauses within the
Aggref. This is a useful transformation to do as it is common that
people write COUNT(1), which until now has added unneeded overhead.
When counting a NOT NULL column. The overheads can be worse as that
might mean deforming more of the tuple, which for large fact tables may
be many columns in.
It may be possible to add prosupport functions for other aggregates. We
could consider if ORDER BY could be dropped for some calls, e.g. the
ORDER BY is quite useless in MAX(c ORDER BY c).
There is a little bit of passing fallout from adjusting
expr_is_nonnullable() to handle Const which results in a plan change in
the aggregates.out regression test. Previously, nothing was able to
determine that "One-Time Filter: (100 IS NOT NULL)" was always true,
therefore useless to include in the plan.
Author: David Rowley <dgrowleyml@gmail.com>
Reviewed-by: Corey Huinker <corey.huinker@gmail.com>
Reviewed-by: Matheus Alcantara <matheusssilv97@gmail.com>
Discussion: https://postgr.es/m/CAApHDvqGcPTagXpKfH=CrmHBqALpziThJEDs_MrPqjKVeDF9wA@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/42473b3b31238b15cc3c030b4416b2ee79508d8c
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 20 ++--
src/backend/optimizer/plan/initsplan.c | 20 +---
src/backend/optimizer/util/clauses.c | 73 +++++++++++++++
src/backend/utils/adt/int8.c | 49 +++++++++-
src/include/nodes/supportnodes.h | 25 +++++
src/include/optimizer/optimizer.h | 3 +
src/test/regress/expected/aggregates.out | 125 +++++++++++++++++++++++--
src/test/regress/sql/aggregates.sql | 44 ++++++++-
src/tools/pgindent/typedefs.list | 1 +
9 files changed, 324 insertions(+), 36 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: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Have the planner replace COUNT(ANY) with COUNT(*), when possible
In-Reply-To: <E1vONIy-001Ye2-34@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