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

On Wed, Aug 12, 2026 at 7:46 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> John Naylor <johncnaylorls(at)gmail(dot)com> writes:
> > On Wed, Aug 12, 2026 at 11:01 AM Haibo Yan <tristan(dot)yim(at)gmail(dot)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