kb/planner ── Планировщик и оптимизатор ── advanced

Расширенная статистика (CREATE STATISTICS)

По умолчанию планировщик считает колонки независимыми и перемножает селективности - для коррелированных это занижает оценку. CREATE STATISTICS (dependencies, ndistinct, mcv) собирает статистику по группе колонок вместе.

view as markdownaka: create-statistics, multivariate-statistics, column-correlation

Проблема независимости

Планировщик по умолчанию считает условия независимыми: P(A и B) = P(A) × P(B). Для независимых колонок это верно, для коррелированных - грубая ошибка. city = 'СПб' AND country = 'Россия' отбирает столько же строк, сколько одно city = 'СПб', но перемножение даёт заниженную оценку - и под неё выбирается плохой план (см. planner-statistics).

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

sql

CREATE STATISTICS s (dependencies, ndistinct, mcv)

  ON departure, arrival FROM flights;

ANALYZE flights;

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

После CREATE STATISTICS и ANALYZE оценка rows для коррелированных условий приближается к фактической, и план исправляется. Проверить эффект - сравнить estimated и actual rows в EXPLAIN ANALYZE до и после.

Где смотреть

sql

SELECT * FROM pg_stats_ext;

§ команды

bash

CREATE STATISTICS s (dependencies) ON a, b FROM t;

Снять предположение независимости для пары колонок

bash

ANALYZE t;

Собрать расширенную статистику (без ANALYZE она пустая)

bash

SELECT * FROM pg_stats_ext;

Посмотреть собранную расширенную статистику

§ см. также

kb/planner ── Планировщик и оптимизатор ── advanced

Расширенная статистика (CREATE STATISTICS)

view as markdownaka: create-statistics, multivariate-statistics, column-correlation

Проблема независимости

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

sql

CREATE STATISTICS s (dependencies, ndistinct, mcv)

  ON departure, arrival FROM flights;

ANALYZE flights;

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

Где смотреть

sql

SELECT * FROM pg_stats_ext;

§ команды

bash

CREATE STATISTICS s (dependencies) ON a, b FROM t;

Снять предположение независимости для пары колонок

bash

ANALYZE t;

Собрать расширенную статистику (без ANALYZE она пустая)

bash

SELECT * FROM pg_stats_ext;

Посмотреть собранную расширенную статистику