X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D1EBE9FA5FC for ; Fri, 30 Jun 2006 03:21:19 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 56718-01 for ; Fri, 30 Jun 2006 03:21:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 096079FA0EC for ; Fri, 30 Jun 2006 03:21:08 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k5U6L6Tn020651; Fri, 30 Jun 2006 02:21:06 -0400 (EDT) To: "Craig A. James" cc: pgsql-performance@postgresql.org Subject: Re: Sort order in sub-select In-reply-to: <44A4AEAE.8070809@modgraph-usa.com> References: <44A4AEAE.8070809@modgraph-usa.com> Comments: In-reply-to "Craig A. James" message dated "Thu, 29 Jun 2006 21:55:10 -0700" Date: Fri, 30 Jun 2006 02:21:06 -0400 Message-ID: <20650.1151648466@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/491 X-Sequence-Number: 19848 "Craig A. James" 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