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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Marcin Miros*aw<marcin(at)mejor(dot)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