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

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

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

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

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

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, когда документов по конкретному покупателю нет совсем.

Такой запрос не только ищет запись однократно, но еще и в 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».

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

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