PostgreSQL Antipatterns: сизифов JOIN массивов

Иногда возникает задача «склеить» внутри SQL-запроса из переданных в качестве параметров линейных массивов целостную выборку с теми же данными «по столбцам».

Как это иногда делают:

WITH T1 AS (
  SELECT
    row_number() OVER() rn
  , unnest v1
  FROM
    unnest('{1,2,3,4}'::integer[])
)
, T2 AS (
  SELECT
    row_number() OVER() rn
  , unnest v2
  FROM
    unnest('{5,6}'::integer[])
)
SELECT
  T1.v1
, T2.v2
FROM
  T1
LEFT JOIN
  T2
    USING(rn);

v1 | v2
-------
 1 |  5
 2 |  6
 3 |
 4 |

То есть сначала каждый из массивов был «развернут» в выборку, пронумерован, а затем этот номер использовался как ключ соединения CTE…


[посмотреть на explain.tensor.ru]

WITH ORDINALITY

Больше четверти всего времени ушло на пару WindowAgg!

Но если мы используем версию PG не ниже 9.4, то можем применить WITH ORDINALITY для нумерации результатов любой SRF, включая unnest:

WITH T1 AS (
  SELECT
    *
  FROM
    unnest('{1,2,3,4}'::integer[])
    WITH ORDINALITY T(v1, rn)
)
, T2 AS (
  SELECT
    *
  FROM
    unnest('{5,6}'::integer[])
    WITH ORDINALITY T(v2, rn)
)
SELECT
  T1.v1
, T2.v2
FROM
  T1
LEFT JOIN
  T2
    USING(rn);

[посмотреть на explain.tensor.ru].

Таким образом, мы вообще избавились от использования оконных функций.

Multi-argument UNNEST

Но с точки зрения эффективности пока не все хорошо — почти половина времени ушла на Hash Left Join.

Да и автор явно исходил из предположения, что первый массив точно длиннее — потому и воспользовался LEFT JOIN. Но это допущение корректно не всегда, и может вызвать проблемы.

Чтобы его обойти, воспользуемся unnest для нескольких массивов одновременно, который появился с той же версии 9.4:

SELECT
  *
FROM
  unnest(
    '{1,2,3,4}'::integer[]
  , '{5,6}'::integer[]
  ) T(v1, v2);

В результате, и от запроса почти ничего не осталось, и от плана:

Function Scan on t  (cost=0.01..1.00 rows=100 width=8) (actual time=0.006..0.007 rows=4 loops=1)

Значит, и шансов допустить ошибку — намного меньше. Да и по времени выполнения улучшили в несколько раз — а на более длинных массивах эффект будет еще заметнее.