From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kaloyan Iliev Iliev <news1@faith.digsys.bg>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problems with UNION ALL and ORDER BY
Date: Mon, 02 Aug 2004 13:09:42 -0400
Message-ID: <12126.1091466582@sss.pgh.pa.us> (raw)
In-Reply-To: <410E6E42.6030206@faith.digsys.bg>
References: <410E6376.1010404@faith.digsys.bg>
<10984.1091464511@sss.pgh.pa.us>
<410E6E42.6030206@faith.digsys.bg>
Kaloyan Iliev Iliev <news1@faith.digsys.bg> writes:
> As I say in my previous letter I am using 7.2.3. If you wish I can show
> you the query and the result to see for yourself, that there is
> something wrong. It just don't order the overall result but the separate
> results of the both subqueries.
Quite honestly, I don't believe it. In 7.2 I get
regression=# explain select * from tenk1 a union all select * from tenk1 b order by unique1;
NOTICE: QUERY PLAN:
Sort (cost=3128.28..3128.28 rows=20000 width=148)
-> Append (cost=0.00..760.00 rows=20000 width=148)
-> Subquery Scan *SELECT* 1 (cost=0.00..380.00 rows=10000 width=148)
-> Seq Scan on tenk1 a (cost=0.00..380.00 rows=10000 width=148)
-> Subquery Scan *SELECT* 2 (cost=0.00..380.00 rows=10000 width=148)
-> Seq Scan on tenk1 b (cost=0.00..380.00 rows=10000 width=148)
EXPLAIN
and as you can see there's only one sort step being applied to the union
result.
Now if EXPLAIN shows you a different sorting structure for your query,
then I'd be interested to see the exact query and the EXPLAIN output.
But what I think is that you are misinterpreting the sorting result you
get. If you are using a non-C locale you may be seeing some pretty
weird sorting rules :-(
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, news1@faith.digsys.bg
Subject: Re: Problems with UNION ALL and ORDER BY
In-Reply-To: <12126.1091466582@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox