[PATCH] Remove redundant ORDER BY from COUNT aggregates
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t253385psql -h localhost -U postgresBuilt from patchset v1 (message #1), August 23, 2026 at 04:41 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t253385_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t253385_1 && git checkout t253385_1Patchset v1 (message #1) is on t253385_1
Hi, hackers,
I’d like to propose a small optimization for aggregate-local ORDER BY in
COUNT.
Currently, for example:
SELECT count(a ORDER BY b) FROM t;
is planned as an ordered aggregate, even though the ordering cannot affect
the
result of COUNT. This may require a Sort, but the impact is broader than
just
the extra sort: having aggorder also prevents partial aggregation and hash
aggregation, and prevents the aggregate from sharing state with an
otherwise
identical count(a).
This patch extends the existing SupportRequestSimplifyAggref handling for
COUNT
to canonicalize such cases before aggregate preprocessing:
count(a ORDER BY b) -> count(a)
and, when a is known to be non-null:
count(a ORDER BY b) -> count(*)
I deliberately limited this first patch to COUNT.
It is tempting to apply the same idea to SUM or MIN/MAX, but mathematical
order-independence is not sufficient to prove that removing ORDER BY
preserves
PostgreSQL-visible behavior.
For floating-point SUM, input order can affect both the result and whether
an
error occurs. For example, values such as:
1e20, 1, -1e20
can produce different results depending on the accumulation order because
floating-point addition is not associative:
(1e20 + 1) + -1e20 -> 0
(1e20 + -1e20) + 1 -> 1
There is also a transient-overflow case. With sufficiently large finite
floating-point values, an order such as:
large + large + -large
can overflow during the intermediate addition, while:
large + -large + large
does not. Therefore an explicit aggregate ORDER BY can affect not only
rounding
but also whether SUM raises an error.
MIN/MAX have a different issue. Equal values are not necessarily
indistinguishable
values. Numeric values such as 1.0 and 1.00, or strings that compare equal
under
some collations, can have distinguishable representations, so changing
input order
can change which representative is returned. Handling these cases
therefore
requires more type- and collation-specific reasoning.
There is also a separate issue with the ORDER BY expressions themselves.
Removing
an aggregate-local ORDER BY can eliminate evaluation of ORDER-BY-only
expressions.
For example:
count(a ORDER BY nextval('s'))
count(a ORDER BY 1 / b)
cannot simply become count(a), since that would remove a side effect or an
error.
For this first patch I therefore use a deliberately conservative rule: an
ORDER-BY-only expression may be discarded only when it is a bare Var or
Const.
Expressions that are also real COUNT arguments do not have this
restriction,
since their evaluation remains after ORDER BY is removed.
The transformation is done through the existing COUNT
SupportRequestSimplifyAggref
support function, before preprocess_aggrefs(). Consequently the rest of
the
planner sees an ordinary COUNT without requiring special handling in
aggregate
path generation or the executor. This also naturally restores partial/hash
aggregation opportunities and aggregate-state sharing.
I think this provides a small and easily defensible first step. Possible
follow-up work includes:
. broadening the class of ORDER-BY expressions that can safely be
discarded;
. handling outer-level Aggrefs;
. investigating safe subsets of SUM/AVG and MIN/MAX;
. considering DISTINCT simplification for aggregates where duplicates
provably
cannot affect the result.
I kept those out of this patch because each introduces additional semantic
questions
that are independent of the basic COUNT optimization.
The patch includes regression coverage for removable and non-removable
ORDER BY
expressions, DISTINCT, FILTER, volatile expressions, error-producing
expressions,
NULL/empty inputs, and the distinction between ORDER-BY-only expressions
and
expressions that are also real COUNT arguments.
Thoughts and reviews are welcome.
Regards,
Haibo
On Wed, Aug 12, 2026 at 11:01 AM Haibo Yan <tristan.yim@gmail.com> wrote:
I’d like to propose a small optimization for aggregate-local ORDER BY in COUNT.
Currently, for example:SELECT count(a ORDER BY b) FROM t;
is planned as an ordered aggregate, even though the ordering cannot affect the
result of COUNT. This may require a Sort, but the impact is broader than just
the extra sort: having aggorder also prevents partial aggregation and hash
aggregation, and prevents the aggregate from sharing state with an otherwise
identical count(a).
The question that comes to mind is, why would someone (or a program
for that matter) write it this way? Self-join elimination cited ORMs
as a motivation, what is it for this case?
--
John Naylor
Amazon Web Services
John Naylor <johncnaylorls@gmail.com> writes:
On Wed, Aug 12, 2026 at 11:01 AM Haibo Yan <tristan.yim@gmail.com> wrote:
I’d like to propose a small optimization for aggregate-local ORDER BY in COUNT.
Currently, for example:SELECT count(a ORDER BY b) FROM t;
is planned as an ordered aggregate, even though the ordering cannot affect the
result of COUNT. This may require a Sort, but the impact is broader than just
the extra sort: having aggorder also prevents partial aggregation and hash
aggregation, and prevents the aggregate from sharing state with an otherwise
identical count(a).
The question that comes to mind is, why would someone (or a program
for that matter) write it this way? Self-join elimination cited ORMs
as a motivation, what is it for this case?
If someone did write that, they might have a good reason to. I don't
think we should expend planner cycles (not to mention development and
code maintenance effort) on looking for such cases.
regards, tom lane
On Wed, Aug 12, 2026 at 7:46 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
John Naylor <johncnaylorls@gmail.com> writes:
On Wed, Aug 12, 2026 at 11:01 AM Haibo Yan <tristan.yim@gmail.com>
wrote:
I’d like to propose a small optimization for aggregate-local ORDER BY
in COUNT.
Currently, for example:
SELECT count(a ORDER BY b) FROM t;
is planned as an ordered aggregate, even though the ordering cannot
affect the
result of COUNT. This may require a Sort, but the impact is broader
than just
the extra sort: having aggorder also prevents partial aggregation and
hash
aggregation, and prevents the aggregate from sharing state with an
otherwise
identical count(a).
The question that comes to mind is, why would someone (or a program
for that matter) write it this way? Self-join elimination cited ORMs
as a motivation, what is it for this case?If someone did write that, they might have a good reason to. I don't
think we should expend planner cycles (not to mention development and
code maintenance effort) on looking for such cases.regards, tom lane
John, Tom,
That’s a fair question. I should probably explain the motivation better.
I actually started looking at this from cases such as:
sum(a ORDER BY b)
rather than COUNT. An explicit order there is easier to imagine coming
from
generated SQL or from someone trying to make accumulation deterministic.
However, once I looked at the semantics more closely, SUM turned out not to
be a good first target. For example, floating-point SUM can produce
different
results for different input orders because of rounding, and the order can
also
determine whether an intermediate overflow occurs. Interval SUM has
similar
order-dependent overflow issues. Other SUM overloads need to be considered
individually based on their transition implementation.
So I used COUNT as the first, deliberately narrow case because its
aggregate
semantics are much easier to prove: input permutation cannot affect the
COUNT
result. The intent was to first establish the Aggref simplification path
and
its planner benefits, and then investigate which SUM overloads, if any, can
safely use the same mechanism.
I agree that count(a ORDER BY b) by itself is probably not a compelling
workload pattern, so I should not present COUNT frequency as the main
motivation. It is more of a conservative first step toward removing
redundant
aggregate-local ordering where we can prove that doing so is safe.
The question then becomes whether that incremental path is worthwhile, or
whether we should wait until there is a sufficiently useful set of
aggregates/cases to justify the planner and maintenance cost.
Thanks,
Haibo
Hello,
Thanks for the patch. I applied it and did some testing; results below.
It applied cleanly to master (7e6e294e4e4) with a small offset and built
with no new warnings. With an --enable-cassert --enable-debug build on
macOS/aarch64, make check (245/245, including aggregates) and
make check-world both passed, with no assertion failures.
I also checked the behaviour on a running server. The removable cases
lose the Sort as intended, and the non-removable ones are correctly left
alone: DISTINCT, "nl + 1", random(), and the mixed "ORDER BY nl, nn + 1"
case. Results matched plain count() everywhere I tried, including at
300k rows, and the volatile and error cases behave as you describe --
count(nn ORDER BY nextval('s')) advanced the sequence 5 times, stayed at
5 under FILTER (WHERE false), and reached 9 with FILTER (WHERE a <> 2).
The state sharing shows up too. For
SELECT count(nl ORDER BY nn), count(nl) FROM big;
master plans two separate aggregates under a Sort, while with the patch
the plan shows a single "PARTIAL count(nl)". GROUP BY reaches
HashAggregate and the query becomes parallelisable, as you predicted.
I measured 38.8ms -> 8.2ms on 300k rows, though that was a single cached
run, so the plan-shape changes seem like the more meaningful result.
I also confirmed the reasoning for limiting this to COUNT: forced
left-to-right float8 accumulation over (1e20, 1, -1e20) gives 0 or 1
depending on order, and min(numeric) over 1.0/1.00/1.000 returns
different representations. Restricting the first patch to COUNT looks
right.
Thanks,
Rithvika
On Tue, Aug 11, 2026 at 9:01 PM Haibo Yan <tristan.yim@gmail.com> wrote:
Show quoted text
Hi, hackers,
I’d like to propose a small optimization for aggregate-local ORDER BY in
COUNT.
Currently, for example:SELECT count(a ORDER BY b) FROM t;
is planned as an ordered aggregate, even though the ordering cannot affect
the
result of COUNT. This may require a Sort, but the impact is broader than
just
the extra sort: having aggorder also prevents partial aggregation and hash
aggregation, and prevents the aggregate from sharing state with an
otherwise
identical count(a).This patch extends the existing SupportRequestSimplifyAggref handling for
COUNT
to canonicalize such cases before aggregate preprocessing:count(a ORDER BY b) -> count(a)
and, when a is known to be non-null:
count(a ORDER BY b) -> count(*)
I deliberately limited this first patch to COUNT.
It is tempting to apply the same idea to SUM or MIN/MAX, but mathematical
order-independence is not sufficient to prove that removing ORDER BY
preserves
PostgreSQL-visible behavior.For floating-point SUM, input order can affect both the result and whether
an
error occurs. For example, values such as:1e20, 1, -1e20
can produce different results depending on the accumulation order because
floating-point addition is not associative:(1e20 + 1) + -1e20 -> 0
(1e20 + -1e20) + 1 -> 1There is also a transient-overflow case. With sufficiently large finite
floating-point values, an order such as:large + large + -large
can overflow during the intermediate addition, while:
large + -large + large
does not. Therefore an explicit aggregate ORDER BY can affect not only
rounding
but also whether SUM raises an error.MIN/MAX have a different issue. Equal values are not necessarily
indistinguishable
values. Numeric values such as 1.0 and 1.00, or strings that compare
equal under
some collations, can have distinguishable representations, so changing
input order
can change which representative is returned. Handling these cases
therefore
requires more type- and collation-specific reasoning.There is also a separate issue with the ORDER BY expressions themselves.
Removing
an aggregate-local ORDER BY can eliminate evaluation of ORDER-BY-only
expressions.For example:
count(a ORDER BY nextval('s'))
count(a ORDER BY 1 / b)cannot simply become count(a), since that would remove a side effect or an
error.For this first patch I therefore use a deliberately conservative rule: an
ORDER-BY-only expression may be discarded only when it is a bare Var or
Const.
Expressions that are also real COUNT arguments do not have this
restriction,
since their evaluation remains after ORDER BY is removed.The transformation is done through the existing COUNT
SupportRequestSimplifyAggref
support function, before preprocess_aggrefs(). Consequently the rest of
the
planner sees an ordinary COUNT without requiring special handling in
aggregate
path generation or the executor. This also naturally restores
partial/hash
aggregation opportunities and aggregate-state sharing.I think this provides a small and easily defensible first step. Possible
follow-up work includes:. broadening the class of ORDER-BY expressions that can safely be
discarded;
. handling outer-level Aggrefs;
. investigating safe subsets of SUM/AVG and MIN/MAX;
. considering DISTINCT simplification for aggregates where duplicates
provably
cannot affect the result.I kept those out of this patch because each introduces additional semantic
questions
that are independent of the basic COUNT optimization.The patch includes regression coverage for removable and non-removable
ORDER BY
expressions, DISTINCT, FILTER, volatile expressions, error-producing
expressions,
NULL/empty inputs, and the distinction between ORDER-BY-only expressions
and
expressions that are also real COUNT arguments.Thoughts and reviews are welcome.
Regards,
Haibo