Re: [PATCH] Remove redundant ORDER BY from COUNT aggregates

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(dot)yim(at)gmail(dot)com> wrote:

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