From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pantelis Theodosiou <ypercube@gmail.com>
cc: Shay Rojansky <roji@roji.org>,
        "David G. Johnston" <david.g.johnston@gmail.com>,
        pgsql-docs <pgsql-docs@postgresql.org>
Subject: Re: Clarify the ordering guarantees in combining queries (or lack
 thereof)
In-reply-to: 
 <CAE3TBxzS4OHsB23guzu0Xe-xvjs7tMODgJqopm6NyF0pSjcb+w@mail.gmail.com>
References: 
 <CADT4RqD0oWGCeSdGtN3UEH21HxF=1Svbr3dvEL9ueuEr-x5L4g@mail.gmail.com>
 <CAKFQuwZLpXqOs5JhtyLkEhv6Y6TAUK-aJuMOG9v1-zatgrZq5Q@mail.gmail.com>
 <CADT4RqCGMsRpW-76yhnzxJokjgOuo-faNu68Vdq4Lf3AKWPybg@mail.gmail.com>
 <CAE3TBxzS4OHsB23guzu0Xe-xvjs7tMODgJqopm6NyF0pSjcb+w@mail.gmail.com>
Comments: In-reply-to Pantelis Theodosiou <ypercube@gmail.com>
	message dated "Thu, 14 Jul 2022 11:22:43 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2742510.1657800108.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 14 Jul 2022 08:01:48 -0400
Message-ID: <2742511.1657800108@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2742511.1657800108%40sss.pgh.pa.us>
Precedence: bulk

Pantelis Theodosiou <ypercube@gmail.com> writes:
> On Thu, Jul 14, 2022 at 9:16 AM Shay Rojansky <roji@roji.org> wrote:
>> I was trying to understand what - if any - are the guarantees with
>> regards to ordering for combining queries (UNION/UNION ALL/...).

> No, there is no guarantee. It's just that UNION ALL works this way today
> (preserving the order of the subselects) - and I'm not even sure about
> that, it may not preserve the order in all cases, with different indexes=
 or
> partitioning or a parallel plan, etc.

Yeah, that.  You can get a parallelized plan today for UNION ALL:

=3D# explain analyze select * from foo union all select * from foo;
                                                                 QUERY PLA=
N                                                                 =

--------------------------------------------------------------------------=
------------------------------------------------------------------
 Gather  (cost=3D0.00..208552.05 rows=3D5120008 width=3D244) (actual time=3D=
0.652..390.135 rows=3D5120000 loops=3D1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=3D0.00..208552.05 rows=3D2133336 width=3D244=
) (actual time=3D0.021..228.848 rows=3D1706667 loops=3D3)
         ->  Parallel Seq Scan on foo  (cost=3D0.00..98942.68 rows=3D10666=
68 width=3D244) (actual time=3D0.453..78.084 rows=3D853333 loops=3D3)
         ->  Parallel Seq Scan on foo foo_1  (cost=3D0.00..98942.68 rows=3D=
1066668 width=3D244) (actual time=3D0.024..125.299 rows=3D1280000 loops=3D=
2)
 Planning Time: 0.094 ms
 Execution Time: 488.352 ms

It's true that in simple non-parallelized cases we'll do the first query
then the second, but SQL doesn't promise that to be true and neither does
Postgres.

>>> If you want ordered output use ORDER BY.

>> I don't see how that could be done. Consider the following:
>> (SELECT id FROM data ORDER BY id)
>> UNION ALL
>> (SELECT id FROM data ORDER BY id DESC);

You do it like this:

=3D# explain analyze (select * from foo union all select * from foo) order=
 by unique1;
                                                                    QUERY =
PLAN                                                                    =

--------------------------------------------------------------------------=
------------------------------------------------------------------------
 Gather Merge  (cost=3D839258.04..889070.63 rows=3D4266672 width=3D244) (a=
ctual time=3D931.054..1707.780 rows=3D5120000 loops=3D1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=3D839258.01..844591.35 rows=3D2133336 width=3D244) (act=
ual time=3D924.821..1132.096 rows=3D1706667 loops=3D3)
         Sort Key: foo.unique1
         Sort Method: external merge  Disk: 433552kB
         Worker 0:  Sort Method: external merge  Disk: 423400kB
         Worker 1:  Sort Method: external merge  Disk: 415592kB
         ->  Parallel Append  (cost=3D0.00..208552.05 rows=3D2133336 width=
=3D244) (actual time=3D0.051..218.476 rows=3D1706667 loops=3D3)
               ->  Parallel Seq Scan on foo  (cost=3D0.00..98942.68 rows=3D=
1066668 width=3D244) (actual time=3D0.030..79.118 rows=3D853333 loops=3D3)
               ->  Parallel Seq Scan on foo foo_1  (cost=3D0.00..98942.68 =
rows=3D1066668 width=3D244) (actual time=3D0.073..118.566 rows=3D1280000 l=
oops=3D2)
 Planning Time: 0.109 ms
 Execution Time: 1830.390 ms
(13 rows)

The parentheses are actually optional here, if memory serves --- to get
the ORDER BY to be applied inside the second sub-select, you'd have to
use parens as Shay had it.

			regards, tom lane