Стоимостная модель и кардинальность

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

SHOW seq_page_cost;        -- 1.0   последовательное чтение страницы

SHOW random_page_cost;     -- 4.0   случайное чтение страницы

SHOW cpu_tuple_cost;       -- 0.01  обработка одной строки

SHOW cpu_index_tuple_cost; -- 0.005 обработка записи индекса

SHOW cpu_operator_cost;    -- 0.0025 вычисление оператора/функции

Эти числа - не физика, а модель. random_page_cost = 4 отражает, что на вращающемся диске случайное чтение примерно вчетверо дороже последовательного. На SSD это соотношение меньше, поэтому на серверах с SSD random_page_cost часто снижают до 1.1-2.0 - и тем самым делают индексные сканы выгоднее в глазах планировщика.

Самый простой план - последовательное чтение всей таблицы. Его стоимость складывается из двух частей: прочитать все страницы и обработать все строки.

cost(Seq Scan) = relpages × seq_page_cost

               + reltuples × cpu_tuple_cost

relpages и reltuples - это сколько страниц и строк, по мнению планировщика, в таблице. Берутся они из pg_class и обновляются при ANALYZE/VACUUM:

SELECT relpages, reltuples FROM pg_class WHERE relname = 'tickets';

Если в запросе есть WHERE, добавляется стоимость проверки условия на каждой строке: reltuples × cpu_operator_cost за каждый оператор в фильтре. Само число прочитанных страниц от WHERE не меняется - Seq Scan всё равно читает всю таблицу, фильтр лишь отсеивает строки после чтения.

Возьмём tickets. Пусть pg_class показывает relpages = 4, reltuples = 500 (числа зависят от данных в твоём образе - бери свои).

cost = 4 × 1.0  +  500 × 0.01

     = 4.0      +  5.0

     = 9.0

Теперь сверяем:

EXPLAIN SELECT * FROM tickets;

-- Seq Scan on tickets  (cost=0.00..9.00 rows=500 width=...)

total cost в плане - ровно 9.00. Это не совпадение и не приближение: планировщик честно применил ту же формулу. Когда числа сходятся вручную, исчезает ощущение, что планировщик «решает по-своему» - он считает арифметику, которую ты теперь умеешь повторить. Подробный разбор - в cost-model.

В плане у каждого узла не одно число, а два: cost=0.00..9.00. Первое - startup cost, стоимость до выдачи первой строки. Второе - total cost, стоимость выдачи всех строк.

Для Seq Scan startup близок к нулю: первую строку отдают почти сразу. А вот у сортировки startup большой - чтобы отдать первую строку, нужно сначала отсортировать всё:

Sort  (cost=39.27..40.52 rows=500 width=...)

  ->  Seq Scan on tickets  (cost=0.00..9.00 rows=500 ...)

У Sort startup 39.27 - это работа, которую надо сделать до первой строки. Различие важно, потому что планировщик выбирает план не всегда по total cost. Когда наверху стоит LIMIT, он смотрит, сколько стоит получить только первые строки, - и тут план с маленьким startup выигрывает у плана с маленьким total. Разбор - в startup-total-cost.

LIMIT 10 способен перевернуть решение. Без него планировщик сравнивает планы по total cost, и Seq Scan большой таблицы может проиграть индексу. С LIMIT 10 важна стоимость первых десяти строк, а не всех.

Если есть индекс по колонке из ORDER BY, Index Scan отдаёт строки уже в нужном порядке и с маленьким startup: десять строк - и готово. Seq Scan + Sort обязан прочитать и отсортировать всю таблицу, прежде чем отдать первую, - его startup огромен. Поэтому запрос ORDER BY x LIMIT 10 с индексом по x уходит в Index Scan, хотя без LIMIT тот же запрос мог бы выбрать Seq Scan + Sort.

Отсюда практическое: LIMIT - не косметика поверх готового результата, а вход в стоимостную модель, меняющий сам план.

В каждом узле плана есть rows=N - сколько строк, по оценке планировщика, вернёт этот узел. Это кардинальность, и она важнее всех параметров стоимости вместе взятых. Почему: стоимость родительских узлов считается из числа строк, которое отдают дети. Ошибся в строках внизу - ошибся в стоимости наверху.

Кардинальность фильтра выводится из селективности - доли строк, проходящих условие. Если планировщик считает, что departure = 'SVO' пропускает 80% строк, а реально 100%, он недооценит размер и может выбрать неподходящий способ соединения дальше. Селективность он берёт из статистики - гистограмм и списков частых значений, о которых вся следующая глава.

Самое опасное в неверной кардинальности - она не остаётся локальной, а распространяется вверх по дереву и усиливается.

Пусть планировщик решил, что скан вернёт 10 строк, а реально их 10 000. Под это «10» он выбрал nested loop - алгоритм, отличный для десятка строк и катастрофический для десяти тысяч (он повторяет внутренний поиск на каждую внешнюю строку). На следующем уровне ошибка ещё растёт. В итоге план, идеальный для воображаемых 10 строк, выполняется минутами на реальных 10 000.

Вот почему диагностика планов - это всегда сравнение rows (оценка) с фактическим числом из EXPLAIN ANALYZE. Где оценка резко разошлась с фактом - там корень проблемы, а не там, где больше всего времени. К этому вернёмся в главе 30.