Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение 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):
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-подзапрос, выполняющийся отдельно для каждой записи выборки, собранной на предыдущих шагах (в нашем случае это набор из 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 животворящий делает!
Такой запрос не только ищет запись однократно, но еще и в 1.5 раза быстрее из-за отсутствия необходимости формировать и читать CTE!
Добавим к нашим документам пару полей - сотрудника-автора и сотрудника-исполнителя, которые указывают на таблицу с именами сотрудников:
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;
Можно ли свести эти два прохода по индексу в один? Вполне! Используем для этого 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;
Один Index Scan сразу по набору ключей
Впрочем, этот способ стоит использовать с осторожностью, поскольку иногда сама агрегация может стоить дороже остальных вычислений.
Знаете еще другие случаи "клонированных" узлов в планах - поделитесь в комментариях, а у меня на сегодня все.
habr.com
Пролистываешь выше - и таки-да, вот он рядом - но почему он там оказался, и как выйти из Матрицы самому и помочь коллегам?
Одна запись - несколько полей
Возьмем простую и достаточно типовую бизнес-задачу - показать последний документ по каждому из некоторого набора покупателей: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 животворящий делает!
Такой запрос не только ищет запись однократно, но еще и в 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;
Все нормально, но пара узлов в плане оказывается подозрительно похожа друг на друга:
Можно ли свести эти два прохода по индексу в один? Вполне! Используем для этого 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;
Один Index Scan сразу по набору ключей
Впрочем, этот способ стоит использовать с осторожностью, поскольку иногда сама агрегация может стоить дороже остальных вычислений.
Знаете еще другие случаи "клонированных" узлов в планах - поделитесь в комментариях, а у меня на сегодня все.
PostgreSQL Antipatterns: «где-то я тебя уже видел...»
Иногда при анализе производительности запроса на предмет "куда ушло все время" возникает стойкое ощущение deja vu, что вот ровно этот же кусок плана ты уже где-то раньше видел... Пролистываешь выше -...
habr.com