12.12.2019 11:00
Kilor 6 4600 Источник
Иногда возникает задача «склеить» внутри 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)

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

Комментарии (6)


  1. go-prolog
    12.12.2019 23:10
    #20999932

    Интересное следствие получили, вместо задачи «склеить» два массива, просто зададим константу,
    может, так еще быстрее:

    select * 
from (values (1,5),(2,6),(3,null),(4,null))t(v1,v2)


    1. Kilor Автор
      12.12.2019 23:12
      #20999958

      Сможете передать содержимое VALUES через пару входящих параметров?


      1. go-prolog
        12.12.2019 23:16
        #20999976

        Если трактовать проблему так, то зачем передавать в запрос два массива для простого «склеить», это же задача одного цикла,
        а откуда передаем-то?


        1. Kilor Автор
          12.12.2019 23:29
          #21000020

          Почему же "для простого"? Можно представить это началом сложного prepared statement, в который хочется передать матрицу не вполне фиксированного размера в качестве параметра.
          Для этого существует несколько способов разной проблемности, но про это будет отдельная статья.


          1. go-prolog
            12.12.2019 23:45
            #21000096

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

            Нужны матрицы — временные таблицы,
            при чем тут «склеить» и в заголовке «ДЖОИН».


            1. Kilor Автор
              12.12.2019 23:57
              #21000132

              Временная таблица в PG — это достаточно дорогое решение для передачи параметров в часто исполняемый запрос, потому что приводит к созданию дополнительных записей в pg_class, pg_attribute, pg_depend,… Они от этого распухают и потом плохо себя чувствуют.