To: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
cc: "=?ISO-8859-15?Q?Marcin=20Miros*aw?=" <marcin@mejor.pl>,
	pgsql-performance@postgresql.org
Subject: Re: [planner] Ignore "order by" in subselect if parrent do count(*) 
In-reply-to: <4F4F54910200002500045D59@gw.wicourts.gov> 
References: <4F4F6158.7060200@mejor.pl>
	<4F4F54910200002500045D59@gw.wicourts.gov>
Comments: In-reply-to "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
	message dated "Thu, 01 Mar 2012 10:50:57 -0600"
Date: Thu, 01 Mar 2012 12:50:02 -0500
Message-ID: <14667.1330624202@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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