llmdocsсправочник разработчика

EXPLAIN и план запроса

Материал из llmdocs — справочник разработчика
Портал: Базы данных
EXPLAIN и план запроса
КомандыEXPLAIN, EXPLAIN ANALYZE (PostgreSQL, MySQL), EXPLAIN QUERY PLAN (SQLite)
Оптимизаторстоимостной, на основе статистики
Основы стоимостной оптимизацииSystem R, Патриция Селинджер (1979)
Типичные узлыSeq Scan, Index Scan, Bitmap Heap Scan, Nested Loop, Hash Join, Merge Join, Sort
Визуализацияexplain.depesz.com, explain.dalibo.com

План запроса — конкретный способ, которым СУБД выполнит SQL-запрос: в каком порядке читать таблицы, какие индексы использовать, каким алгоритмом соединять строки, где сортировать и агрегировать. SQL декларативен — запрос описывает результат, а не алгоритм, — и план выбирает оптимизатор. Команда EXPLAIN показывает выбранный план, а EXPLAIN ANALYZE выполняет запрос и добавляет фактическое время и число строк на каждом шаге.

EXPLAIN — главный инструмент разбора медленных запросов: вместо догадок «наверное, не хватает индекса» он показывает, что на самом деле происходит.

Как оптимизатор выбирает план

Для одного запроса возможны тысячи планов. Современные СУБД используют стоимостную оптимизацию, идея которой восходит к системе System R и работе Патриции Селинджер (1979):[4] оптимизатор оценивает стоимость каждого варианта в условных единицах — сколько страниц прочитать с диска, сколько строк обработать процессором — и выбирает самый дешёвый.

Оценки строятся на статистике о данных: число строк в таблице, доля пустых значений, самые частые значения и гистограмма распределения каждого столбца.[3] Статистику собирает команда ANALYZE (в PostgreSQL — автоматически вместе с autovacuum). Устаревшая или неточная статистика — частая причина плохих планов.

Чтение плана

EXPLAIN ANALYZE
SELECT c.name, count(*) AS orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '7 days'
GROUP BY c.name;

HashAggregate  (cost=1250.3..1262.8 rows=1250 width=40) (actual time=18.4..18.9 rows=1180 loops=1)
  Group Key: c.name
  ->  Hash Join  (cost=310.0..1180.1 rows=14040 width=32) (actual time=3.1..15.2 rows=13892 loops=1)
        Hash Cond: (o.customer_id = c.id)
        ->  Index Scan using orders_created_at_idx on orders o  (cost=0.43..820.5 rows=14040 width=8)
                                                                 (actual time=0.02..7.8 rows=13892 loops=1)
              Index Cond: (created_at >= (now() - '7 days'::interval))
        ->  Hash  (cost=185.0..185.0 rows=10000 width=36) (actual time=3.0..3.0 rows=10000 loops=1)
              ->  Seq Scan on customers c  (cost=0.0..185.0 rows=10000 width=36) (actual ...)
Planning Time: 0.4 ms
Execution Time: 19.3 ms

План — дерево: читается от вложенных узлов к корню. Для каждого узла:[1]

  • cost=начальная..полная — оценка стоимости до первой строки и до последней;
  • rows — сколько строк оптимизатор ожидает; в actual — сколько получилось на деле;
  • loops — сколько раз выполнялся узел (время и строки в actual указаны за один проход);
  • width — средний размер строки в байтах.

Самое важное — сравнить ожидаемое и фактическое число строк. Расхождение в десятки и сотни раз означает, что оптимизатор ошибся в оценке и, скорее всего, выбрал неподходящий план.

Основные узлы

Узел Что делает Когда хорош
Seq Scan читает таблицу целиком маленькие таблицы, выборка большой доли строк
Index Scan проходит индекс и читает строки из таблицы выборка малой доли строк
Index Only Scan отвечает по индексу, не заходя в таблицу покрывающий индекс
Bitmap Heap Scan собирает по индексу карту страниц, затем читает их по порядку средняя доля строк, несколько условий
Nested Loop для каждой строки внешней стороны ищет пары во внутренней мало строк снаружи и индекс внутри
Hash Join строит хеш-таблицу по меньшей стороне большие наборы, условие равенства
Merge Join сливает два отсортированных потока данные уже отсортированы
Sort, HashAggregate сортировка, группировка —

Типичные проблемы и что они значат

  • Seq Scan по большой таблице с избирательным условием — нет подходящего индекса или он неприменим (функция над столбцом, несовпадение типов, LIKE '%...').
  • Nested Loop с огромным числом loops — оптимизатор недооценил число строк; помогает ANALYZE, расширенная статистика (CREATE STATISTICS для связанных столбцов) или переписывание запроса.
  • Sort с external merge Disk — сортировке не хватило памяти (work_mem), она ушла на диск.
  • Rows Removed by Filter много больше полученных строк — индекс не покрывает часть условия.

Ключ EXPLAIN (ANALYZE, BUFFERS) дополнительно показывает, сколько страниц прочитано из кэша и с диска, — это часто объясняет разницу между первым и повторным запуском.[2]

Другие СУБД

  • MySQL — EXPLAIN выводит табличный план (тип доступа ALL, ref, range, используемый ключ, оценку строк), EXPLAIN ANALYZE (с версии 8.0.18) — дерево с фактическим временем, EXPLAIN FORMAT=JSON — подробности.[5]
  • SQLite — EXPLAIN QUERY PLAN кратко показывает, где используется индекс (SEARCH ... USING INDEX), а где полный проход (SCAN).[6]

Практика

  1. Найти медленные запросы — в PostgreSQL через расширение pg_stat_statements или журнал медленных запросов.
  2. Выполнить EXPLAIN (ANALYZE, BUFFERS) на реалистичных данных: план на пустой тестовой базе бесполезен. Осторожно с изменяющими запросами — ANALYZE их действительно выполняет; оборачивайте в BEGIN ... ROLLBACK.
  3. Найти самый дорогой узел и расхождения оценок.
  4. Исправить: индекс, статистика, переписывание запроса, настройки памяти.
  5. Проверить план повторно.

Длинные планы удобно разбирать в визуализаторах вроде explain.depesz.com и explain.dalibo.com.

См. также

Источники

  1. PostgreSQL Documentation — Using EXPLAIN
  2. PostgreSQL Documentation — EXPLAIN
  3. PostgreSQL Documentation — Statistics Used by the Planner
  4. P. Selinger et al. — Access Path Selection in a Relational Database Management System (SIGMOD 1979)
  5. MySQL — EXPLAIN Statement
  6. SQLite — EXPLAIN QUERY PLAN
Категории: