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
