[ACCEPTED]-Parallel unnest() and sort order in PostgreSQL-set-returning-functions

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

• Unnest multiple arrays in parallel

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

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

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

If 6 the second query in your question is what 5 you actually want (add an index number to 4 unnested array elements), there is a better 3 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 2 will be interested in WITH ORDINALITY in Postgres 9.4:

• PostgreSQL unnest() with element number

Then 1 you can use:

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

Short answer: No, idx will not match the array positions, when 34 accepting the premise that unnest() output may be 33 randomly ordered.

Demo: since the current implementation 32 of unnest actually output the rows in the order 31 of elements, I suggest to add a layer on 30 top of it to simulate a random order:

CREATE FUNCTION unnest_random(anyarray)  RETURNS setof anyelement
language sql as
$$ select unnest($1) order by random() $$;

Then 29 check out a few executions of your query 28 with unnest replaced by unnest_random:

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

Example of output:

 id | idx 
----+-----
  3 |   1
  9 |   2
  5 |   3

id=3 is 27 associated with idx=1 but 3 was in 2nd position 26 in the array. It's all wrong.

What's wrong in the query: it assumes 25 that the first unnest will shuffle the elements 24 using the same permutation as the second 23 unnest (permutation in the mathematic sense: the 22 relationship between order in the array 21 and order of the rows). But this assumption 20 contradicts the premise that the order output 19 of unnest is unpredictable to start with.

About this question:

Is it 18 guaranteed that the 2 unnest() calls (which 17 have the same length) will unroll in parallel

In 16 select unnest(...) X1, unnest(...) X2, with X1 and X2 being of type SETOF something and having the 15 same number of rows, X1 and X2 will be paired 14 in the final output so that the X1 value at 13 row N will face the X2 value at the same row 12 N. (it's a kind of UNION for columns, as 11 opposed to a cartesian product).

But I wouldn't 10 describe this pairing as unroll in parallel, so I'm not sure 9 this is what you meant.

Anyway this pairing 8 doesn't help with the problem since it happens 7 after the unnest calls have lost the array 6 positions.

An alternative: In this thread from the pgsql-sql mailing 5 list, this function is suggested:

CREATE OR REPLACE FUNCTION unnest_with_ordinality(anyarray, OUT value
anyelement, OUT ordinality integer)
  RETURNS SETOF record AS
$$
SELECT $1[i], i FROM
    generate_series(array_lower($1,1),
                    array_upper($1,1)) i;
$$
LANGUAGE sql IMMUTABLE; 

Based on 4 this, we can order by the second output 3 column:

select * from unnest_with_ordinality(array[5,3,9]) order by 2;

 value | ordinality 
-------+------------
     5 |          1
     3 |          2
     9 |          3

With postgres 9.4 and above: The WITH ORDINALITY clause that can follow SET 2 RETURNING function calls will provide this 1 functionality in a generic way.