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

Re: Sort order in sub-select

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Craig A(dot) James" <cjames(at)modgraph-usa(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Sort order in sub-select
Date: 2006-06-30 06:21:06
Message-ID: 20650.1151648466@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
"Craig A. James" <cjames(at)modgraph-usa(dot)com> writes:
>   insert into hitlist(p_id, sortorder)
>     (select p_id, nextval('hitlist_seq') from
>        (select p_id, min(data) as m from c group by p_id order by m);

> Apparently, the sort order returned by the innermost select is NOT
> maintained as you go through the next select statement -- the rows seem
> to come out in random order.  This surprised me.

It surprises me too.  This is outside the SQL spec, because the spec
doesn't allow ORDER BY in subselects, but Postgres definitely does and
we expect it to be honored.  Can you provide a complete example and the
EXPLAIN plan that you're getting?

			regards, tom lane

In response to

pgsql-performance by date

Next:From: Josh BerkusDate: 2006-06-30 06:33:20
Subject: FWD: Update touches unrelated indexes?
Previous:From: Tom LaneDate: 2006-06-30 06:04:49
Subject: Re: explain analyze reports 20x more time than actual

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