Parallel unnest() and sort order in PostgreSQL
postgresql, postgresql-9.3, set-returning-functions, sql, unnest
Solution
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);
Problem
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.