12

As per standard SQL UNION / UNION ALL do not guarantee any particular sort order without an outer ORDER BY clause - like there is hardly any place in SQL where sort order is guaranteed without ORDER BY.

However, Postgres uses an "Append" step for plain cases of UNION ALL, hence results from the first leg (even if unsorted within their partition) always come before the next leg, etc. Postgres simply appends results from each leg in the given order. This is particularly relevant with a LIMIT clause:

SELECT 1 FROM tbl  -- or any complex query
UNION ALL
SELECT 2
LIMIT  1

Obviously this does not hold for UNION (without ALL). But other than that, I have never seen Postgres return out of order, i.e. '2' from the above query, while the first SELECT would also return row(s). Not even if the first leg is extremely expensive.

I have based queries on this behavior in the past. Now I have met a claim Postgres might return rows out of order here, but unsubstantiated with actual proof.

The current Postgres manual has this to say on the matter:

UNION effectively appends the result of query2 to the result of query1 (although there is no guarantee that this is the order in which the rows are actually returned). Furthermore, it eliminates duplicate rows from its result, in the same way as DISTINCT, unless UNION ALL is used.

This is pretty unclear. Does the cited order apply to the list of SELECT clauses, or rows within each clause, or just the returned set? Also, UNION ALL is only mentioned in the second sentence, so it remains unclear if the all-important first sentence is supposed to apply to UNION ALL ...

Can anybody show an example, where rows are returned out of order, breaking the sequence of UNION ALL clauses? In any version of Postgres. (Even if the latest version would be most interesting.)

If not that, is there reason to believe this might change in a future version?

ORDER BY is not the immediate issue here. The question is whether multiple UNION ALL clauses return rows in the given sequence (before LIMIT can kick in and stop further branches from executing).

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633

3 Answers3

17

There was a similar question recently in pgsql-docs mailing list,
Clarify the ordering guarantees in combining queries (or lack thereof):

I was trying to understand what - if any - are the guarantees with regards to ordering for combining queries (UNION/UNION ALL/...). From this message1, it seems that UNION ALL does preserve the ordering of the operand queries, whereas UNION does not (presumably neither do INTERSECT, INTERSECT ALL, EXCEPT and EXCEPT ALL).

The documentation[2] makes no mention of this, I'd suggest adding a note clarifying this.

Tom Lane (and others) replied:

Since the documentation doesn't make a guarantee there is none. If you want ordered output use ORDER BY.

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. In any case, there is no guarantee that the behaviour will not change in the future due to planner improvements.

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

=# explain analyze select * from foo union all select * from foo;
                                                                 QUERY PLAN



Gather  (cost=0.00..208552.05 rows=5120008 width=244) (actual time=0.652..390.135 rows=5120000 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..208552.05 rows=2133336 width=244) (actual time=0.021..228.848 rows=1706667 loops=3)
         ->  Parallel Seq Scan on foo  (cost=0.00..98942.68 rows=1066668 width=244) (actual time=0.453..78.084 rows=853333 loops=3)
         ->  Parallel Seq Scan on foo foo_1  (cost=0.00..98942.68 rows=1066668 width=244) (actual time=0.024..125.299 rows=1280000 loops=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.

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
4

It has been established, there is no guarantee that rows from the first UNION ALL term would be returned before rows from the next UNION ALL term etc. Not in standard SQL, and not in Postgres, either.

The clean solution is to not rely on the order of clauses.

Alternatives

For simple queries as shown above (no outer ORDER BY or JOIN) the sequence is observed anyway in Postgres versions before Postgres 11, where the planner method Parallel Append was added. Simply because Append is (was) the only plan option - which is implemented that way.

It still happens to work in current Postgres (v16 at the time of writing) as long as Parallel Append is not involved - which only happens for big sets. So while only handling very few rows (and that is transparent for the query planner!) the order is de-facto preserved.
You can make sure of it by disabling Parallel Append. The manual:

enable_parallel_append can be used to disable this feature.

You might set this option locally, even just for the current transaction to patch up old code quickly with:

SET LOCAL enable_parallel_append = off;

But don't get hung up on provisional workarounds. Better fix your SQL code properly.

Proper Alternatives

vladich commented:

[...] The thing here is if you're using ORDER BY, you lose the convenient functionality of "execute in particular order until certain number of rows are received". I don't see any other way of doing that aside from executing multiple queries sequentially (which could be less efficient and also requires dynamic SQL in general).

The next best thing I can think of is a PL/pgSQL function building up the set. You can check the number of result rows after each query with GET DIAGNOSTICS, modify (reduce) the LIMIT for the next query, and RETURN as soon as you found enough. Can be done without dynamic SQL (so no EXECUTE required) as LIMIT accepts a parameter. See:

More code and planning overhead. But the dynamically adapted LIMIT may produce even better query plans for subsequent queries.

Another example, repeating the same query in a loop, also without dynamic SQL:

Code example for a dynamic LIMIT while also using dynamic SQL with EXECUTE in a loop:

Erwin Brandstetter
  • 185,527
  • 28
  • 463
  • 633
-2

A simple test can be :

CREATE TABLE T (C INT);

INSERT INTO T SELECT * FROM generate_series(0, 999999);


SELECT C
FROM   T
WHERE  C < 500000
UNION ALL
SELECT C
FROM   T
WHERE  C >= 500000;

On a PG Cluster that do parallelism...

SQLpro
  • 550
  • 2
  • 8