sql - Parallel unnest() and sort order in PostgreSQL

Question

I understand that using

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

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

id
--
3
5
9

But what about the following request:

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

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?

I am using PostgreSQL 9.3.3.

Answer 1

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

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.

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:

• How to access array internal index with postgreSQL?

You will be interested in WITH ORDINALITY in Postgres 9.4:

• PostgreSQL unnest() with element number

Then you can use:

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