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

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

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