Как планировщик запросов выбирает план и почему он регулярно ошибается

MAATRIX

Когда вы отправляете в базу SELECT ... WHERE ... JOIN ..., вы не говорите ей, как именно выполнять запрос — вы говорите, что вам нужно получить. Как именно это получить, решает отдельный компонент — планировщик запросов (query planner, или оптимизатор). Он перебирает несколько способов добраться до тех же данных, оценивает каждый по стоимости и выбирает тот, что кажется дешевле всего. Проблема в том, что «кажется» здесь ключевое слово: планировщик не знает данные точно, он о них догадывается — и иногда догадывается неправильно.

Один запрос — несколько способов его выполнить

SQL — декларативный язык: вы описываете результат, а не алгоритм. Одну и ту же выборку база физически может получить разными путями, и они не эквивалентны по стоимости.

Возьмём простой пример: SELECT * FROM orders WHERE customer_id = 42. У планировщика как минимум два варианта:

• Последовательное сканирование (Seq Scan) — прочитать всю таблицу целиком, строка за строкой, и отфильтровать нужные.
• Сканирование по индексу (Index Scan) — если по customer_id есть индекс, найти в нём нужные позиции и точечно прочитать только соответствующие строки таблицы.

Для таблицы на несколько тысяч строк разница может быть незаметна. Для таблицы на десятки миллионов строк — это разница между чтением всей таблицы и чтением нескольких десятков строк. Но верно и обратное: если условие customer_id = 42 отбирает половину таблицы, тот же Index Scan может оказаться медленнее, чем честное последовательное чтение — потому что случайный доступ по индексу к каждой второй строке дороже, чем один проход подряд.

Дальше добавляется JOIN — и вариантов становится ощутимо больше:

• Nested Loop Join — для каждой строки одной таблицы искать совпадения в другой. Хорош, когда одна из сторон маленькая.
• Hash Join — построить хеш-таблицу по одной стороне и пробегать по второй. Хорош для больших наборов без подходящего индекса.
• Merge Join — слить два отсортированных потока. Хорош, когда данные уже отсортированы (например, по индексу) или сортировка дёшева.

Есть и порядок соединения таблиц: для запроса с несколькими JOIN число возможных комбинаций растёт факториально. Для 3-4 таблиц планировщик обычно перебирает все варианты; для запроса с десятком таблиц полный перебор становится слишком дорогим, и он переключается на эвристики — то есть сам процесс выбора плана становится компромиссом между качеством решения и временем на его поиск.

Отдельная статья о том, как индекс ускоряет запрос и когда наоборот замедляет — это ровно та развилка, которую планировщик проходит на каждом запросе с условием WHERE.

Стоимость плана — условные единицы, а не секунды

Планировщик не измеряет время выполнения — он считает абстрактную «стоимость» в условных единицах. Модель стоимости учитывает:

• сколько страниц данных придётся прочитать с диска (или сколько уже, вероятно, есть в кеше);
• сколько строк нужно обработать на каждом шаге;
• дороже ли последовательное чтение случайного доступа (на вращающемся диске — заметно дороже, на SSD — разница меньше, но не нулевая из-за самого механизма случайного I/O и накладных расходов процессора);
• стоимость самой обработки строки процессором — сравнение, вычисление выражения, агрегация.

У этой модели есть настраиваемые коэффициенты — например, во сколько раз случайный доступ к странице дороже последовательного. По умолчанию они рассчитаны на обычный сервер с диском, а не на конкретное железо заказчика: на сервере с NVMe-накопителем случайный доступ реально дешевле, чем предполагает модель по умолчанию, — это одна из типичных настроек тюнинга, наравне с тем, что описано в статье про тюнинг PostgreSQL на сервере.

Но даже при идеально откалиброванных коэффициентах вся эта арифметика строится на одном исходном числе — сколько строк вернёт каждый шаг плана. И вот это число планировщик не измеряет, а оценивает.

Статистика — то немногое, что база знает о своих данных

Чтобы оценить, сколько строк отберёт условие WHERE status = 'paid', планировщику нужно представление о том, как распределены значения в столбце status. Это представление он берёт не из самой таблицы (пересчитывать точно на каждый запрос — слишком дорого), а из заранее собранной статистики.

В PostgreSQL статистика собирается командой ANALYZE (обычно её запускает автоматически фоновый процесс autovacuum) и хранится в системных таблицах, доступных через представление pg_stats:

SELECT attname, n_distinct, null_frac, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'status';

Здесь есть несколько ключевых характеристик:

• n_distinct — приблизительное число различных значений в столбце;
• null_frac — доля NULL-значений;
• most_common_vals / most_common_freqs — список самых частых значений и их доля (Most Common Values, MCV-список);
• гистограмма — для значений вне MCV-списка распределение хранится как гистограмма по границам диапазонов.

Ключевое слово здесь — «приблизительное». ANALYZE не читает таблицу целиком: он берёт случайную выборку строк (размер выборки зависит от параметра default_statistics_target, по умолчанию собирается порядка сотни наиболее частых значений и сопоставимое число интервалов гистограммы на столбец) и строит статистику по этой выборке, а не по всем данным.

MySQL и MariaDB с InnoDB работают похоже: ANALYZE TABLE собирает статистику по индексам, и планировщик тоже использует выборку, а не полное чтение таблицы. Идея та же — оценка на основе сэмпла, а не точный подсчёт.

Почему это оценка, а не факт

Тут стоит остановиться и явно проговорить, откуда берётся неточность — их несколько источников, и они складываются.

Выборка — это не перепись. Статистика строится по случайному подмножеству строк. Если в таблице есть значения, встречающиеся очень редко и неравномерно (например, один клиент с миллионом заказов на фоне тысяч клиентов с несколькими заказами каждый), выборка может не попасть на эту аномалию — или, наоборот, случайно переоценить её долю.

Столбцы по умолчанию считаются независимыми. Это, пожалуй, самое частое расхождение оценки с реальностью. Если запрос фильтрует сразу по двум столбцам — WHERE city = 'Москва' AND postal_code LIKE '101%' — планировщик по умолчанию перемножает избирательность каждого условия отдельно, как будто город и почтовый индекс никак не связаны. На деле они жёстко связаны: почти все строки с postal_code LIKE '101%' и так относятся к Москве. Реальное число строк, подходящих под оба условия, гораздо больше произведения двух отдельных долей — планировщик занижает ожидаемое число строк и может выбрать план, рассчитанный на маленькую выборку (например, Nested Loop), тогда как по факту строк окажется на порядок больше, и план обернётся медленным.

Именно для этого случая в PostgreSQL есть расширенная статистика — CREATE STATISTICS, которая явно просит планировщик учесть зависимость между конкретными столбцами:

CREATE STATISTICS orders_city_postal (dependencies)
ON city, postal_code FROM orders;
ANALYZE orders;

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

Данные меняются, статистика — нет, пока её не пересчитают. Статистика — это снимок на момент последнего ANALYZE. Между пересчётами таблица продолжает жить: строки добавляются, удаляются, обновляются. Если статистика собиралась неделю назад, а с тех пор в таблицу влили миллион новых заказов с новым статусом refunded, планировщик о существовании этого статуса просто не знает — с его точки зрения такого значения в столбце нет вовсе, либо оно попадает в «прочие» с очень грубой оценкой.

Приближения самой модели. Даже гистограмма — это всего лишь набор границ интервалов с предположением о равномерном распределении значений внутри каждого интервала. Реальное распределение внутри интервала может быть каким угодно — оценка всё равно усредняет.

Всё это не баг конкретной СУБД, а неизбежное следствие того, что точная оценка селективности произвольного условия для произвольных данных потребовала бы либо полного сканирования на каждый запрос (что убивает саму идею индексов и планирования), либо идеально актуальной и бесконечно подробной статистики, которую невозможно поддерживать бесплатно.

Когда оценка расходится с реальностью особенно сильно

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

Массовая загрузка данных. Только что созданная таблица или таблица, в которую одним COPY или batch-INSERT влили большой объём данных, ещё не имеет статистики вообще — если ANALYZE не был запущен явно, autovacuum доберётся до неё не мгновенно. Первые запросы после загрузки планировщик строит вслепую, на устаревшей статистике от предыдущего состояния таблицы, которое могло быть вообще пустым.

Резко меняющееся распределение. Таблица очередей задач, где почти все строки имеют статус done, а горячие запросы идут по WHERE status = 'pending'. Если доля pending-строк то падает почти до нуля, то резко растёт при всплеске нагрузки, статистика, собранная в спокойный момент, не отражает пиковое состояние — и план, оптимальный для редкого статуса, для выборки в четверть таблицы уже не годится.

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

Автовакуум, настроенный на редкий пересчёт. Параметры autovacuum_analyze_scale_factor и autovacuum_analyze_threshold определяют, какая доля изменённых строк должна накопиться, прежде чем autovacuum пересчитает статистику заново. Для очень больших и активно изменяемых таблиц значения по умолчанию могут означать, что перерасчёт происходит реже, чем меняется реальное распределение данных.

Нестандартные типы и выражения. JSONB, полнотекстовый поиск, LIKE '%подстрока%' без якоря в начале — для всего этого построчная статистика по столбцу либо не применяется, либо применяется очень грубо, и планировщик оценивает селективность практически наугад.

Как увидеть ошибку планировщика и что с ней делать

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

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'refunded';

В выводе для каждого узла плана есть rows=N (оценка) и, после выполнения с флагом ANALYZE, фактическое число строк в скобках. Если оценка расходится с фактом на порядок и более — это прямой сигнал, что планировщик строил план на неверных предпосылках, даже если сам план формально логичен для той оценки, которая у него была.

Что делать дальше по порядку, от самого дешёвого к более тяжёлому:

1. Пересчитать статистику вручную. ANALYZE orders; — быстрая операция, не блокирует таблицу надолго. Первое, что стоит сделать после массовой загрузки или удаления данных, не дожидаясь autovacuum.
2. Повысить детализацию статистики для конкретного столбца с сильно неравномерным распределением:

   ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
   ANALYZE orders;

Значение по умолчанию для всей базы задаётся параметром default_statistics_target; per-column SET STATISTICS переопределяет его точечно.

1. Явно описать зависимость между столбцами через CREATE STATISTICS, как показано выше, если проблема в совместных условиях по нескольким полям.
2. Проверить частоту autovacuum-analyze для конкретной горячей таблицы и, при необходимости, задать более агрессивные пороги через ALTER TABLE ... SET (autovacuum_analyze_scale_factor = ...).
3. Проверить актуальность статистики — время последнего ANALYZE видно в pg_stat_user_tables (last_analyze, last_autoanalyze, n_mod_since_analyze).

Отдельная и довольно частая ситуация — когда планировщик вообще не берёт существующий индекс, хотя он «правильный» на первый взгляд: она разобрана в статье про то, как планировщик игнорирует индекс из-за устаревшей статистики. А если тормозит не конкретный запрос, а вся база целиком после импорта данных — это, скорее всего, тот же корень: статистики ещё нет или она не пересчитана. Похожие симптомы разбираются и на стороне MySQL в статье про медленные запросы MySQL — там своя реализация статистики, но логика диагностики та же: сравнить ожидаемое число строк с фактическим.

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

Частые вопросы