Thread: Another optimizer question

Hi

Is it just me, or is there any way a sort could be relevant in a 
subquery? (except on queries containing volatile functions)

select a.* from test1 a, (select id from test1 order by num) as b where 
a.id = b.id;

There is no constraint on the order of 'a', so why is pull_up_subqueries 
explicitly ignoring subqueries that contain an 'order by'?

-- 
Dennis

On Tue, Jan 27, 2004 at 17:27:25 +0100, Dennis Haney <davh@diku.dk> wrote:
> 
> Is it just me, or is there any way a sort could be relevant in a 
> subquery? (except on queries containing volatile functions)

Yes. It is important when a limit or distinct on clause is used in a
subquery.

Dennis Haney <davh@diku.dk> writes:
> There is no constraint on the order of 'a', so why is pull_up_subqueries 
> explicitly ignoring subqueries that contain an 'order by'?

Because there would be no place to apply the sort operation.  If you are
saying you don't want the sort to occur, why did you write it?
        regards, tom lane

Bruno Wolff III wrote: <blockquote cite="mid20040127174135.GA14806@wolff.to" type="cite"><pre wrap="">On Tue, Jan 27,
2004at 17:27:25 +0100, Dennis Haney <a class="moz-txt-link-rfc2396E"
href="mailto:davh@diku.dk"><davh@diku.dk></a>wrote: </pre><blockquote type="cite"><pre wrap="">Is it just me, or
isthere any way a sort could be relevant in a 
 
subquery? (except on queries containing volatile functions)   </pre></blockquote><pre wrap="">
Yes. It is important when a limit or distinct on clause is used in a
subquery. </pre></blockquote> Yup, but queries with those are also ignored, so I'm only talking about explicit 'order
by'without any fancy stuff ;)<br /><br /><br /><pre class="moz-signature" cols="72">-- 
 
Dennis
</pre>

Tom Lane wrote: <blockquote cite="mid24882.1075226771@sss.pgh.pa.us" type="cite"><pre wrap="">Dennis Haney <a
class="moz-txt-link-rfc2396E"href="mailto:davh@diku.dk"><davh@diku.dk></a> writes: </pre><blockquote
type="cite"><prewrap="">There is no constraint on the order of 'a', so why is pull_up_subqueries 
 
explicitly ignoring subqueries that contain an 'order by'?   </pre></blockquote><pre wrap="">
Because there would be no place to apply the sort operation. </pre></blockquote> Then why spend time doing it at all?
<blockquotecite="mid24882.1075226771@sss.pgh.pa.us" type="cite"><pre wrap=""> If you are saying you don't want the sort
tooccur, </pre></blockquote> I'm saying the sort makes no sense. So why even bother executing it?<br /><br
/><blockquotetype="cite"><pre wrap=""> why did you write it?</pre></blockquote> I believe the most common scenario
wouldbe that the subquery was expanded from a view...<br /><br /><pre class="moz-signature" cols="72">-- 
 
Dennis
</pre>

Dennis Haney kirjutas T, 27.01.2004 kell 21:08:
> Tom Lane wrote: 
> > Dennis Haney <davh@diku.dk> writes:
> >   
> > > There is no constraint on the order of 'a', so why is pull_up_subqueries 
> > > explicitly ignoring subqueries that contain an 'order by'?
> > >     
> > Because there would be no place to apply the sort operation. 
> Then why spend time doing it at all? 
> >  If you are saying you don't want the sort to occur,
> >   
> I'm saying the sort makes no sense. So why even bother executing it?
> 
> >  why did you write it?
> I believe the most common scenario would be that the subquery was
> expanded from a view...

And why is it written on the outer level of view. AFAIK any select from
that view is also free to ignore it.

-----------
Hannu

Hannu Krosing <hannu@tm.ee> writes:
> Dennis Haney kirjutas T, 27.01.2004 kell 21:08:
>> I'm saying the sort makes no sense. So why even bother executing it?
>> 
>>> why did you write it?
>>
>> I believe the most common scenario would be that the subquery was
>> expanded from a view...

> And why is it written on the outer level of view. AFAIK any select from
> that view is also free to ignore it.

Indeed.  If we decree that we can drop an ORDER BY in a subselect then
there is no reason for anyone to write an ORDER BY in a view, because a
view is exactly the same thing as a subselect.

As a more direct response, there *are* reasons for people to put ORDER
BY in a subselect and expect it to be honored.  The typical example
that's been discussed several times in the archives is that you want to
use an aggregate function that is sensitive to the ordering of its input
values.  (None of the SQL-standard ones are, of course, but we've
frequently seen examples wherein it's convenient to build a user-defined
aggregate that is ordering-sensitive.)
        regards, tom lane

> As a more direct response, there *are* reasons for people to put ORDER
> BY in a subselect and expect it to be honored.  The typical example
> that's been discussed several times in the archives is that you want to
> use an aggregate function that is sensitive to the ordering of its input

Not to mention our workaround for Max and min (ORDER BY LIMIT)

Rod Taylor <pg@rbt.ca> writes:
>> As a more direct response, there *are* reasons for people to put ORDER
>> BY in a subselect and expect it to be honored.  The typical example
>> that's been discussed several times in the archives is that you want to
>> use an aggregate function that is sensitive to the ordering of its input

> Not to mention our workaround for Max and min (ORDER BY LIMIT)

Right, although one could reasonably expect that an optimization to drop
ORDER BY wouldn't drop it if there were a LIMIT there as well.  The
planner knows perfectly well that those two clauses interact.  The cases
that are relevant are where the planner could not realize that dropping
the ORDER BY would change the results in an unwanted way.  The aggregate
function example is interesting because the planner doesn't know whether
an aggregate function is order-sensitive or not.  (We could imagine
extending pg_aggregate and CREATE AGGREGATE to tell that, if we were
determined to drop ORDER BY in subselects whenever possible.  But I'm
not sure that that's the only relevant issue.)
        regards, tom lane