Re: Another optimizer question

Rod Taylor <pg(at)rbt(dot)ca> writes:
>> As a more direct response, there *are* reasons for people to put ORDER
>> BY in a subselect and expect it to be honored. The typical example
>> that's been discussed several times in the archives is that you want to
>> use an aggregate function that is sensitive to the ordering of its input

> Not to mention our workaround for Max and min (ORDER BY LIMIT)

Right, although one could reasonably expect that an optimization to drop
ORDER BY wouldn't drop it if there were a LIMIT there as well. The
planner knows perfectly well that those two clauses interact. The cases
that are relevant are where the planner could not realize that dropping
the ORDER BY would change the results in an unwanted way. The aggregate
function example is interesting because the planner doesn't know whether
an aggregate function is order-sensitive or not. (We could imagine
extending pg_aggregate and CREATE AGGREGATE to tell that, if we were
determined to drop ORDER BY in subselects whenever possible. But I'm
not sure that that's the only relevant issue.)

regards, tom lane