主页 / dba / 问题 / 316818
Accepted
Erwin Brandstetter
Asked: 2022-09-13 15:50:54 +0800 CST

UNION ALL 子句的结果是否总是按顺序附加？

  • 772

根据标准 SQL UNION/UNION ALL不保证任何特定的排序顺序没有外部ORDER BY子句 - 就像 SQL 中几乎没有任何地方可以保证排序顺序没有ORDER BY.

但是，Postgres 对 的普通情况使用“追加”步骤UNION ALL，因此第一条腿的结果（即使在它们的分区中未排序）总是在下一条腿之前，等等。Postgres 只是按照给定的顺序附加来自每条腿的结果。这与一个LIMIT子句特别相关：

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

显然，这不适用于UNION（没有ALL）。但除此之外，我从未见过 Postgres 乱序返回，即上述查询中的“2”SELECT ，而第一个也会返回行。即使第一站非常昂贵，也不是。

我过去曾基于此行为的查询。现在我遇到了一个说法，Postgres 可能会在这里返回乱序的行，但没有得到实际证据的证实。

当前的Postgres 手册对此事有这样的说法：

UNION有效地将结果附加到query2结果query1（尽管不能保证这是实际返回行的顺序）。此外，它从结果中消除重复行，与使用 , 的方式 DISTINCT相同UNION ALL。

这很不清楚。引用的顺序是否适用于SELECT子句列表，或每个子句中的行，还是仅适用于返回的集合？此外，UNION ALL仅在第二句中提及，因此尚不清楚最重要的第一句是否应该适用于UNION ALL...

任何人都可以举一个例子，其中行被乱序返回，破坏了UNION ALL子句的顺序？在任何版本的 Postgres 中。（即使最新版本最有趣。）

如果不是这样，是否有理由相信这可能会在未来的版本中发生变化？

ORDER BY不是这里的直接问题。问题是多个UNION ALL子句是否返回给定序列中的行（之前LIMIT可以启动并阻止进一步的分支执行）。

postgresql order-by

2 个回答

  1. Best Answer

    最近在 pgsql-docs 邮件列表中有一个类似的问题，
    澄清组合查询中的排序保证（或缺乏）：

    我试图了解关于组合查询（UNION/UNION ALL/...）的排序的保证（如果有的话）。从这条消息1来看，似乎 UNION ALL 确实保留了操作数查询的顺序，而 UNION 没有（大概也没有 INTERSECT、INTERSECT ALL、EXCEPT 和 EXCEPT ALL）。

    文档 [2] 没有提到这一点，我建议添加一个说明来澄清这一点。

    汤姆·莱恩（和其他人）回答说：

    由于文档不能保证没有。如果您想要有序输出，请使用 ORDER BY。

    不，没有保证。只是今天 UNION ALL 以这种方式工作（保留子选择的顺序） - 我什至不确定，它可能不会在所有情况下都保留顺序，具有不同的索引或分区或并行计划等。在任何情况下，都不能保证将来不会因为规划器的改进而改变行为。

    是的，那个。您今天可以获得 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

    确实，在简单的非并行化情况下，我们将执行第一个查询然后执行第二个查询，但 SQL 并没有保证这是真的，Postgres 也没有。

    • 8

  2. 已经确定，不能保证第一个UNION ALL术语的行会在下一个UNION ALL术语的行之前返回，等等。在标准 SQL 中，也不在 Postgres 中。

    干净的解决方案是不依赖子句的顺序。

    备择方案

    对于如上所示的简单查询（没有外部ORDER BYor JOIN），无论如何都会观察到序列，直到添加与Postgres 11Parallel Append的并行计划。仅仅因为是唯一的计划选项-（曾经）以这种方式实施。Append

    只要不涉及它，它仍然恰好在当前的Postgres 15Parallel Append中工作——这只发生在大集合中。您可以通过禁用该选项来确保它。手册：

    enable_parallel_append可用于禁用此功能。

    您可以在本地设置此选项，即使只是为了当前事务快速修补旧代码：

    SET LOCAL enable_parallel_append = off;

    不要挂断这种临时解决方法。更好地正确修复您的 SQL 代码。

    适当的选择

    弗拉迪奇评论说：

    [...]这里的事情是，如果您正在使用ORDER BY，您将失去“以特定顺序执行直到收到一定数量的行”的便捷功能。除了顺序执行多个查询（这可能效率较低并且通常还需要动态 SQL）之外，我没有看到任何其他方法。

    我能想到的下一个最好的事情是建立集合的 PL/pgSQL 函数。您可以使用 来检查每个查询后的结果行数GET DIAGNOSTICS，修改（减少）LIMIT下一个查询的行数，RETURN只要找到足够的。可以在没有动态 SQL（因此EXECUTE不需要）的情况下完成，因为它LIMIT接受参数。看：

    相当多的代码。更多的计划开销。但好的一面是：动态调整LIMIT甚至可以为后续查询生成更好的查询计划。

    另一个例子，在循环中重复相同的查询，也没有动态 SQL：

    在循环中LIMIT同时使用动态 SQL的动态代码示例：EXECUTE

    • 3

相关问题