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

[go-prolog]

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

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

[Kilor]

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

[go-prolog]

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

[Kilor]

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

[go-prolog]

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

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

[Kilor]

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

[DmitryKoterov]

Привет из 2022!

Ну ничо себе. Я только недавно делал разбитие массива на чанки (по 250 элементов) через window function. А тут вон он какой синтаксис есть, оказывается!

[Kilor]

А вроде для такой задачи в самом простом варианте не нужна нумерация вовсе?

SELECT
  arr[i:(i + sz - 1)]
FROM
  (VALUES('{1,2,3,4,5,6,7,8,9,10}'::integer[], 3)) T(arr, sz)
, generate_series(1, array_length(arr, 1), sz) i;

[DmitryKoterov]

Это очень интересно, спасибо! К сожалению, я не знаю заранее, сколько элементов в массиве (выборка производится из другой таблицы, т.е. надо разбить на чанки все id из некоей таблицы). Count делать уж очень не хочется.

У меня получился вот такой запрос (чанки по 250 шт, сортировка по полю updated_at):

SELECT array_agg(id) AS ids
FROM (
  SELECT 
    id,
    ((row_number() over (ORDER BY updated_at)) - 1) / 250 AS chunk
  FROM mytable
  ORDER BY updated_at
) t
GROUP BY chunk
ORDER BY chunk

Эти чанки вставляются балком в другую простенькую таблицу, где поля (seq, ids[]). Скорость получилась около 5 млн id в секунду в сумме (вместе со вставкой) на средней машине.

[Kilor]

Ну, если все равно надо прочитать всю табличку (т.е. нет требования читать по смещению с конкретного чанка), то можно несложно свести к предыдущему:

SELECT
  arr[i:(i + sz - 1)] ids
FROM
  (
    SELECT
      array_agg(id ORDER BY updated_at) arr
    , 250 sz
    FROM
      mytable
  ) T
, generate_series(1, array_length(arr, 1), sz) i;

Вопрос лишь в размере общего массива будет - примерно до 100K еще как-то можно использовать, а дальше будет "ой" с тормозами по памяти.

[DmitryKoterov]

Там может быть и 100 млн записей, так что да, в память сложновато прочитать.

[Kilor]

Попробовал ради интереса собрать вариант без разовой вычитки всей таблицы, когда можно итерировать с клиента по ключу сортировки и он не является целевым полем:

WITH RECURSIVE T AS (
  SELECT
    NULL::name[] chunk
  , 1234::oid lst -- min-ключ сортировки (меньше минимально возможного или с предыдущей итерации)
  , 10 sz         -- размер чанка
  , 5 i           -- лимит чанков
UNION ALL
  SELECT
    X.*
  , sz
  , i - 1
  FROM
    T
  , LATERAL (
      SELECT
        array_agg(relname ORDER BY oid) chunk     -- сортировка прямая
      , (array_agg(oid ORDER BY oid DESC))[1] lst -- сортировка обратная
      FROM
        (
          SELECT
            relname
          , oid
          FROM
            pg_class
          WHERE
            oid > T.lst
          ORDER BY
            oid
          LIMIT sz -- размер чанка
        ) Y
    ) X
  WHERE
    i > 0 AND
    array_length(X.chunk, 1) = sz -- размер чанка еще раз
)
SELECT
  chunk
, lst
FROM
  T
WHERE
  chunk IS NOT NULL;