Comments 36
Исходный запрос простейший и если его PG выполняет 28 секунд при наличии двух исходных индексов, это как минимум странно.
Хотелось бы посмотреть план выполнения исходного запроса и точное кол-во записей в таблицах Users и Orders.
Немного смутил GROUP BY по u.name, это некрасиво, надеюсь PG такое оптимизирует на автомате, понимая что группировки по u.id достаточно. Можно было не тащить u.name в запрос, а потом уже подставить по справочнику (внешней логикой).
То, что status хранится строкой а не ID на справочник этих статусов - тоже попахивает )
>>т.к. u.name можно даже не писать в GROUP BY , ид достаточно
MS SQL Server требует писать в group by всё что есть в select'e без агрегатных функций (даже если это 100500 полей одной таблицы). Если postgres позволяет не писать, это удобно конечно
Постгрес тоже требует
Вообще-то этого требует SQL-стандарт. Остальное - лишь следствие следования этому стандарту различными СУБД.
А у меня работает такой запрос:
SELECT e.empId, e.name, sum(o.amount)
FROM EMPLOYEE e
INNER JOIN ORDERS o ON e.empId = o.empId
GROUP BY e.empId
Allow non-GROUP BY columns in the query target list when the primary key is specified in the GROUP BY clause (Peter Eisentraut)
The SQL standard allows this behavior, and because of the primary key, the result is unambiguous.
https://www.postgresql.org/docs/release/9.1.0/
Еще с 9.1 требует не всегда. В какой-то из более поздних версий определение доступных к возврату столбцов было заменено на что-то типа "однозначно функционально вычислимые".
Пробовали в первую часть cte добавить еще фильтр по дате и статусу, тогда бы агрегация далее происходила бы по еще меньшему набору данных. Пробовали? Какой был бы результат. Хотелось бы подробнее про то как вы пришли именно к такому решению по оптимизации запроса которое вы здесь описали. С разбором планов запроса например.
Увидел даты. Первый вопрос возник, а таблица партицирована? Даже если не на этапе дизайна, а в процессе эксплуатации.
CREATEINDEX idx_orders_coveringONorders(user_id, created_at, status)WHEREstatus = 'completed';
Имеет ли смысл в индекс добавлять статус, если он всегда completed? Я бы сказал, что нет, но может какая хитрость есть.
Почему нет? Индекс хранит ссылки на записи со статусом completed. Если в запросе есть where status = completed, то планировщик использует индекс. Если бы был какой нибудь where status = created, то индекс не использовался бы. Единственный момент - сколько в БД % записей с таким статусом. Если он довольно высок (заказ в конце как правило или completed, или какой нибудь другой конечный статус), то тогда индекс будет просто неэффективен.
Ну и частичный индекс занимает меньше места чем полный, и соответственно чуть быстрее ищет.
Не уверен, что понял ответ. Поэтому поясню вопрос :)
Я про то в составном индексе есть поле статус и в ограничении есть статус. Т.е. в поле индекса статус всегда будет один и тот же. Поэтом возник вопрос, а зачем?
Кажется такой индекс будет работать так же и меньше места занимать.
CREATEINDEX idx_orders_coveringONorders(user_id, created_at)WHEREstatus = 'completed';
Может, эта мысль покажется крамольной, но если этот запрос так важен для вас, что "Сайт не грузится!" и "Отчёты зависли!", почему бы не сделать отдельную физическую таблицу для тоталзов, и обновлять её по мере добавления заказов, вместо того, чтобы постоянно агрегировать по 50 миллионам записей?
Всегда было интересно, почему на SQL так часто пишут is_active = true, хотя логично же просто is_active, как в любом нормальном языке программирования.
Вам явно требуется архитектор баз данных... У вас прям все минусы, которые мог заложить программист в решение. И нет видимо вообще понимания как работают запросы
Вероятно потому, что в sql булева логика троична, в том смысле что в ней ещё замешаны значения NULL, а не только true и false. Например, обе проверки field=true и field=false будут ложны при значении поля равном NULL
Причина еще может крыться в привычке писать как в моем примере ниже:JOIN users u ON (u.id, u.is_active) = (o.user_id, TRUE)
vs.
JOIN users u ON u.id = o.user_id AND u.is_active
Господи, какая чушь начала заполнять хабр, это просто жесть
Отдельная CTE для WITH potential_users мне кажется не обязателен - постгря бы сама исходя из фильтра подставила бы данные из нужного индекса
Group by в рантайме это уже тех долг
Попадал на такие истории не раз. Вы на первом шагу. Дальше разбиениеина на партиции по датам транзакций, затем таблица с уже расчитанными агрегатами, потом перенос таких операций в аналитическую базу данных. Где-то в середине можно добавить материализированные представления.
Ещё может помочь обрезание транщакционных данных. Например, не работали с клиентом 3 года - выбрасывает все транзакции по нему. Но это не всегда применимо.
Тут, конечно, без плана исходного запроса добрая половина оптимизаций выглядит сделанной непонятно зачем, но попробуем зайти на задачу с точки зрения алгоритмов и прикладной логики.
Сформулируем условие: "Вывести TOP-50 активных (is_active = true) пользователей, у кого наберется хотя бы по 10K оборота, лидирующих по сумме (amount) отгрузок (status = 'completed') заказов, созданных (created_at) с 01.01.2023."
Из базовой прикладной логики можно предположить, что большинство пользователей, отгружавших на диапазоне последних 3 лет, будут активными и по сей день (и чем этот интервал меньше, тем больше вероятность, что все отгружавшие - активны).
По этой причине эффективнее сразу "схлопнуть" все заказы интервала по пользователю.
Потом подтягиваем активных пользователей к полученной выборке и не забываем посортировать снова после JOIN.
Как-то примерно так:
SELECT
u.id
, u.name
, o.order_count
, o.total_amount
FROM
(
SELECT
user_id
, count(id) order_count
, sum(amount) total_amount
FROM
orders
WHERE
status = 'completed' AND
created_at >= '2023-01-01'
GROUP BY
user_id
HAVING
total_amount > 10000
ORDER BY
total_amount DESC
LIMIT 50
) o
JOIN
users u
ON (u.id, u.is_active) = (o.user_id, TRUE)
ORDER BY
total_amount DESC;И только после этого имеет смысл прикидывать, какие из индексов нам реально пригодятся тут:
CREATE INDEX ON orders(created_at) WHERE status = 'completed';
CREATE INDEX ON users(id) WHERE is_active = true;Понятно, что индекс orders(user_id, created_at) тоже может быть полезен, но или не для этой задачи, или если разных пользователей у нас всего десяток-полтора.
На практике это точка, где:
Индексы перестают помещаться в оперативную память
Планировщик запросов начинает выбирать неоптимальные пути
Не могли бы вы подтвердить фактическими данными, что именно на 10кк записей произошли указанные "неприятности". Особенно в части плана выполнения запроса при 9.9кк и 10.1кк записей.
И да, при количестве записей в таблицах в 10кк - вот не верю я, что какие-то два вшивых индекса по одному полю каждый (по user_id и created_at) зажрали 4 с хвостом гига на диске. НЕ ВЕРЮ!!!
Заодно поясните - что, все 127 раз в день запрос выполнялся с одними и теми же значениями всех параметров-литералов? Но если так - а нафига вы вообще его выполняете, если все значения параметров известны и фиксированы? Тем более что запрос на чистые TOP50, на пагинацию даже не намекает, а в таком случае 99% за то, что даже ошибочное включение в этот список 51-го и пропуск 50-го ну вообще ни на что не влияет - то есть достаточно выполнить однократный предрасчёт, а всем 127 запросившим предоставлять готовые результаты, пусть и на полчаса устаревшие, но зато менее чем за миллисекунду.
PS. Я вообще вот не верю подобным стопервым рассказкам. Почти убеждён, что инцидент придуман, просто высосан из пальца для написания статьи, и не имел места на практике. Уж больно он невероятный.
Сделать поле orders_sum у users и триггер на изменения в orders, обновляющие orders_sum?
Удивительно, olap-запрос после какого-то порога начинает убивать oltp-базу. Никогда такого не было, и вот опять...
Эти местные прикладывания подорожника через какое-то время снова перестанут работать.
И да, 256 MB на сессию для бд, которая обслуживает внешний сайт - ну такое
Ммм, а в первоначальном варианте статус вообще не индексирован?
Статья про оптимизацию плана без единого плана)
Улыбнуло :
-- Включаем детальный мониторингALTERSYSTEMSETshared_preload_libraries = 'pg_stat_statements';SELECTpg_reload_conf();
AI и/или лень хоть как то проверить было ?
Добрый день! Была похожая ситуация, БД с количеством записей порядка 100+ миллионов. Контект базы: id скважины, время и различные показатели разработки. При агрегации данных для одной скважины занимало 2 секунды, к примеру для небольшого местоположения с 120 скважин, весь запрос увеличился на порядок. Также как автор применил составной индекс и БД взлетела (+200%). Основная сложность была определить источник проблем.
Автору спасибо за статью 🤝
PostgreSQL против 10 миллионов записей: оптимизация запросов, которая спасла наш проект