Вывод SQL EXPLAIN не обязан выглядеть как текст из инопланетного языка

Вы выполняете запрос EXPLAIN на медленном запросе. База данных возвращает что-то вроде этого:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
  Hash Cond: (o.customer_id = c.id)
  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000

Вы смотрите на это десять секунд. Вы добавляете индекс случайным образом. Вы перезапускаете приложение и надеетесь. Это не стратегия отладки — это суперститие с дополнительными шагами.

EXPLAIN на самом деле сообщает вам что-то конкретное и действуемое. Вот как его читать.

EXPLAIN против EXPLAIN ANALYZE: какую использовать

Простой EXPLAIN показывает вам, что планировщик планирует сделать. Он никогда не выполняет запрос — он просто показывает план с оценочными затратами. Быстро, но оценки могут быть неверными. на самом деле выполняет запрос и добавляет реальные временные метки. Вы получаете как план, так и реальные данные:

EXPLAIN ANALYZE вариант доступен только в PostgreSQL и добавляет счётчики попаданий и пропусков в буфер — полезен для диагностики проблем с I/O, но пока проигнорируйте его.

-- PostgreSQL: run the query and show real timings
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

-- MySQL equivalent
EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

The BUFFERS Одно предостережение:

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

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'pending';
ROLLBACK;

Каждый узел в плане показывает

Люди видят эти числа и предполагают, что они в миллисекундах. Это не так. (cost=X..Y rows=N width=W)cost=X..Y

• — X — это стартовая стоимость (работа, выполняемая до возврата первого результата), Y — общая стоимость обработки всех строк. Единица — произвольный «стоимость страницы» — примерно пропорциональна чтению страниц диска, но калиброванная по константам стоимости PostgreSQL. Стоимость 1.0 соответствует одному последовательному чтению страницы. rows=N
• — оценка количества строк, сделанная планировщиком. Может быть совершенно неверной, если статистика по таблицам устарела. width=W
• — средний размер строки в байтах. Более широкие строки замедляют соединения и сортировки. Когда вы выполняете

каждый узел также получает EXPLAIN ANALYZE. Эти (actual time=X..Y rows=N loops=L)миллисекунд. Большое расхождение между оценкой и фактическим количеством строк — самый надёжный сигнал того, что планировщик принял плохое решение. являются Аннотированный обзор EXPLAIN ANALYZE

Вот тот же запрос из введения, но теперь с

и с каждой строкой объяснённой: ANALYZE Пункт действия сразу выделяется:

Hash Join  (cost=145.00..578.00 rows=3200 width=96)
           (actual time=2.451..12.879 rows=3168 loops=1)
-- Top-level node. Joins orders and customers via hashing.
-- cost estimate: 145..578 | actual: 2.4ms startup, 12.9ms total
-- rows estimate: 3200 | actual: 3168 — pretty close here

  Hash Cond: (o.customer_id = c.id)
  -- This is the join key. The planner built a hash table on customers.id
  -- then probed it with every row from orders.

  ->  Seq Scan on orders  (cost=0.00..248.00 rows=16000 width=64)
                          (actual time=0.019..3.125 rows=16000 loops=1)
  -- Full table scan on orders. 16,000 rows scanned.
  -- No filter here — we pull all orders and join them below.
  -- This is expected: we need all orders, so no index would help here.

  ->  Hash  (cost=132.50..132.50 rows=1000 width=32)
            (actual time=2.412..2.413 rows=1000 loops=1)
            Buckets: 1024  Batches: 1  Memory Usage: 64kB
  -- Builds the in-memory hash table from the customers result.
  -- 1 batch = fits in work_mem. Multiple batches = spilling to disk (bad).

        ->  Seq Scan on customers  (cost=0.00..132.50 rows=1000 width=32)
                                   (actual time=0.012..1.345 rows=1000 loops=1)
              Filter: ((country)::text = 'US'::text)
              Rows Removed by Filter: 9000
        -- Scanned all 10,000 customer rows. Kept 1,000 where country='US'.
        -- *** This is the expensive part. An index on customers.country
        --     would let PostgreSQL skip the 9,000 discarded rows entirely. ***

Planning Time: 0.187 ms
Execution Time: 13.451 ms

на полном сканировании всей таблицы — наиболее распространённый паттерн для «тут нужно индекс». Rows Removed by Filter: 9000 Типы узлов, которые вы действительно встретите

Seq Scan

Читает каждую строку в таблице в порядке хэп-ордера. Люди видят это и сразу хотят добавить индекс. Но Seq Scan не всегда ошибочен — если фильтр соответствует 20%+ строк, планировщик правильно решает, что случайные поиски по индексу будут медленнее. Seq Scan на таблице из 500 строк — нормально. Seq Scan на таблице из 10-миллионных строк с очень селективным фильтром — проблема.

Index Scan

Использует B-дерево индекса для поиска соответствующих строк, затем извлекает каждую строку из хэпа. Извлечение из хэпа — дорогое действие — каждая строка требует случайного чтения. Хорошо работает, когда фильтр селективен (то есть вы извлекаете небольшую часть строк).

Index Only Scan

Запрос может быть полностью решён из индекса без обращения к хэпу. Это требует, чтобы все столбцы в вашем

условии были покрыты индексом. Самый быстрый тип сканирования — если вы видите это, то дизайн индекса работает правильно. SELECT и WHERE Bitmap Heap Scan

Среднее решение. Планировщик строит битовую карту страниц хэпа, содержащих соответствующие строки (через

), затем извлекает только эти страницы в порядке, что снижает случайные чтения. Часто встречается, когда индексный поиск приведёт к слишком большим случайным чтениям, а полный сканирование сканирует слишком много ненужных строк. Bitmap Index ScanHash Join

Создаёт хэш-таблицу из меньшего входа, затем проверяет её на каждой строке из большего входа. Хорошо подходит для больших соединений без полезного порядка сортировки. Следите за

в узле хэша — это означает, что хэш-таблица переполнилась и была записана на диск из-за превышения Batches: > 1 . Увеличение work_memдля сессии часто решает эту проблему. work_mem Nested Loop

Для каждой строки на внешней стороне проверяется внутренняя сторона (обычно через индекс). Отлично работает, когда одна сторона очень мала — O(n) вместо O(n log n). Плохо работает, когда обе стороны большие, потому что внутренний скан выполняется один раз на каждую строку внешнего набора. Если вы видите Nested Loop с

, то внутренний скан выполняется 50 000 раз. loops=50000Merge Join

Оба входа приходят отсортированными по ключу соединения; планировщик идёт по ним параллельно. Эффективно, но требует сортировки на начальном этапе. Вы увидите это, когда обе стороны уже имеют индекс на ключ соединения, или когда планировщик решает, что сортировка дешевле, чем хэширование.

Почему планировщик выбирает Seq Scan вместо Index Scan

Это наиболее путает людей. У вас есть индекс. Запрос медленный. EXPLAIN показывает Seq Scan.

Планировщик использует статистику по столбцам — хранится в

, обновляется через pg_statistic— чтобы оценить, сколько строк фильтр вернёт. Если оценка показывает, что 30% строк будут соответствовать, Seq Scan действительно дешевле, чем 300 000 случайных поисков по индексу. Случайные операции чтения дороги. ANALYZE Порог, при котором планировщик предпочитает Seq Scan, составляет примерно 10–20% селективности, в зависимости от вашей аппаратуры и констант стоимости. Вы можете проверить оценку планировщика по сравнению с реальными данными:

Если планировщик оценил 8 000 строк, но фактически их только 80, это проблема устаревших статистик. Выполните

-- Check the planner's selectivity estimate
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Compare with actual count
SELECT COUNT(*) FROM orders WHERE status = 'pending';
SELECT COUNT(*) FROM orders;

и перепроверьте план. Это исправляет плохие планы чаще, чем добавление индексов. ANALYZE orders; Чтение EXPLAIN в формате JSON

PostgreSQL может выводить план в формате JSON, что удобно для программного парсинга или просмотра в виде дерева:

Формат JSON плотный. Если вы вставите его в

EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.country = 'US';

, он станет навигируемым деревом — полезно при работе с глубоко вложенным планом из сложных запросов с несколькими подзапросами или CTE. IO Tools’ JSON FormatterПрактическая последовательность отладки

Когда запрос медленный, следуйте этой последовательности, а не угадывайте:

на самом запросе с реальными параметрами (не

1. Выполните EXPLAIN (ANALYZE, BUFFERS) заполнителями — используйте реальные значения). $1 Найдите самый дорогой узел.
2. Ищите самый высокий , а не самый высокий оценочный расход. Они могут отличаться. actual timeСравните оценку и фактическое количество строк
3. на этом узле. Отклонение более чем в 10 раз означает, что планировщик работал с плохой информацией. Сначала выполните . ANALYZE <table>; Ищите Seq Scans на больших таблицах с селективными фильтрами.
4. Непосредственно под Seq Scan — ваш кандидат на индекс. Rows Removed by Filter: <large number> Проверьте узлы Hash на наличие
5. Если они есть, соединение переполнилось на диск. Увеличьте Batches > 1. для сессии и перезапустите. work_mem Проверьте Nested Loop с высоким количеством итераций.
6. Количество итераций в тысячи означает, что внутренний скан подвергается сильному давлению. Индекс на столбце соединения в таблице внутреннего скана обычно решает эту проблему. Перед добавлением новых индексов также выполните запросы через

IO Tools’ SQL Formatter — читаемые запросы проще анализировать, и иногда переписывание устраняет проблему полностью. Понимание плана — первый шаг, а не добавление индекса. Как только вы сможете читать вывод EXPLAIN, вы будете тратить меньше времени на угадывание и больше времени на целенаправленные изменения, которые действительно работают.

Вывод EXPLAIN не должен выглядеть как текст из космоса 2

Вам также может понравиться