Статистика планировщика

Статистику собирает команда ANALYZE (её же запускает autovacuum). Она не читает всю таблицу - берёт случайную выборку (по умолчанию около 30 000 строк) и по ней строит сводку, которую кладёт в системную таблицу pg_statistic. Читать её удобнее через view pg_stats:

ANALYZE flights;

SELECT attname, null_frac, n_distinct, most_common_vals, histogram_bounds

FROM pg_stats WHERE tablename = 'flights';

Здесь всё, на что планировщик опирается о колонке: доля NULL, число различных значений, список самых частых значений и гистограмма распределения остальных. Это и есть его картина мира. Если она устарела - решения будут неверные, хотя сама модель стоимости работает безупречно.

Для каждой колонки pg_stats хранит несколько ключевых полей:

Селективность любого условия планировщик собирает из этих кусков. Для col = 'X', если 'X' есть в MCV, он берёт частоту прямо оттуда - точное число. Если нет в MCV - считает, что значение «обычное», и оценивает как (1 − сумма частот MCV − null_frac) / число оставшихся различных значений.

Распределения почти никогда не равномерны: пара значений встречается на каждом шагу, остальные - редко. Если бы планировщик считал все значения одинаково частыми, он бы грубо ошибался на «популярных». Поэтому самые частые значения вынесены в отдельный список MCV с точными частотами, а гистограмма строится только по остальным.

Это разделение и даёт точность. Запрос WHERE departure = 'SVO', где SVO - частое значение, оценивается по его записи в MCV почти идеально. Запрос по редкому значению, которого в MCV нет, оценивается через равномерное предположение по «хвосту». Сколько значений попадёт в MCV, регулирует default_statistics_target (по умолчанию 100): больше target - длиннее MCV и гистограмма, точнее оценки, но дороже ANALYZE.

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

SELECT histogram_bounds FROM pg_stats

WHERE tablename = 'tickets' AND attname = 'flight_id';

-- {1,6,11,17,22,28,...,50}

Если в массиве 101 граница, это 100 корзин, и в каждой - примерно 1% строк. Поэтому там, где значения плотные, корзины узкие, а где редкие - широкие. Такое устройство даёт ровную точность по всему диапазону: на любом участке планировщик знает, что между двумя соседними границами лежит ровно 1% данных.

Гистограмма позволяет оценить col > X без обращения к данным. Логика: найти, в какую корзину попадает X, и сложить доли всех корзин правее.

Пусть 100 корзин (каждая - 1% строк), а граница X приходится ровно между 80-й и 81-й границей. Тогда правее лежит 20 корзин - примерно 20% строк. Селективность col > X ≈ 0.20, и для таблицы в 10 000 строк планировщик оценит rows ≈ 2000.

EXPLAIN SELECT * FROM tickets WHERE flight_id > 40;

-- rows=... сверь с долей корзин правее значения 40 в histogram_bounds

Если предсказанная по гистограмме доля совпала с rows в плане - ты только что повторил работу планировщика. Если нет - скорее всего статистика устарела. Подробности расчёта - в selectivity-crossover.

Статистика - снимок на момент ANALYZE. Между запусками данные меняются, а сводка нет. Самый болезненный случай - массовая загрузка: залили миллион строк, и до следующего ANALYZE планировщик думает, что таблица пустая или прежнего размера.

EXPLAIN SELECT * FROM tickets WHERE flight_id = 7;  -- rows=1 (по старой статистике)

-- ... загрузили данные ...

ANALYZE tickets;

EXPLAIN SELECT * FROM tickets WHERE flight_id = 7;  -- rows=900 (после обновления)

Симптом устаревшей статистики - большое расхождение между rows в EXPLAIN и фактическим числом в EXPLAIN ANALYZE. Первое, что делают при «непонятно плохом плане», - запускают ANALYZE и смотрят, не починилось ли. Autovacuum делает это сам по порогам, но сразу после большой загрузки ANALYZE стоит позвать руками.

По умолчанию планировщик считает колонки независимыми. Селективность двух условий он перемножает: P(A и B) = P(A) × P(B). Когда колонки и правда независимы - это верно. Когда коррелированы - грубая ошибка.

Классика - город и страна. city = 'СПб' имеет селективность, и country = 'Россия' имеет селективность. Но СПб всегда в России, поэтому условие city = 'СПб' AND country = 'Россия' отбирает ровно столько же строк, сколько одно city = 'СПб'. Планировщик же перемножит две доли и оценит совместное условие в разы заниженно - а на заниженной оценке выберет неподходящий план.

Это не баг, а сознательное упрощение: хранить совместное распределение всех пар колонок невозможно. Но там, где корреляция есть и мешает, её можно явно подсказать.

CREATE STATISTICS собирает статистику по группе колонок вместе и снимает предположение о независимости там, где это нужно.

CREATE STATISTICS flights_dep_arr (dependencies, ndistinct)

  ON departure, arrival FROM flights;

ANALYZE flights;

Три вида расширенной статистики:

• dependencies - функциональные зависимости (departure почти определяет arrival): планировщик перестаёт перемножать их селективности;
• ndistinct - число различных комбинаций группы колонок (важно для GROUP BY по нескольким колонкам);
• mcv - частые комбинации значений группы, как MCV, но по нескольким колонкам сразу.

После CREATE STATISTICS и ANALYZE оценка rows для коррелированных условий приближается к реальной, и план исправляется. Подробнее - в extended-statistics.