Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение 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".
Впрочем, этот способ стоит использовать с осторожностью, поскольку иногда сама агрегация может стоить дороже остальных вычислений.
Знаете еще другие случаи "клонированных" узлов в планах - поделитесь в комментариях, а у меня на сегодня все.
Комментарии (21)
VanKrock
24.08.2021 20:39Если вы используете в запросе с
with
поле-запись, то почему бы её не использовать в первоначальном запросе?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;
Kilor Автор
24.08.2021 20:49Поскольку драйверы поголовно не понимают тип record, необходимо развернуть запись в столбцы (SELECT ...).* - а тогда это будет ровно первый переписанный вариант.
VanKrock
24.08.2021 20:59Можно обернуть в ещё один select
select s.*from (
select (SELECT doc FROM doc WHERE customer_id = d.customer_id ORDER BY dt DESC LIMIT 1)
from doc d
where d.customer_id in (1,2,4,8,16,32,64)) s
сделал in чтобы убрать пустые строки
Kilor Автор
24.08.2021 21:03Вот как раз убирать пустые строки - противоречит задаче. Ну и двойной поиск по doc вернет на первом уровне все документы по этим клиентам - то есть далеко не 7.
VanKrock
24.08.2021 22:17+1Так как есть первичный ключ, понял, что можно обойтись без record. Вообще можно использовать стандартный вариант решения такой задачи
select d.customer_id, d.doc_id, d.dt, d.sum, author.emp_name author_name, executor.emp_name executor_name from doc d, employee author, employee executor where d.customer_id in (1,2,4,8,16,32,64) and d.dt = (select max(dt) from doc where doc_id = d.doc_id) and d.emp_author = author.emp_id and d.emp_executor = executor.emp_id
Danik-ik
24.08.2021 21:28+1Странно, но мне для выборки последних строк по каждому клиенту ни один из Ваших вариантов в голову бы не пришёл. Я бы через оконные функции действовал:
select * from ( select *, row_number() over (partition by d.customer_id order by dt desc) as rn from ... ) preselect where rn=1
Поля, правда, перечислил бы (Извините, пишу с телефона). Интересно, как оно в смысле плана именно в postgres (до сих пор только с ms работал)?
Kilor Автор
24.08.2021 21:43Навскидку - чтобы отобрать только "первые" пронумерованные окном записи, их надо как-то отобрать "все", что не супербыстро.
mr_stepik
24.08.2021 23:28Присоединюсь к автору комментария: через оконные функции получаются самые эффективные планы:
with a as ( select doc_id, row_number() over (partition by customer_id order by dt desc) rn from doc where customer_id in (1,2,4,8,16,32,64)) select doc.* from a join doc on doc.doc_id = a.doc_id where rn = 1;
тут для вычисления последней записи он просканирует индекс, а уже саму запись прочитает из кучи
Kilor Автор
25.08.2021 09:32+1У этого запроса все-таки несколько существенных проблем (план):
он обращается к doc дважды (точнее, Bitmap + N x Index Scan)
он вычитывает все записи по этим customer_id
он не возвращает строку с id, для которого вообще нет документов
он в 1.5-2.5 раза медленнее, чем LATERAL
mr_stepik
17.09.2021 15:26Но вообще, спасибо!
Начал активно пользоваться lateral. это действительно сильно производительнее чем окна.
nin-jin
25.08.2021 09:59Вам не кажется, что лучше сделать запрос последнего документа для покупателя отдельным? Это позволит легко такой запрос закешировать, причём не только на сервере, но и даже на клиенте. Сбрасывать такой кеш не сложно при добавлении документов.
Kilor Автор
25.08.2021 10:08+1Отдельным от чего - от СУБД и SQL-запроса по клиентам?
Тут мы уже выходим за рамки БД и ступаем на зыбкую почву архитектурных модификаций системы - отдельный кэш и его поддержка в актуальном состоянии отдельная непростая тема. Зато в качестве альтернативы можно пробовать прямо в триггере создания/модификации "последнего" документа записывать его ID прямо в карточку клиента.
Но эффективность этого способа сильно зависит от контента в записи клиента - если там лежит огромный JSON с какими-то атрибутами, его постоянное перезаписывание и рост bloat могут "съесть" весь эффект от более быстрого запроса при чтении.
nin-jin
25.08.2021 10:55Поэтому я и говорю про кеш, а не денормализацию. В этом случае субд (или обёртке вокруг неё) достаточно кинуть событие, что такой-то документ изменился. А владелец кеша уже решает насколько ему это событие релевантно и сбрасывает кеш, если подозревает его неактуальность.
Kilor Автор
25.08.2021 11:02+1А дальше мы занимаемся проблемами этого кэша:
поиска недолетевшего события
организацией очередей событий
некорректно отработавшего обработчика события
периодической проверки логической целостности его данных
необходимости пересинхронизации при потере связи / проблем на узле
И не всегда оно того стоит.
nin-jin
25.08.2021 12:22Зачем? Для всего этого уже есть готовые решения. Зато мы получаем качественно иной уровень сервиса - данные обновляются в реальном времени, а не так, что смотришь на данные, а они уже 10 минут как не актуальны.
Kilor Автор
25.08.2021 12:29+1Да, решения есть - но их много и разных, что сильно усложняет архитектуру и ее сопровождение. А на другой чаше весов - "все в БД", и нельзя однозначно сказать, что это всегда хуже
.
VanKrock
25.08.2021 16:20На самом деле получение последней версии документов для массива пользователей, это очень частая задача в том или ином виде. В принципе, это постоянная задача если вы делаете версионность чего-либо
remzalp
Скажите, а чем ТАКОЙ запрос хуже???
SELECT customer_id, doc_id, dt, sum FROM doc WHERE customer_id IN (1,2,4,8,16,32,64) LIMIT 7
VanKrock
Тут нужен последний документ по dt для каждого пользователя, у вас же запрос вернёт все документы для пользователя
Kilor Автор
Он вернет какие-то 7 из документов этих покупателей. Ни первые, ни последние, ни по каждому - просто "какие-то".