Параллельный unnest() и порядок сортировки в PostgreSQL

Я понимаю, что с помощью

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

без ORDER BY предложение, порядок набора результатов не гарантируется. Я мог бы, например, получить:

id
--
3
5
9

Но как насчет следующего запроса:

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

Гарантируется ли, что 2 unnest() вызовы (которые имеют одинаковую длину) будут развернуты параллельно и что индекс idx будет действительно соответствовать позиции элемента в массиве?

Я использую PostgreSQL 9.3.3.

Источник

2 ответа

Решение

Да, это особенность Postgres, и параллельное удаление будет гарантированно синхронизировано (при условии, что все массивы имеют одинаковое количество элементов).
Postgres 9.4 добавляет чистое решение для параллельного удаления:

Однако порядок результирующих строк не гарантируется. На самом деле, с таким простым утверждением, как:

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

результирующий порядок строк "гарантирован", но Postgres ничего не утверждает. Оптимизатор запросов может свободно упорядочивать строки по своему усмотрению, если порядок явно не определен. Это может иметь побочные эффекты в более сложных запросах.

Если второй запрос в вашем вопросе - это то, что вы на самом деле хотите (добавить индексный номер для неперемещенных элементов массива), есть более эффективный способ с помощью generate_subscripts ():

SELECT unnest(ARRAY[5,3,9]) AS id
     , generate_subscripts(ARRAY[5,3,9], 1) AS idx
ORDER  BY idx;

Подробности в этом ответе:

Вы будете заинтересованы в WITH ORDINALITY в Postgres 9.4:

Тогда вы можете использовать:

SELECT * FROM unnest(ARRAY[5,3,9]) WITH ORDINALITY tbl(id, idx);
Источник

Краткий ответ: нет, idx не будет соответствовать позициям массива при принятии предпосылки, что unnest() вывод может быть случайным образом упорядочен.

Демо: начиная с текущей реализации unnest на самом деле выводим строки в порядке элементов, я предлагаю добавить слой поверх него для имитации случайного порядка:

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

Затем проверьте несколько исполнений вашего запроса с unnest заменен на 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

Пример вывода:

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

id=3 связан с idx=1 но 3 был на 2-й позиции в массиве. Это все неправильно.

Что не так в запросе: предполагается, что первый unnest будет перемешивать элементы, используя ту же перестановку, что и второй unnest (Перестановка в математическом смысле: связь между порядком в массиве и порядком строк). Но это предположение противоречит предпосылке, что порядок вывода unnest непредсказуемо для начала.

Об этом вопросе:

Гарантируется ли, что 2 вызова unnest() (одинаковой длины) будут развернуты параллельно

В select unnest(...) X1, unnest(...) X2, с X1 а также X2 быть типом SETOF something и с таким же количеством строк, X1 а также X2 будет в паре в окончательном выводе, так что X1 значение в строке N столкнется с X2 значение в той же строке N, (это своего рода СОЮЗ для колонн, в отличие от декартового произведения).

Но я бы не охарактеризовал это спаривание как параллельное развертывание, поэтому я не уверен, что вы это имели в виду.

В любом случае, это спаривание не поможет с проблемой, так как это происходит после того, как нестандартные вызовы потеряли позиции массива.

Альтернатива: в этой теме из списка рассылки pgsql-sql предлагается следующая функция:

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;

Исходя из этого, мы можем упорядочить по второму столбцу вывода:

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

 значение | ordinality 
-------+------------
     5 |          1
     3 |          2
     9 |          3

С Postgres 9,4 и выше: WITH ORDINALITY Предложение, которое может следовать за вызовами функции SET RETURNING, предоставит эту функцию в общем виде.

Источник
Другие вопросы по тегам