Интерпретация плана выполнения: чтение и анализ узлов (`Seq Scan`, `Index Scan`, `Hash Join`)

Сегодня мы разберёмся, что такое узлы плана выполнения PostgreSQL, как их читать и, самое главное, как понять, что что-то пошло не так. Вы узнаете, почему ваша база данных иногда предпочитает использовать ресурсозатратный Seq Scan, хотя казалось бы, что индекс уже есть, а может быть и не один.

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

Sequential Scan (Seq Scan)

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

Когда используется Seq Scan?
Seq Scan применяется, если:

• В таблице нет подходящего индекса для ускорения запроса.
• Условие фильтрации слишком общее, чтобы индекс оказался полезным (например, извлечение более 50% данных).
• PostgreSQL считает, что последовательное чтение таблицы быстрее, чем использование индекса (иногда такое бывает на очень маленьких таблицах).

EXPLAIN SELECT * FROM students WHERE age > 18;

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

Seq Scan on students  (cost=0.00..35.50 rows=10 width=50)
  Filter: (age > 18)

Обратите внимание на Seq Scan on students — это PostgreSQL говорит, что он будет читать таблицу "students" целиком.

Проблемы с Seq Scan: Если таблица огромная, последовательное сканирование может занять очень много времени.

Index Scan

Index Scan — это сканирование данных с использованием индекса. Когда вы создаёте индекс в PostgreSQL, это примерно как создать список "содержания" для вашей таблицы. Если запрос может воспользоваться индексом, PostgreSQL обходит таблицу не целиком, а только нужные части.

Когда используется Index Scan?

• В запросе указаны условия фильтрации для столбца с индексом (например, WHERE).
• Используются операции сравнения, такие как =, <, >, BETWEEN и т.д.

CREATE INDEX idx_students_age ON students(age);

EXPLAIN SELECT * FROM students WHERE age = 18;

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

Index Scan using idx_students_age on students  (cost=0.15..8.27 rows=1 width=50)
  Index Cond: (age = 18)

Здесь Index Scan using idx_students_age показывает, что PostgreSQL использует индекс idx_students_age. Построчное чтение таблицы заменяется на гораздо более быстрый доступ через индекс.

Преимущества Index Scan:

• Значительное ускорение запросов при больших таблицах.
• Уменьшение количества данных, считаемых с диска.

Проблемы Index Scan:
Если ваш запрос возвращает слишком много данных (например, больше половины таблицы), использование индекса может быть даже медленнее, чем Seq Scan.

Hash Join

Hash Join используется для объединения двух таблиц на основе условия соединения (например, ON students.course_id = courses.id). PostgreSQL создаёт хэш-таблицу для одной из таблиц (меньшей по размеру) и использует её для поиска совпадений во второй таблице.

Когда используется Hash Join?

• При соединении таблиц через INNER JOIN, LEFT JOIN и т.д.
• Когда PostgreSQL считает Hash Join более эффективным, чем другие методы соединения.

EXPLAIN
SELECT * 
FROM students 
JOIN courses ON students.course_id = courses.id;

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

Hash Join  (cost=25.00..50.00 rows=10 width=100)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..20.00 rows=10 width=50)
  -> Hash  (cost=15.00..15.00 rows=10 width=50)
       -> Seq Scan on courses  (cost=0.00..15.00 rows=10 width=50)

Здесь Hash Join соединяет две таблицы. Обратите внимание, что PostgreSQL сначала выполняет Seq Scan для обеих таблиц, а затем строит хэш-таблицу (Hash).

Преимущества Hash Join:

• Быстрая обработка для таблиц среднего размера.
• Эффективен для соединения таблиц с большим количеством строк.

Проблемы Hash Join:
Если размер хэш-таблицы превышает доступную память, PostgreSQL будет использовать диск для её хранения, что значительно замедляет соединение.

Пример анализа плана выполнения

Давайте разберем реальный пример.

Запрос:

EXPLAIN ANALYZE
SELECT *
FROM students
JOIN courses ON students.course_id = courses.id
WHERE students.age > 18;

Результат:

Hash Join  (cost=35.00..75.00 rows=5 width=100) (actual time=1.00..2.50 rows=5 loops=1)
  Hash Cond: (students.course_id = courses.id)
  -> Seq Scan on students  (cost=0.00..40.00 rows=10 width=50) (actual time=0.50..1.00 rows=7 loops=1)
        Filter: (age > 18)
        Rows Removed by Filter: 3
  -> Hash  (cost=25.00..25.00 rows=5 width=50) (actual time=0.30..0.30 rows=5 loops=1)
       -> Seq Scan on courses  (cost=0.00..20.00 rows=5 width=50) (actual time=0.20..0.25 rows=5 loops=1)
Planning Time: 0.50 ms
Execution Time: 3.00 ms

Интерпретация:

1. Hash Join: Главный узел. PostgreSQL соединяет таблицы students и courses.
   • actual time: от 1.00 до 2.50 мс.
   • rows=5: запрос вернул 5 строк.
2. Вложенные узлы:
   • Seq Scan on students: последовательно читает таблицу students и применяет фильтр (age > 18).
   • Rows Removed by Filter = 3: 3 строки не соответствовали условию.
   • Hash: PostgreSQL создает хэш-таблицу для таблицы courses.

Сравнение и выбор узлов

Когда вы анализируете план выполнения, ключ в том, чтобы понимать, почему PostgreSQL выбрал тот или иной метод обработки данных. Иногда вам нужно вмешаться, чтобы исправить ситуацию, например, добавить индекс или переписать запрос. Вот несколько советов:

• Если видите Seq Scan на большой таблице, подумайте об индексах.
• Если Hash Join слишком медленный, проверьте доступную память для PostgreSQL.
• Используйте EXPLAIN ANALYZE, чтобы сравнить предполагаемые и фактические значения метрик (rows, time).

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