Что такое EXPLAIN и как с его помощью анализировать запросы

Представьте, что вы разрабатываете приложение, и один из запросов неожиданно становится самым дорогим в вашей базе данных. Вы начинаете замечать сбои и тормоза в работе приложения. Вот тут-то на сцену выходит EXPLAIN, который помогает понять, где все пошло не так. Оптимизация запросов на основе анализа EXPLAIN позволяет вам сэкономить ресурсы, выиграть время и улучшить впечатление пользователей от работы вашего приложения.

EXPLAIN — это ваш способ заглянуть внутрь PostgreSQL и увидеть, как именно база данных собирается выполнять запрос. Он показывает, будет ли использован индекс или всё сведётся к полному сканированию таблицы, какие шаги выполнит оптимизатор, в каком порядке они пойдут и насколько объёмными будут промежуточные результаты.

Другими словами, EXPLAIN позволяет понять, чего ожидать от выполнения запроса: насколько он «тяжёлый», сколько строк предполагается обработать, и какие ресурсы при этом задействуются. Это незаменимый инструмент, когда запрос вдруг начинает тормозить, а вам нужно выяснить — почему.

EXPLAIN — это как фонарик в темноте: с ним видно, что происходит под капотом, и где именно всё идёт не так.

Синтаксис команды EXPLAIN

Давайте посмотрим на базовый синтаксис команды EXPLAIN:

EXPLAIN ваш_SQL_запрос;

Пример запроса:

EXPLAIN SELECT * FROM students WHERE age > 20;

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

Вот пример вывода:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Этот вывод может показаться пугающим, но не переживайте — сейчас мы разберемся с основными компонентами.

Разбор базового плана выполнения

Давайте разберем тот самый результат:

1. Seq Scan on students — это означает, что PostgreSQL будет сканировать таблицу students полностью (последовательное сканирование). Это не всегда плохо, но в больших таблицах Seq Scan может быть медленным.

2. cost=0.00..35.00 — это оценка затрат на выполнение операции:

   • Startup Cost: стартовые затраты операции (в данном случае 0.00).
   • Total Cost: общие затраты на завершение операции (здесь 35.00).

3. rows=7 — PostgreSQL предполагает, что условие age > 20 вернет 7 строк. Это называется "кардинальностью" (cardinality). Если вы видите странные оценки, это может быть сигналом, что статистика вашей таблицы устарела.

4. width=37 — это средний размер одной строки в байтах.

5. Filter: (age > 20) — уточняет, что PostgreSQL применит фильтр, проверяя каждую строку.

Таким образом, вывод EXPLAIN дает вам представление о стратегиях и предположениях PostgreSQL. Вы можете использовать эту информацию для оптимизации.

Опции команды EXPLAIN

Хотя базовый вывод EXPLAIN уже полезен, вы можете модифицировать его с помощью следующих опций:

ANALYZE

С помощью этой опции PostgreSQL не только покажет план выполнения, но и выполнит запрос, предоставив фактические данные. Пример:

EXPLAIN ANALYZE SELECT * FROM students WHERE age > 20;

Это позволит сравнить предположения PostgreSQL с реальным выполнением, чтобы понять, насколько они совпадают.

VERBOSE

Показывает дополнительную детализацию, полезную для глубокого анализа. Пример:

EXPLAIN VERBOSE SELECT * FROM students WHERE age > 20;

BUFFERS

Показывает использование буферов памяти при выполнении запроса. Применяется вместе с ANALYZE:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM students WHERE age > 20;

COSTS

Если вы хотите скрыть или показать информацию о затратах (cost), используйте эту опцию:

EXPLAIN (COSTS OFF) SELECT * FROM students WHERE age > 20;

FORMAT

Вывод плана может быть представлен в других форматах, таких как JSON или XML. Пример:

EXPLAIN (FORMAT JSON) SELECT * FROM students WHERE age > 20;

Пример использования EXPLAIN

Рассмотрим базу данных university с таблицей students. Допустим, вы хотите найти всех студентов старше 20 лет:

EXPLAIN SELECT * FROM students WHERE age > 20;

Вывод может быть следующим:

Seq Scan on students  (cost=0.00..35.00 rows=7 width=37)
  Filter: (age > 20)

Как мы уже упоминали, это последовательное сканирование Seq Scan, которое может быть неэффективным для больших таблиц.

Теперь создадим индекс по столбцу age и посмотрим, изменится ли план:

CREATE INDEX age_index ON students(age);

EXPLAIN SELECT * FROM students WHERE age > 20;

Вывод:

Index Scan using age_index on students  (cost=0.15..4.23 rows=7 width=37)
  Index Cond: (age > 20)

Теперь PostgreSQL использует индексное сканирование (Index Scan), что обычно быстрее, особенно для больших таблиц.

Типичные вопросы и ошибки

Почему мой запрос выполняется медленно, несмотря на индекс?

Возможно, запрос возвращает слишком много строк, что делает использование индекса менее выгодным. Индекс может быть некачественным или не обновленным.

Что если вывод EXPLAIN сложно понять?

Начните с простых запросов и изучайте узлы плана выполнения по одной операции.

Как узнать, устарела ли статистика таблицы?

Выполните команду ANALYZE students

Когда использовать EXPLAIN без ANALYZE?

Если вы хотите увидеть план без реального выполнения (например, для запросов, которые модифицируют данные).

2

Задача

SQL SELF, 41 уровень, 2 лекция

Недоступна

Использование `EXPLAIN` с индексацией

Использование `EXPLAIN` с индексацией

Открыть