Расширенная статистика: исправление ошибок оценки для коррелированных колонок

WHERE-условие фильтрует по country='US' AND region='CA' AND status='shipped'. Планировщик предсказывает 500 совпадающих строк — три независимые вероятности, перемноженные вместе. Реальных строк 50,000. Nested Loop выполняется в 100× лишних раз. Колонки коррелированы, а не независимы. CREATE STATISTICS исправляет это двумя строками SQL.

Почему предположение о независимости ломается

Дефолтная модель многоколоночной селективности планировщика:

P(country='US' AND region='CA' AND status='shipped')
  = P(country='US') × P(region='CA') × P(status='shipped')
  = 0.50 × 0.05 × 0.20
  = 0.005 (0.5%)

Но если country определяет region (каждый CA-заказ в US — функциональная зависимость), реальная селективность:

P(region='CA') × P(status='shipped') = 0.05 × 0.20 = 1.0%

На 100M строках: планировщик оценивает 500,000 строк; реальность — 1,000,000 — ошибка в 2×, которая может склонить Nested Loop к катастрофическому поведению. При более жёстких корреляциях ошибка может быть в 1000×.

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

CREATE STATISTICS (доступен с PG 10) поддерживает три взаимодополняющих вида:

dependencies

Хранит коэффициенты функциональной зависимости между парами колонок. Коэффициент, близкий к 1.0, означает «колонка A почти всегда определяет колонку B». Когда планировщик видит WHERE country='US' AND region='CA', он проверяет зависимость между country и region — если country определяет region с коэффициентом 0.95, планировщик знает, что не нужно перемножать P(country) × P(region); вместо этого он оценивает на основе более селективной из двух.

CREATE STATISTICS stx_orders_geo (dependencies)
  ON country, region, status FROM orders;
ANALYZE orders;

ndistinct

Без этого планировщик оценивает количество уникальных комбинаций (country, region) как n_distinct(country) × n_distinct(region), что резко переоценивает, когда колонки коррелированы. ndistinct хранит реальное количество комбинаций.

Полезно для запросов вида:

SELECT country, region, COUNT(*) FROM orders GROUP BY country, region;

Без ndistinct планировщик может выделить Hash Aggregate, ожидая миллионы групп, тогда как их сотни.

mcv (многоколоночные наиболее частые значения)

Хранит наиболее частые кортежи комбинации колонок напрямую — аналог MCV-списка на уровне колонки, но для пар или троек. Для WHERE country='US' AND status='shipped', если (US, shipped) — частая комбинация, планировщик читает её точную частоту из MCV-списка, а не перемножает маргинальные вероятности.

Доступен с PG 12 и часто является наиболее ценным видом для OLTP-шаблонов запросов.

Статистика выражений (PG 14+)

PG 14 добавил статистику по выражениям:

CREATE STATISTICS stx_orders_lower_email ON LOWER(email) FROM users;
ANALYZE users;

Это позволяет планировщику точно оценивать селективность для WHERE LOWER(email) = 'x' даже без функционального индекса на LOWER(email).

Когда и как применять CREATE STATISTICS

Рабочий процесс:

1. Запустите EXPLAIN ANALYZE на медленном запросе. Найдите узлы, где rows (оценка) отличается от actual rows более чем в 10×.
2. Определите колонки WHERE на этом узле. Проверьте, коррелированы ли они: zip и city, country и region, продукт и категория.
3. Создайте объект статистики:

-- Наиболее частый шаблон: dependencies + mcv вместе
CREATE STATISTICS stx_orders_country_region_status
  (dependencies, mcv)
  ON country, region, status FROM orders;

-- Для точности GROUP BY:
CREATE STATISTICS stx_orders_ndistinct
  (ndistinct)
  ON country, region FROM orders;

-- Запустите ANALYZE для заполнения:
ANALYZE orders;

4. Повторно запустите EXPLAIN ANALYZE. Убедитесь, что rows отслеживает actual rows в пределах 2× на затронутых узлах.

Стоимость хранения: небольшая — килобайты на объект статистики. Улучшение планирования на затронутых запросах: 100–1000×.