PostgreSQL中的并行unnest（）和排序顺序 [英] Parallel unnest() and sort order in PostgreSQL

问题描述

我了解使用

SELECT unnest(ARRAY[5,3,9]) as id

，不带 ORDER BY 子句，结果集的顺序为不保证。例如，我可以得到：

without an ORDER BY clause, the order of the result set is not guaranteed. I could for example get:

id
--
3
5
9

但是以下请求呢？

SELECT
  unnest(ARRAY[5,3,9]) as id,
  unnest(ARRAY(select generate_series(1, array_length(ARRAY[5,3,9], 1)))) as idx
ORDER BY idx ASC

是它保证了2个 unnest（）调用（长度相同）将并行展开，并且保证索引 idx 确实与项目在数组中的位置匹配吗？

Is it guaranteed that the 2 unnest() calls (which have the same length) will unroll in parallel and that the index idx will indeed match the position of the item in the array?

我正在使用PostgreSQL 9.3.3。

I am using PostgreSQL 9.3.3.

推荐答案

是的，这是Postgres的功能，并且并行取消嵌套 保证 可以保持同步（只要所有数组都具有相同数量的元素）。

Postgres 9.4为并行嵌套添加了一个干净的解决方案：

Yes, that is a feature of Postgres and parallel unnesting is guaranteed to be in sync (as long as all arrays have the same number of elements).
Postgres 9.4 adds a clean solution for parallel unnest:

• 同时嵌套多个数组

• Unnest multiple arrays in parallel

但是，不能保证结果行的顺序。实际上，使用如下语句即可：

The order of resulting rows is not guaranteed, though. Actually, with a statement as simple as:

SELECT unnest(ARRAY[5,3,9]) AS id

行的结果顺序是保证的，但是Postgres没有声明任何东西。只要未明确定义顺序，查询优化器就可以随意对行进行排序。

the resulting order of rows is "guaranteed", but Postgres does not assert anything. The query optimizer is free to order rows as it sees fit as long as the order is not explicitly defined. This may have side effects in more complex queries.

如果问题中的第二个查询是您真正想要的（将索引号添加到未嵌套的数组元素中），则有 generate_subscripts（） ：

If the second query in your question is what you actually want (add an index number to unnested array elements), there is a better way with generate_subscripts():

SELECT unnest(ARRAY[5,3,9]) AS id
     , generate_subscripts(ARRAY[5,3,9], 1) AS idx
ORDER  BY idx;

此相关答案的详细信息：

Details in this related answer:

• 如何访问内部数组用postgreSQL索引吗？

• How to access array internal index with postgreSQL?

您将对 有序 在Postgres 9.4 ：

You will be interested in WITH ORDINALITY in Postgres 9.4:

• 带有元素编号的PostgreSQL unnest（）

• PostgreSQL unnest() with element number

然后您可以使用：

SELECT * FROM unnest(ARRAY[5,3,9]) WITH ORDINALITY tbl(id, idx);

这篇关于PostgreSQL中的并行unnest（）和排序顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！