Медленный запрос почти никогда не медленный «сам по себе» — у него есть план, и план видно. EXPLAIN показывает, как PostgreSQL собирается выполнять запрос, а EXPLAIN (ANALYZE, BUFFERS) — как он выполнил его на самом деле, со временем, числом строк и обращениями к диску. Умение читать этот вывод превращает оптимизацию из гадания в инженерию: видно, где планировщик ошибся в оценке, где не хватает индекса и где индекс есть, но запрос написан так, что им нельзя воспользоваться.
Это четвёртая статья серии «PostgreSQL в проде». Она опирается на предыдущую про VACUUM и статистику: планировщик принимает решения по статистике, которую собирает тот же autovacuum, и без свежей статистики хорошего плана не будет. Фокус статьи — именно чтение плана: не «какой индекс выбрать под задачу» (это отдельная серия «Индексы в базах данных»), а как по выводу EXPLAIN понять, что происходит, и где узкое место. Все числа ниже — из живого стенда postgres-ops/explain в публичном репозитории digital-cookbook: PostgreSQL 18.4, схема интернет-магазина (orders 2 млн строк, order_items 6 млн, products 100 тыс).
В статье
- Как читать EXPLAIN
- Способы доступа
- Типы индексов в плане
- Статистика и планировщик
- Антипаттерны: индекс есть, но не работает
- pg_stat_statements: поиск тяжёлых запросов
Как читать EXPLAIN
EXPLAIN без выполнения показывает только оценку планировщика — предсказание стоимости и числа строк. EXPLAIN (ANALYZE, BUFFERS) реально выполняет запрос и добавляет факт: время, фактические строки, обращения к буферам. Разбор одного узла:
$ docker compose exec -T postgres psql -U postgres -d shopdemo -c \
"EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
WHERE status = 'paid' AND created_at > now() - interval '30 days'"
# (сведено к узлу Seq Scan)
Seq Scan on orders (cost=0.00..56959.00 rows=31324 width=36)
(actual time=221.581..231.097 rows=30715.00 loops=1)
Filter: ((status = 'paid') AND (created_at > (now() - '30 days')))
Rows Removed by Filter: 1969285
Buffers: shared read=16959
Что здесь читать. cost=0.00..56959.00 — оценка стоимости в абстрактных единицах (старт..итог); rows=31324 — оценка числа строк, width=36 — средняя ширина строки в байтах. Строка actual — факт: rows=30715 против оценки 31324 (близко — статистика свежая). loops=1 — сколько раз выполнялся узел; actual time и rows в этой строке — средние на одно выполнение. Здесь это одно и то же, но у внутренней стороны Nested Loop бывает loops=20000, и тогда реальные строки и время — это показанное значение, умноженное на loops. Rows Removed by Filter: 1969285 — сколько строк узел прочитал и отбросил (весь Seq Scan прошёл по 2 млн). Buffers: shared read=16959 — ни одной из этих страниц не было в shared buffers PostgreSQL, их запросили у ОС (повторный прогон дал бы shared hit — из буферного кеша). Это не обязательно физическое чтение диска: страница могла прийти из page cache ОС, и EXPLAIN этого не различает — реальное время ввода-вывода покажет только track_io_timing (строка I/O Timings).
Три красных флага при чтении плана: крупное расхождение rows (оценка) и actual rows — статистика врёт (см. ниже); высокие shared read — запрос много читает мимо shared buffers (а при холодном page cache — с диска); большой Rows Removed by Filter — читаем много, отдаём мало, просится индекс.
Способы доступа
Как узел добирается до строк — определяется селективностью предиката. На стенде, управляя ей, получаем в живую четыре разных узла.
Seq Scan — полный проход. Низкоселективный предикат (status = 'paid' — ~40% строк, 748 тыс из 2 млн): индекс не помог бы, дешевле читать таблицу подряд. 191 мс.
Index Scan — точечный доступ. customer_id = 12345 (47 строк): в плане Index Cond, предикат ушёл в индекс. 0.3 мс. С 191 мс Seq Scan выше это сравнивать напрямую нельзя: запрос другой, и строк он отдаёт в ~16 000 раз меньше (47 против 748 тыс). Цифры показывают, какой узел выбирает планировщик при какой селективности, а не ускорение одного запроса индексом — такие пары «тот же запрос с индексом и без» есть ниже, в btree и антипаттернах.
Bitmap Heap Scan + Bitmap Index Scan — золотая середина. Диапазон customer_id BETWEEN 10000 AND 10500 (20 030 строк, разбросаны по heap): индекс строит битовую карту нужных страниц, heap читается по физическому порядку. В плане — Recheck Cond и Heap Blocks: exact=11805.
Index Only Scan — покрывающий запрос. SELECT customer_id WHERE customer_id = 12345: всё нужное есть в индексе, в heap не ходим — Heap Fetches: 0.
Главный вывод раздела: Seq Scan — не всегда «плохо». На этом стенде для 40% строк планировщик выбрал его — и правильно: строки разбросаны по всей таблице, индекс заставил бы читать почти все те же страницы, да ещё вразнобой. Где проходит граница, зависит от физического порядка строк, ширины строки, кеша и random_page_cost, универсального процента нет; но заставлять планировщик лезть в индекс при такой селективности — обычно вредить. «Плохо» — это Seq Scan там, где строк мало и индекс был бы на порядки быстрее (как в антипаттернах ниже).
Типы индексов в плане
Разные индексы по-разному выглядят в плане — и научиться их узнавать полезнее, чем помнить теорию. (Когда какой выбирать — разбирает серия про индексы; здесь — как читать их узел.)
btree — самый частый. Хранит значения отсортированно, поэтому даёт не только поиск, но и бесплатный порядок. ORDER BY created_at DESC LIMIT 10 с btree — это Index Scan Backward, 0.014 мс, без узла Sort. Тот же запрос с отключённым индексом — Sort (top-N heapsort) над Seq Scan всех 2 млн строк, 300 мс. Разница — почти 20 000×, и складывается она из двух вещей сразу: индекс отдаёт строки уже упорядоченными, поэтому узел Sort не нужен, и благодаря этому Limit останавливает чтение после десяти строк. Без индекса прочитать придётся все два миллиона, а затем отобрать из них десять — основная часть времени уходит именно на чтение.
GiN — инвертированный индекс для составных значений (полнотекст, jsonb, массивы). В плане — Bitmap Index Scan плюс Recheck Cond над Bitmap Heap Scan. Полнотекстовый search @@ to_tsquery('russian', 'ноутбук & игровой') нашёл 1535 строк; любопытная деталь плана — в индексе лежит лексема 'игров', а не слово «игровой»: это стемминг русской конфигурации. К recheck стемминг отношения не имеет: строка Recheck Cond печатается у любого Bitmap Heap Scan и означает условие, которое будет перепроверено по строке heap, если понадобится. Понадобится оно, когда битовая карта не уместилась в work_mem и стала постраничной (lossy), либо когда сам индекс не может ответить точно — для полнотекстового GiN это запросы с весами ('игров':A), потому что веса в индексе не хранятся. Обычный & без весов GiN отвечает точно. Тот же тип узла у jsonb-containment attrs @> '{"brand":"acme"}'.
GiST — дерево для «перекрытий» и близости: диапазоны, гео, nearest-neighbour. Пересечение периодов period && tstzrange(now(), now()+'1 day') — Bitmap Index Scan по gist, 2508 активных акций из 100 тыс. Отдельно показателен kNN: ORDER BY price <-> 500 LIMIT 5 — это Index Scan с Order By в узле (а не Sort!): GiST обходит дерево от ближайшего, выдавая строки сразу по возрастанию расстояния. 0.146 мс.
BRIN — мин/макс на диапазон блоков; крошечный ценой грубости. Эффективен, когда значения колонки коррелируют с физическим размещением строк: тогда диапазоны блоков почти не перекрываются и большую часть таблицы можно отбросить. Без такой корреляции индекс формально работает, но отсекает мало — почти каждый диапазон блоков подходит под условие. На orders.created_at (корреляция ровно 1, append-only по времени) BRIN даёт Bitmap Index Scan с характерными Heap Blocks: lossy и Rows Removed by Index Recheck: 3270 — грубость видна прямо в плане. Зато размер: 24 КБ против 43 МБ у btree на тех же 2 млн строк — в ~1800 раз меньше.
Статистика и планировщик
Планировщик выбирает план по статистике, которую собирает ANALYZE (и автоматически — autovacuum): гистограммы, n_distinct, списки частых значений (MCV). Устаревшая статистика ломает оценку — и это видно в самом плане. На стенде таблица, где доля значения active резко выросла с ~1% до ~50%, но ANALYZE после этого не запускали:
$ ./run.sh sql/07-stats-stale.sql
# (сведено к rows и actual rows узла Index Only Scan: фаза 2 и после ANALYZE)
без ANALYZE: rows=5546 (оценка) против actual rows=303099 ← расхождение ×55
после ANALYZE: rows=302480 (оценка) против actual rows=303099 ← совпало
Планировщик масштабирует общее число строк по размеру файла, но долю строк под предикат берёт из последнего ANALYZE. Если данные перекосились, оценка доли врёт в десятки раз, пока не переанализируешь, — а на реальном запросе такой misestimate ведёт к выбору неверного соединения или скана. Именно поэтому после массовой загрузки данных полезно звать ANALYZE вручную, не дожидаясь autovacuum. Для коррелированных колонок, где оценка «в лоб» перемножает вероятности неверно, существуют extended statistics (CREATE STATISTICS). А default_statistics_target регулирует, насколько подробную гистограмму строить (точнее оценка ценой более долгого ANALYZE).
Антипаттерны: индекс есть, но не работает
Самый частый класс проблем — индекс существует, но запрос написан так, что им нельзя воспользоваться. Диагноз ставится по плану: ищем Seq Scan/Filter там, где ждали Index Cond.
Важное правило для всех примеров ниже: исправленный запрос обязан возвращать те же строки. Ускорение ценой потерянных результатов — не оптимизация, а новая ошибка; сверяйте результат до и после. Совпавший count(*) для этого недостаточен — одинаковое число строк не означает одинаковые строки. Строгая проверка — обе разности с учётом дублей, (A EXCEPT ALL B) и (B EXCEPT ALL A): обе должны быть пустыми.
Функция по колонке. lower(name) = 'ivan' — btree по name не подходит, Seq Scan + Filter, 5.1 мс. Рядом стенд показывает name = 'ivan' — Bitmap Index Scan, 0.33 мс: пара наглядно показывает, как функция на колонке уводит предикат в Filter, но заменой она не является. lower(name) = 'ivan' находит и ivan, и Ivan, а name = 'ivan' — только строчное написание, и проверка это подтверждает: из 8384 строк теряются 5591. Регистронезависимый поиск лечат не переписыванием, а expression-индексом под тот же запрос: CREATE INDEX ON customers (lower(name)) — и lower(name) = 'ivan' идёт через Index Scan с Index Cond, 0.73 мс, все 8384 строки на месте.
Каст по колонке. Здесь переписать можно — потому что форма эквивалентна. created_at::date = current_date даёт Seq Scan с Filter по всем 2 млн строк, 96 мс, а полуинтервал created_at >= current_date AND created_at < current_date + 1 — Index Only Scan с Index Cond, 0.14 мс, ~700×. Строки те же: обе разности пусты.
Ведущий wildcard. LIKE '%van%' — Seq Scan, btree бессилен без опоры слева, 1.6 мс. Стенд показывает рядом LIKE 'Iva%' с индексом text_pattern_ops — Bitmap Index Scan, 0.56 мс, но это опять другой запрос: префиксный поиск не находит строки ivan, а их 2793. Он годится, только если по смыслу задачи нужен именно префикс. Для того же поиска подстроки индекс нужен триграммный (CREATE EXTENSION pg_trgm и CREATE INDEX ... USING gin (name gin_trgm_ops)): с ним тот же LIKE '%van%' идёт через Bitmap Index Scan, 0.88 мс, а результат не меняется. На 50 тыс. строк и 17% совпадений выигрыш скромный, ~2×; он растёт с размером таблицы и селективностью подстроки (и подстрока должна быть не короче трёх символов).
Проверки эквивалентности (обе разности EXCEPT ALL для каждой пары) стенд выполняет в том же прогоне ./run.sh sql/08-antipatterns.sql, сразу после планов.
OR — не зло само по себе. Если оба операнда селективны и под индексами, планировщик строит BitmapOr из двух Bitmap Index Scan — 0.05 мс. А вот один неиндексируемый операнд роняет весь OR в Seq Scan: customer_id = 12345 OR status = 'cancelled' (status без индекса) — 62 мс. Достаточно одной плохой ветви. (Эти два запроса тоже не эквивалентны друг другу — они показывают, как план зависит от индексов на ветвях, а не предлагают замену.)
Отдельно стоит N+1 — это не проблема плана (каждый запрос быстрый), а проблема их количества; лечится на стороне приложения (JOIN/IN вместо цикла), см. клиентскую практику.
pg_stat_statements: поиск тяжёлых запросов
EXPLAIN отвечает на вопрос «почему медленный этот запрос». Но сначала нужно найти, какие запросы вообще съедают время в проде, — и это делает pg_stat_statements. Расширение нормализует запросы (константы → $1) и копит по ним счётчики. Топ по суммарному времени:
$ ./run.sh sql/09-pgss-heavy.sql
# (сведено к трём строкам топа, без колонки rows)
calls | total_ms | mean_ms | query
-------+----------+---------+--------------------------------------------
1 | 842.6 | 842.64 | SELECT o.status, count(*) FROM orders o JOIN order_items oi ...
1 | 381.0 | 381.02 | SELECT status, count(*), avg(total) FROM orders GROUP BY status
3 | 1.3 | 0.43 | SELECT * FROM orders WHERE customer_id = $1
Два момента. Сортировка по total_exec_time (суммарно), а не по mean: запрос, дешёвый за вызов, но вызываемый миллион раз, съедает больше, чем редкий тяжёлый, — и именно он кандидат на оптимизацию. И нормализация: три запроса customer_id = <разные числа> слились в одну строку customer_id = $1 с calls = 3 — статистика агрегируется по форме запроса.
Ту же диагностику удобно снимать из кода — для мониторинга и алертов. Небольшая Go-утилита на pgx читает топ pg_stat_statements обычным SELECT:
rows, _ := conn.Query(ctx, `
SELECT calls, total_exec_time, mean_exec_time, left(query,70)
FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10`)
В проде это опрашивается периодически и уходит в наблюдаемость (khorost_tech.* через OpenTelemetry/Prometheus): рост mean_ms у известного запроса — сигнал, что план сменился или данные выросли, и пора смотреть EXPLAIN. А чтобы разбирать конкретный план не глазами по тексту, а визуально, удобен разборщик explain.tensor.ru — он подсвечивает самые дорогие узлы и места, где факт расходится с оценкой.
Эти четыре статьи выстраивают базовый инструментарий эксплуатации: высокая доступность сервера (Patroni и репликация), слой соединений перед ним (пулинг), обслуживание хранилища (VACUUM и bloat) — и чтение планов, которыми база отвечает на запросы. Все они — про то, что происходит со стороны самого PostgreSQL, а не приложения. Завершает серию боевой кейс, где эти нити сходятся вместе на реальной мониторинговой БД: «TimescaleDB под Zabbix: как перестать вручную бороться с bloat».
Источники
- PostgreSQL Documentation — 14.1. Using EXPLAIN, 11.2. Index Types, F.32. pg_stat_statements
- Визуализатор планов — explain.tensor.ru
- Стенд:
postgres-ops/explain(PostgreSQL 18.4, Go 1.26.3 / pgx v5.10.0)
Комментарии