Как найти медленные запросы в PostgreSQL: EXPLAIN ANALYZE
Практика3 минРедакция Innovialabs
Коротко
Сначала найдите самые дорогие запросы через pg_stat_statements или log_min_duration_statement, затем запустите для них EXPLAIN (ANALYZE, BUFFERS) и ищите узел, где тратится время: чаще всего это Seq Scan по большой таблице или лишняя сортировка, которые лечатся правильным индексом.
Короткий ответ
Работа с медленными запросами — это два шага:
- Найти, какие запросы реально нагружают базу:
pg_stat_statementsи лог медленных запросов. - Разобрать конкретный запрос:
EXPLAIN (ANALYZE, BUFFERS)покажет, какой узел плана тратит время, и станет понятно, что исправлять.
Шаг 1. Найти медленные запросы
pg_stat_statements собирает статистику по всем нормализованным запросам. Включите расширение в postgresql.conf, перезапустите сервер и создайте его в базе:
-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
Сортируйте по total_exec_time: запрос на 5 мс, который выполняется миллион раз, часто важнее редкого запроса на 3 секунды. В старых версиях PostgreSQL колонки называются total_time и mean_time.
Лог медленных запросов записывает каждый запрос дольше порога:
ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();
Модуль auto_explain может дополнительно писать в лог план таких запросов — удобно, когда запрос медленный только на production.
Шаг 2. Прочитать план
EXPLAIN показывает план и оценки, EXPLAIN ANALYZE реально выполняет запрос и добавляет фактическое время и число строк. Для UPDATE и DELETE оборачивайте его в BEGIN; ... ROLLBACK;.
План читается снизу вверх и изнутри наружу. Основные узлы:
| Узел | Что значит | Когда это проблема |
|---|---|---|
| Seq Scan | чтение всей таблицы | большая таблица, а нужно несколько строк |
| Index Scan | поиск по индексу, затем чтение строк из таблицы | редко проблема; плохо, если строк очень много |
| Index Only Scan | все данные берутся из индекса | хороший вариант |
| Bitmap Heap Scan | сначала собирает адреса строк по индексу, потом читает таблицу | нормально для средних выборок |
| Nested Loop | для каждой строки слева ищет совпадения справа | много строк слева и нет индекса справа |
| Hash Join | строит хеш-таблицу по одной стороне | хеш не помещается в work_mem |
| Sort | сортировка | external merge Disk — сортировка ушла на диск |
На что смотреть:
- actual time — где копится время;
- rows оценочные против фактических: расхождение на порядки значит, что статистика устарела или планировщик ошибается;
- Rows Removed by Filter — сколько строк прочитано зря;
- Buffers — сколько страниц прочитано из кеша и с диска.
Шаг 3. Ускоряем реальный запрос
Страница «Мои заказы» открывается медленно:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;
В плане видим Seq Scan on orders с большим Rows Removed by Filter, а над ним Sort. База читает всю таблицу, чтобы найти заказы одного клиента, а потом сортирует их.
Решение — составной индекс, который и фильтрует, и уже отсортирован:
CREATE INDEX CONCURRENTLY idx_orders_customer_created
ON orders (customer_id, created_at DESC);
ANALYZE orders;
Повторный EXPLAIN ANALYZE показывает Index Scan using idx_orders_customer_created под Limit, узел Sort исчез: база читает ровно 20 нужных строк по индексу.
Порядок колонок важен: сначала колонка из условия равенства (customer_id), затем колонка сортировки или диапазона.
Другие частые причины
- Устаревшая статистика — выполните
ANALYZE, проверьте работу autovacuum. - Функция над колонкой в
WHERE, напримерlower(email) = ..., не использует обычный индекс — нужен индекс по выражению. - Несовпадение типов при сравнении мешает использовать индекс.
- N+1 запросов из ORM — каждый быстрый, но их тысячи; видно в
pg_stat_statementsпо огромномуcalls. - Мало work_mem для конкретной сортировки или хеша — признак в плане: работа идёт на диске.