Re: Unexpected sort order.

Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> writes:
> Tom Lane wrote:
>> It looks to me like the planner thinks that order by a and order by b
>> are equivalent because the expressions are equal(); hence it discards
>> what it thinks is a redundant second sort step.

> Would it be a smaller waste of cycles and still avoid the problem
> if the planner blindly kept only the second sort step rather than
> the first one when it sees these redundant steps? Or would that
> get other cases wrong?

I was fuzzing the explanation a bit --- there really isn't any place
that we could simply reverse the logic and get the other behavior.
The real issue is that the planner's "PathKey" representation of sort
ordering is actually incapable of distinguishing whether the sub-query
is sorted by a or by b: in either case the PathKeyItem will contain the
expression "(random()*10)::int". So when the upper query tries to
decide whether the lower query is already sorted the way it wants,
it'll come out with a match. We surely don't want to discard the
optimization of avoiding redundant sorts of subquery outputs, so the
only way to "fix" this would be a fundamental redesign of the PathKey
mechanism to special-case volatile expressions somehow. I'm resistant
to doing that without a fairly solid use-case for sorting by volatile
expressions ...

regards, tom lane