[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:
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:
You 2 will be interested in
WITH ORDINALITY in Postgres 9.4:
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.
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
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
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
select unnest(...) X1, unnest(...) X2, with
X2 being of type
SETOF something and having the 15 same number of rows,
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
(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.
More Related questions