Re: Add proper planner support for ORDER BY / DISTINCT aggregates

On 2/07/21 8:39 pm, David Rowley wrote:
> On Fri, 2 Jul 2021 at 19:54, Ronan Dunklau <ronan(dot)dunklau(at)aiven(dot)io> wrote:
>> I don't know if it's acceptable, but in the case where you add both an
>> aggregate with an ORDER BY clause, and another aggregate without the clause,
>> the output for the unordered one will change and use the same ordering, maybe
>> suprising the unsuspecting user. Would that be acceptable ?
> That's a good question. There was an argument in [1] that mentions
> that there might be a group of people who rely on aggregation being
> done in a certain order where they're not specifying an ORDER BY
> clause in the aggregate. If that group of people exists, then it's
> possible they might be upset in the scenario that you describe.

[...]

I've always worked on the assumption that if I do not specify an ORDER
BY clause then the rdbms is expected to present rows in the order most
efficient for it to do so. If pg orders rows when not requested then
this could add extra elapsed time to the query, which might be
significant for large queries.

I don't know of any rdbms that guarantees the order of returned rows
when an ORDER BY clause is not used.

So I think that pg has no obligation to protect the sensibilities of
naive users in this case, especially at the expense of users that want
queries to complete as quickly as possible.  IMHO

Cheers,
Gavin