Skip site navigation (1) Skip section navigation (2)

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

From: Craig James <cjames(at)emolecules(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>, "Marcin Miros*aw" <marcin(at)mejor(dot)pl>, pgsql-performance(at)postgresql(dot)org
Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*)
Date: 2012-03-01 18:19:31
Message-ID: CAFwQ8rfuNATduYJAm8PJbO+arsvP-uFXEvmi6n-f6Q9A_QaESQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Alessandro GagliardiDate: 2012-03-01 18:27:27
Subject: efficient data reduction (and deduping)
Previous:From: Ants AasmaDate: 2012-03-01 17:53:07
Subject: Re: Bad estimation for "where field not in"

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group