Основные концепции плана выполнения запроса: cost, rows, width

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

Оптимизатор запросов оценивает все возможные пути выполнения вашего запроса: последовательное сканирование таблицы, использование индексов, выполнение фильтрации и сортировки и т.д. Он пытается найти наиболее дешевый (в плане ресурсов) способ выполнить ваш запрос. То есть, он ищет компромисс между временем выполнения и ресурсами сервера.

Ключевые параметры плана выполнения

Что ж, теперь перейдем к само "жести" — разбору параметров, которые PostgreSQL показывает вам в результате выполнения команды EXPLAIN. Для начала возьмем простой пример:

EXPLAIN
SELECT * FROM students WHERE age > 20;

Вы получите что-то вроде такого результата:

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

Давайте разберем эти загадочные слова и числа.

1. cost (затраты на выполнение)

cost — это оценка того, сколько ресурсов потребуется для выполнения запроса. Этот параметр состоит из двух частей:

• Startup Cost: затраты на начало выполнения операции (например, подготовка индекса).
• Total Cost: суммарные затраты на выполнение всей операции.

Пример:

cost=0.00..35.00

• 0.00 — это Startup Cost.
• 35.00 — это Total Cost.

Чем ниже значения cost, тем предпочтительнее этот план для PostgreSQL. Однако, важно помнить, что cost — это относительная величина. Она не измеряется в секундах или миллисекундах, а скорее отражает внутреннюю оценку PostgreSQL.

2. rows (предполагаемое количество строк)

rows показывает, сколько строк PostgreSQL ожидает вернуть или обработать на этом этапе выполнения запроса. В нашем примере:

rows=7

Это значит, что PostgreSQL предполагает, что фильтр age > 20 вернет 7 строк. Эти данные берутся из статистики, которую PostgreSQL собирает о таблице. Если статистика устарела, прогноз может оказаться неправильным. Это может привести к менее оптимальному плану.

3. width (ширина строки в байтах)

width — это средний размер каждой строки, возвращаемой на данном этапе, измеренный в байтах. В нашем примере:

width=72

Это значит, что каждая возвращенная строка в среднем занимает 72 байта. width учитывает размер данных в столбцах и любые дополнительные накладные расходы, такие как идентификаторы строк или служебная информация.

Это примерно как с загрузкой приложения. Если его "вес" (аналог width) большой, вам потребуется больше времени для загрузки, даже если у вас быстрый интернет (аналог cost).

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

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

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

И мы выполняем следующий запрос:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Результат может выглядеть так:

Seq Scan on students  (cost=0.00..42.50 rows=3 width=164)
  Filter: ((age > 20) AND (major = 'CS'))

• Seq Scan: PostgreSQL выполняет последовательное сканирование таблицы students. Это значит, что он проходит по каждой строке.
• cost=0.00..42.50: Затраты на выполнение операции. Startup Cost составляет 0.00, а общие затраты — 42.50.
• rows=3: PostgreSQL ожидает, что фильтр age > 20 AND major = 'CS' вернет 3 строки.
• width=164: Каждая строка занимает в среднем 164 байта.

Теперь вы понимаете, как PostgreSQL принимает решения, и можете выявлять слабые места в запросах. Например, если вы видите высокий cost, это может быть признаком того, что запрос слишком тяжелый. Или, если вы видите большое количество строк в rows, вам стоит пересмотреть свой фильтр.

Как работает cost на практике?

Давайте добавим индекс на столбец age:

CREATE INDEX idx_age ON students(age);

Теперь повторим наш запрос:

EXPLAIN
SELECT * FROM students WHERE age > 20 AND major = 'CS';

Результат может измениться:

Bitmap Heap Scan on students  (cost=4.37..20.50 rows=3 width=164)
  Recheck Cond: (age > 20)
  Filter: (major = 'CS')
  ->  Bitmap Index Scan on idx_age  (cost=0.00..4.37 rows=20 width=0)
        Index Cond: (age > 20)

Что изменилось?

• Вместо Seq Scan теперь используется Bitmap Heap Scan: PostgreSQL сначала ищет подходящие строки в индексе idx_age, а затем извлекает их из таблицы.
• cost существенно снизился: теперь Startup Cost составляет 4.37, а Total Cost — 20.50.
• Операция стала более эффективной благодаря индексу.

Визуализация: разница между Seq Scan и Index Scan

Вот небольшая таблица сравнения, чтобы было нагляднее:

Подводные камни и типичные ошибки

Когда используете параметры плана выполнения, будьте готовы к некоторым сюрпризам. Например, не всегда низкий cost означает лучшее выполнение. Если статистика базы данных устарела (например, после массового обновления таблицы), план может быть недостаточно точным. Обновляйте статистику с помощью команды ANALYZE. Подробнее о ней в следующей лекции.

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