# [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.

**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.

More Related questions

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.