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


Tue, 27 Jan 2004 17:27:25 +0100
Dennis Haney <davh@diku.dk>
Another optimizer question
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.

Tue, 27 Jan 2004 11:41:35 -0600
Bruno Wolff III <bruno@wolff.to>
Re: Another optimizer question
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

Tue, 27 Jan 2004 13:06:11 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another optimizer question 
Bruno Wolff III wrote:

>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.
>  
>
Yup, but queries with those are also ignored, so I'm only talking about 
explicit 'order by' without any fancy stuff ;)


-- 
Dennis








Bruno Wolff III wrote:

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.
  

Yup, but queries with those are also ignored, so I'm only talking about
explicit 'order by' without any fancy stuff ;)


-- 
Dennis
Tue, 27 Jan 2004 20:05:51 +0100
Dennis Haney <davh@diku.dk>
Re: Another optimizer question
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...

-- 
Dennis








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...

-- 
Dennis
Tue, 27 Jan 2004 20:08:58 +0100
Dennis Haney <davh@diku.dk>
Re: Another optimizer question
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


Tue, 27 Jan 2004 23:22:14 +0200
Hannu Krosing <hannu@tm.ee>
Re: Another optimizer question
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

Tue, 27 Jan 2004 16:59:46 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another optimizer question 
> 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)


Tue, 27 Jan 2004 17:19:03 -0500
Rod Taylor <pg@rbt.ca>
Re: Another optimizer question
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

Tue, 27 Jan 2004 17:30:48 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Another optimizer question 