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

Иногда при анализе производительности запроса на предмет «куда ушло все время» возникает стойкое ощущение 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]

Троекратное Троекратное «ура!» разработчику от сервераa1e185a3e14a68997c40b8d79c73d011.jpeg

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

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

Давайте вернем из вложенного запроса сразу всю запись (только 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):

image-loader.svg23758082865b2946e94c36fd0bc818a7.jpeg

Экранируем запись с помощью 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):

image-loader.svg

А вот начиная с 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!

image-loader.svg

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

В предыдущем случае все 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 по таблице.

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

image-loader.svg

Можно ли свести эти два прохода по индексу в один? Вполне! Используем для этого 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 сразу по набору ключей

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

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

image-loader.svg

© Habrahabr.ru