[PATCH] Remove redundant ORDER BY from COUNT aggregates

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