План PostgreSQL ошибся на связанных фильтрах: расширенная статистика

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

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

Как изменить решение

CREATE STATISTICS deliveries_city_region_stats
  (dependencies, mcv)
ON city, region FROM deliveries;
ANALYZE deliveries;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM deliveries
WHERE city = 'Казань' AND region = 'Татарстан';

После ANALYZE сравните rows в оценке с actual rows. Цель — более правдоподобная оценка совместного фильтра. Конкретный план может остаться прежним, если он и раньше был достаточно выгодным.

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

Что проверить на своих данных

Сохраните план до CREATE STATISTICS и после ANALYZE на одном наборе данных. Проверьте частую и редкую комбинации. Для демонстрации удобно загрузить данные, где каждый город имеет фиксированный регион, затем добавить немного нарушающих зависимость записей и посмотреть, как меняется оценка.

Не создавайте один объект на все столбцы большой таблицы. Выберите совместные фильтры, которые реально вызывают ошибку оценки. Разные виды статистики предназначены для разных задач; например, оценка числа групп не тождественна зависимости условий. Важен наблюдаемый узел плана с неправильной кардинальностью, а не само присутствие CREATE STATISTICS в миграции.

Практика дальше

Следующая практика — запросы, где правильность зависит от нескольких источников и граничных случаев. В курсе «Продвинутый SQL» вы решаете 109 задач на JOIN, окна и CTE, сверяете отчёты и собираете витрину интернет-магазина. Запросы выполняются в браузере на SQLite с автоматической проверкой.

Посмотреть программу и практику курса

Источник: План PostgreSQL ошибся на связанных фильтрах — официальная документация.

Другие задачи по теме: PostgreSQL: запись, блокировки и планы запросов.