Как стать автором
Обновить
138.66
Тензор
Разработчик системы СБИС

PostgreSQL Antipatterns: «где-то я тебя уже видел...»

Время на прочтение4 мин
Количество просмотров20K

Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел...

Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?

Wake up, Neo. The Matrix has you.
Wake up, Neo. The Matrix has you.

Одна запись - несколько полей

Возьмем простую и достаточно типовую бизнес-задачу - показать последний документ по каждому из некоторого набора покупателей:

CREATE TABLE doc(
  doc_id
    serial
      PRIMARY KEY
, customer_id
    integer
, dt
    date
, sum
    numeric(32,2)
);
CREATE INDEX ON doc(customer_id, dt DESC);

INSERT INTO doc(
  customer_id
, dt
, sum
)
SELECT
  (random() * 1e5)::integer
, now() - random() * '1 year'::interval
, random() * 1e6
FROM
  generate_series(1, 1e5) id;

Для каждого клиента мы хотим иметь в результате исполнения запроса все значимые поля этого "последнего" документа. Что ж, "как слышится, так и пишется":

SELECT
  id customer_id
, (SELECT doc_id FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) doc_id
, (SELECT dt     FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) dt
, (SELECT sum    FROM doc WHERE customer_id = id ORDER BY dt DESC LIMIT 1) sum
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id;

И... мы героически вытаскиваем одну и ту же запись из таблицы трижды! [посмотреть на explain.tensor.ru]

Троекратное "ура!" разработчику от сервера
Троекратное "ура!" разработчику от сервера

Возврат целой записи таблицы

И вот зачем мы каждое поле отдельно ищем? Мало того, что это раздувает размер запроса, так еще и выполняется каждый раз заново!

Давайте вернем из вложенного запроса сразу всю запись (только id мы теперь не будем переименовывать, чтобы не получилось два customer_id в результате):

SELECT
  id
, (
    SELECT
      doc -- это запись всей таблицы
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ).* -- разворачиваем запись в отдельные поля
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id;

И... теперь вместо 3 циклов у нас стало 4 - по одному на каждое поле извлекаемой вложенным запросом записи, включая customer_id (причем Index Only Scan, когда dt можно было вернуть прямо из индекса, превратился в менее эффективный, зато полностью совпадающий с остальными, Index Scan):

Экранируем запись с помощью CTE

WITH dc AS (
  SELECT
    id
  , (
      SELECT
        doc
      FROM
        doc
      WHERE
        customer_id = id
      ORDER BY
        dt DESC
      LIMIT 1
    ) doc -- это одно поле-запись
  FROM
    unnest(ARRAY[1,2,4,8,16,32,64]) id
)
SELECT
  id
, (doc).* -- разворачиваем в отдельные поля
FROM
  dc;

И, если вы используете версию PostgreSQL ниже 12-й, то все отлично - теперь индекс сканируется однократно (точнее, 7 раз вместо 28):

А вот начиная с PostgreSQL 12, планировщик "разворачивает" содержимое CTE, сводя все к тому же плану с 4 SubPlan. И чтобы он этого не делал, а наш "хак" продолжил работать, для CTE необходимо указать ключевое слово MATERIALIZED:

WITH dc AS MATERIALIZED (
  ...

Незаслуженно забываемый LATERAL

Глядя на все больше обрастающий "хаками" и становящийся менее читабельным код, невольно возникает вопрос - неужели нельзя как-то попроще?

И такой способ есть - это LATERAL-подзапрос, выполняющийся отдельно для каждой записи выборки, собранной на предыдущих шагах (в нашем случае это набор из 7 строк id):

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE; -- LEFT JOIN всегда должен иметь ON-condition

Обратите внимание на комбинацию LEFT JOIN LATERAL ... ON TRUE - это неизбежная плата, если мы хотим обязательно получить запись по каждому из 7 наших id, когда документов по конкретному покупателю нет совсем.

Вот что LATERAL животворящий делает!
Вот что LATERAL животворящий делает!

Такой запрос не только ищет запись однократно, но еще и в 1.5 раза быстрее из-за отсутствия необходимости формировать и читать CTE!

Один источник - разные условия

В предыдущем случае все SubPlan делали ровно одно и то же - искали одну и ту же запись по одинаковому условию. Но что если условия у нас окажутся разными?

Добавим к нашим документам пару полей - сотрудника-автора и сотрудника-исполнителя, которые указывают на таблицу с именами сотрудников:

ALTER TABLE doc
  ADD COLUMN emp_author integer
, ADD COLUMN emp_executor integer;
-- проставляем авторов/исполнителей
UPDATE
  doc
SET
  emp_author = (random() * 1e3)::integer
, emp_executor = (random() * 1e3)::integer;

CREATE TABLE employee(
  emp_id
    serial
      PRIMARY KEY
, emp_name
    varchar
);
-- генерируем "сотрудников"
INSERT INTO employee(
  emp_name
)
SELECT
  (
    SELECT
      string_agg(chr(((random() * 94) + 32)::integer), '')
    FROM
      generate_series(1, (random() * 16 + i % 16)::integer)
  )
FROM
  generate_series(1, 1e3) i;

А теперь давайте попробуем получить имена автора/исполнителя для тех документов, которые мы искали на первом шаге - мы ведь уже знаем, что нам поможет LATERAL:

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE
LEFT JOIN
  LATERAL( -- извлекаем автора
    SELECT
      emp_name emp_a
    FROM
      employee
    WHERE
      emp_id = doc.emp_author
    LIMIT 1
  ) emp_a
    ON TRUE
LEFT JOIN
  LATERAL( -- извлекаем исполнителя
    SELECT
      emp_name emp_e
    FROM
      employee
    WHERE
      emp_id = doc.emp_executor
    LIMIT 1
  ) emp_e
    ON TRUE;

Небольшое замечание: пожалуйста, не забывайте LIMIT 1 во вложенных запросах, когда вам необходима только одна запись, даже если уверены, что PostgreSQL "знает", что поиск идет по уникальному первичному ключу. Потому что иначе ничто не помешает ему выбрать вариант с Seq Scan по таблице.

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

Можно ли свести эти два прохода по индексу в один? Вполне! Используем для этого PIVOT с помощью условных агрегатов:

SELECT
  *
FROM
  unnest(ARRAY[1,2,4,8,16,32,64]) id
LEFT JOIN
  LATERAL(
    SELECT
      *
    FROM
      doc
    WHERE
      customer_id = id
    ORDER BY
      dt DESC
    LIMIT 1
  ) doc
    ON TRUE
LEFT JOIN
  LATERAL(
    SELECT -- min + FILTER = PIVOT
      min(emp_name) FILTER(WHERE emp_id = doc.emp_author) emp_a
    , min(emp_name) FILTER(WHERE emp_id = doc.emp_executor) emp_e
    FROM
      employee
    WHERE
      emp_id IN (doc.emp_author, doc.emp_executor) -- отбор сразу по обоим ключам
  ) emp
    ON TRUE;

Более подробно про разные нетривиальные варианты использования агрегатов можно почитать в статьях "SQL HowTo: 1000 и один способ агрегации" и "PostgreSQL Antipatterns: ударим словарем по тяжелому JOIN".

Один Index Scan сразу по набору ключей
Один Index Scan сразу по набору ключей

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

Знаете еще другие случаи "клонированных" узлов в планах - поделитесь в комментариях, а у меня на сегодня все.

Теги:
Хабы:
Всего голосов 16: ↑16 и ↓0+16
Комментарии21

Публикации

Информация

Сайт
sbis.ru
Дата регистрации
Дата основания
Численность
1 001–5 000 человек
Местоположение
Россия