v(i)b(e)c(o)d(e)4u.com
Назад на форум vbcd4u.com
🔥 Нейросети2026-11-17T10:01:00.000Z▲ 0 upvotes

EXPLAIN ANALYZE в PostgreSQL: как читать план и какие индексы реально нужны

@murdasoftРедакция vbcd4u · MurdaSoft
Открыть все обсуждения →

С чего начинать: не с плана, а со списка запросов

Индексы добавляют под конкретные запросы, а не «на все колонки из WHERE». Сначала найдите, какие запросы действительно дорогие. Расширение pg_stat_statements собирает статистику по нормализованным запросам:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements; -- требует shared_preload_libraries

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 1) AS mean_ms,
       left(query, 120) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 15;

Сортировка по total_exec_time показывает, где база тратит время суммарно. Запрос на 3 мс, вызываемый миллион раз, часто важнее отчёта на 2 секунды раз в день.

Как снять план

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total, created_at
FROM orders
WHERE user_id = 4815 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

ANALYZE реально выполняет запрос. Для UPDATE и DELETE оборачивайте его в транзакцию с откатом:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM sessions WHERE expires_at < now();
ROLLBACK;

BUFFERS показывает, сколько страниц прочитано из кэша (shared hit) и с диска (read). В PostgreSQL 18 эта информация выводится вместе с ANALYZE автоматически, в более ранних версиях параметр нужно указывать явно.

Как читать узлы

План — дерево. Читается изнутри наружу: самые вложенные узлы выполняются первыми. У каждого узла две группы цифр:

Limit  (cost=0.00..1843.20 rows=20 width=24) (actual time=0.041..96.512 rows=20 loops=1)
  ->  Sort  (cost=... rows=1180 ...) (actual time=96.501..96.506 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on orders  (cost=0.00..41250.00 rows=1180 width=24)
                                (actual time=0.030..95.880 rows=1342 loops=1)
              Filter: ((user_id = 4815) AND (status = 'paid'::text))
              Rows Removed by Filter: 1998658

Это иллюстративный план, но паттерн в нём типичный:

  • `cost` — оценка планировщика в условных единицах. Сравнивать её между разными запросами бессмысленно.
  • `actual time=A..B` — миллисекунды до первой строки и до последней. Время родителя включает время детей.
  • `rows` в оценке и в факте. Расхождение на порядки — главный признак проблемы: планировщик выбрал план под неверное количество строк.
  • `loops` — сколько раз узел выполнялся. Реальное время и строки нужно умножать на loops. Внутренний узел Nested Loop с loops=50000 и временем 0,02 мс — это секунда.
  • `Rows Removed by Filter` — строки, прочитанные зря. Здесь два миллиона строк просмотрено ради 1342 нужных.

Какой индекс нужен этому запросу

Запрос фильтрует по равенству user_id и status и сортирует по created_at. Порядок колонок в составном индексе: сначала колонки с равенством, затем колонка сортировки.

CREATE INDEX CONCURRENTLY orders_user_status_created_idx
  ON orders (user_id, status, created_at DESC);

С таким индексом план превращается в Index Scan с Limit: база читает ровно 20 записей по индексу в нужном порядке и не сортирует ничего. CONCURRENTLY не блокирует запись в таблицу на время построения, но не работает внутри транзакции и при сбое оставляет невалидный индекс, который нужно удалить и создать заново.

Если почти все запросы смотрят на одно значение статуса, частичный индекс меньше и дешевле в обслуживании:

CREATE INDEX CONCURRENTLY orders_pending_idx
  ON orders (created_at) WHERE status = 'pending';

Он будет использован, только если условие запроса включает status = 'pending' буквально.

Узлы, на которые стоит смотреть

  • `Sort Method: external merge Disk: ...` — сортировка не поместилась в work_mem и ушла на диск. Лечится индексом с нужным порядком или увеличением work_mem для конкретной сессии, а не глобально.
  • `Bitmap Heap Scan` с `Recheck Cond` и `Heap Blocks: lossy` — битовая карта не поместилась в память и стала постраничной.
  • `Index Only Scan` с большим `Heap Fetches` — индекс покрывает запрос, но карта видимости устарела, и база всё равно ходит в таблицу. Помогает VACUUM (или настройка автовакуума для этой таблицы).
  • `Hash Join` с `Batches: 8` — хеш-таблица не уместилась в память.
  • Оценка `rows=1`, а факт — тысячи. Обновите статистику ANALYZE orders;. Если колонки коррелируют (город и страна), помогает расширенная статистика: CREATE STATISTICS ... (dependencies) ON city, country FROM addresses;.

Почему индекс есть, а не используется

  1. 1.Функция или приведение типа на колонке: WHERE lower(email) = $1 не использует индекс по email. Нужен индекс по выражению (lower(email)).
  2. 2.LIKE '%abc' с ведущим процентом. B-tree тут не поможет, нужен pg_trgm с GIN.
  3. 3.Условие выбирает большую долю таблицы. Последовательное чтение тогда действительно дешевле, и планировщик прав.
  4. 4.Разные типы: колонка bigint, параметр передан как numeric.
  5. 5.Маленькая таблица. Для пары тысяч строк Seq Scan нормален.

Лишние индексы тоже стоят денег

Каждый индекс замедляет вставку и обновление и занимает место. Кандидаты на удаление:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Статистика локальна для сервера: индекс, который не нужен на primary, может обслуживать отчёты на реплике. И не удаляйте индексы, обеспечивающие UNIQUE или первичный ключ.

Когда EXPLAIN не покажет проблему

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

Обсуждение (0)

Ещё на vbcd4u.com