Re: [planner] Ignore "order by" in subselect if parrent do count(*)

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Marcin Miros*aw<marcin@mejor.pl> wrote:
>> SELECT count(*)
>> from (select * from users_profile order by id) u_p;

>> "order by id" can be ignored by planner.

> This has been discussed before.  Certainly not all ORDER BY clauses
> within query steps can be ignored, so there would need to be code to
> determine whether it was actually useful, which wouldn't be free,
> either in terms of planning time or code maintenance.  It wasn't
> judged to be worth the cost.  If you want to avoid the cost of the
> sort, don't specify ORDER BY where it doesn't matter.

Considering that ORDER BY in a subquery isn't even legal per spec,
there does not seem to be any tenable argument for supposing that
a user wrote it there "by accident".  It's much more likely that
he had some semantic reason for it (say, an order-sensitive function
in a higher query level) and that we'd break his results by ignoring
the ORDER BY.  I doubt that very many of the possible reasons for
needing ordered output are reliably detectable by the planner, either.

            regards, tom lane