Ron Mayer <rm_pg@xxxxxxxxxxxxxxxxxxxxxxx> 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
