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

On Thu, Mar 1, 2012 at 9:50 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "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,

That's surprising ... normally it won't affect the result, but with an
offset or limit it would. Does the offset or limit change the "not
even legal" part? Something like:

select * from foo where foo_id in (select bar_id from bar order by
bar_id offset 10 limit 10);

Craig

> 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
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance