Re: Another optimizer question

Hannu Krosing <hannu@tm.ee> writes:
> Dennis Haney kirjutas T, 27.01.2004 kell 21:08:
>> I'm saying the sort makes no sense. So why even bother executing it?
>> 
>>> why did you write it?
>>
>> I believe the most common scenario would be that the subquery was
>> expanded from a view...

> And why is it written on the outer level of view. AFAIK any select from
> that view is also free to ignore it.

Indeed.  If we decree that we can drop an ORDER BY in a subselect then
there is no reason for anyone to write an ORDER BY in a view, because a
view is exactly the same thing as a subselect.

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
values.  (None of the SQL-standard ones are, of course, but we've
frequently seen examples wherein it's convenient to build a user-defined
aggregate that is ordering-sensitive.)
        regards, tom lane