Использование EXPLAIN ANALYZE для измерения фактического времени выполнения запросов

Если команда EXPLAIN позволяет вам заглянуть в кристальный шар и увидеть, как PostgreSQL “планирует” выполнить запрос, то EXPLAIN ANALYZE превращает вас в настоящего сыщика, который выясняет, что на самом деле произошло.

Ключевые отличия EXPLAIN от EXPLAIN ANALYZE:

EXPLAIN – это теория, показывающая, как PostgreSQL планирует выполнение запроса. Вы видите предполагаемые значения, такие как количество строк (rows) и стоимость выполнения (cost).

EXPLAIN ANALYZE – это практика. PostgreSQL реально выполняет запрос и показывает:

• Фактическое количество обработанных строк на каждом этапе.
• Фактическое время выполнения каждой операции.
• Сравнение с предположениями плана (rows и cost).

Пример: если ваш запрос предполагает обработку 100 строк, а фактически обрабатывает 10 000 строк, EXPLAIN ANALYZE тут же раскроет этот неопрятный факт!

Основной синтаксис и использование

Как и EXPLAIN, EXPLAIN ANALYZE легко использовать. Просто добавьте слово ANALYZE к вашей команде EXPLAIN.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Вот что сделает PostgreSQL:

• Он выполняет запрос.
• Записывает каждую операцию в плане выполнения, включая фактические показатели.
• Возвращает полное описание процесса выполнения запроса.

Какие данные предоставляет EXPLAIN ANALYZE?

Фактическое время выполнения операций:

• Actual Start Time: когда операция началась.
• Actual End Time: когда операция завершилась.

Общее количество обработанных строк:

Это помогает оценить, насколько точны предположения плана (значения rows).

Информация о буферах:

Как использовались дисковые и память-ориентированные буферы.

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

Давайте посмотрим на конкретный пример. У нас есть таблица students, содержащая данные о студентах:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Выполним запрос для извлечения студентов старше 20 лет:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Пример результата:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Разберём результат:

• Seq Scan – говорит, что PostgreSQL выполняет последовательное сканирование таблицы.
• cost=0.00..14.00 – это предполагаемая стоимость операции.
• rows=2 – PostgreSQL ожидает, что запрос вернет 2 строки (и он прав!).
• actual time=0.025..0.026 – фактическое время выполнения операции (в миллисекундах).
• Rows Removed by Filter: 2 – две строки были отфильтрованы, так как не соответствовали условию WHERE.

Сравнение теории и практики

Вот в чём магия EXPLAIN ANALYZE: он показывает, как на самом деле был выполнен запрос, а также позволяет сравнить это с теоретическим планом выполнения.

Давайте посмотрим более сложный пример.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Пример результата:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Что мы видим:

1. PostgreSQL выполнил запрос за 0.057 миллисекунды.
2. Только одна строка (rows=1) соответствует условиям WHERE.
3. Три строки были отфильтрованы (Rows Removed by Filter: 3).

Резюме

Использование EXPLAIN ANALYZE позволяет вам найти узкие места и понять, как оптимизировать запросы. Например:

• Если Seq Scan слишком "тяжелый", возможно, пора добавить индекс.
• Если предположения PostgreSQL значительно отличаются от реальных данных, нужно проверить статистику таблиц (ANALYZE) или структуру индексов.

2

Задача

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

Недоступна

Анализ выполнения запроса с фильтрацией и сортировкой

Анализ выполнения запроса с фильтрацией и сортировкой

Открыть