Когда статистический сборщик **PostgreSQL** выдает неверные оценки количества строк для сильно коррелированных многоколонных фильтров, что приводит к использованию **Nested Loop** соединений на больших наборах данных, и какой статистический объект устраняет эту ошибку оценки?

Ответ на вопрос

PostgreSQL поддерживает гистограммы по столбцам в pg_statistic и предполагает статистическую независимость между столбцами при оценке селективности для многоколонных фильтров. Когда вы фильтруете по сильно коррелированным столбцам — таким как city и state_code или make и model в базе данных транспортных средств — планировщик перемножает индивидуальные селективности, значительно недооценив фактическое количество строк. Эта ошибка кардинальности часто заставляет оптимизатор предпочитать Nested Loop соединения вместо Hash или Merge соединений, что приводит к катастрофической производительности на больших таблицах.

Для решения этой проблемы вы создаете объект расширенной статистики с помощью CREATE STATISTICS, который строит данные о множественной корреляции. В частности, тип dependencies отслеживает функциональные зависимости между столбцами, позволяя планировщику понимать, что фильтрация по state_code = 'CA' уже ограничивает city до значений Калифорнии, избегая ошибки умножения.

-- Создать расширенную статистику для коррелированных столбцов
CREATE STATISTICS stats_vehicle_make_model 
ON make, model 
FROM vehicles;

-- Заполнить статистику
ANALYZE vehicles;

Ситуация из жизни

Логистическая платформа столкнулась с проблемами на панели отслеживания отправлений, где соединялась таблица shipments с 50 миллионами строк с таблицей customers. Запрос фильтровал по origin_state и origin_city, где 95% строк для города 'Springfield' на самом деле были в 'IL', но планировщик предполагал, что только 2% отправлений соответствуют обоим условиям независимо. Он оценил 500 строк и выбрал Nested Loop соединение, перебирая миллионы записей клиентов и истекая временем после 90 секунд.

Одним из рассматриваемых решений было полное отключение Nested Loop соединений с помощью SET enable_nestloop = off в сессии. Это заставляло использовать Hash Join и выполняло запрос за 3 секунды, но несет серьезные риски: глобальные изменения конфигурации распространяются по пулам соединений, потенциально ухудшая другие законные планы Nested Loop, которые хорошо работают на малых таблицах с индексированными выборками. Кроме того, это временное решение требовало изменений на уровне приложения для установки параметра перед запросом.

Другой вариант заключался в создании композитного индекса на (origin_city, origin_state). Хотя это улучшило выбор индекса, проблема с недооценкой кардинальности не была решена; планировщик все еще полагал, что из индексации появится мало строк и сохранял стратегию Nested Loop, лишь выполняя её быстрее через покрывающий индекс. Более того, широкий композитный индекс занимал 4 ГБ дополнительного места на диске и замедлял операции записи в высокоскоростной таблице shipments.

В конечном итоге команда развернула расширенную статистику, запустив CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, а затем ANALYZE. Этот подход не потребовал переписывания запросов и добавил незначительные накладные расходы на хранение. После развертывания планировщик правильно оценил 45,000 строк и выбрал Hash Join, снизив задержку запроса до 400 миллисекунд, сохранив при этом оптимальные планы для несвязанных нагрузок.

Что часто упускают кандидаты

Как команда ANALYZE обновляет расширенную статистику, и почему объект статистики может показаться неиспользуемым сразу после создания?

ANALYZE вычисляет расширенную статистику только при явном вызове на целевой таблице или когда автосборщик обрабатывает таблицу после создания объекта статистики. Многие кандидаты считают, что CREATE STATISTICS мгновенно влияет на планирование, но каталожные таблицы pg_statistic_ext и pg_statistic_ext_data остаются пустыми до следующего цикла анализа. Следовательно, планировщик продолжает использовать гистограммы по отдельным столбцам и предположения об независимости, пока ANALYZE shipments; не заполнит данные о множественной корреляции. Вы можете проверить использование, проверив представление pg_stats_ext на наличие ненулевых значений dependencies или ndistinct.

В чем функциональное различие между dependencies и ndistinct в CREATE STATISTICS, и какие шаблоны запросов выигрывают от каждого?

Dependencies фиксируют функциональные отношения, где один столбец определяет другой (например, zip_code определяет city), прямо корректируя оценки селективности WHERE. Ndistinct вычисляет точное количество различных комбинаций для групп столбцов, что улучшает оценки GROUP BY и DISTINCT, а не селективность фильтров. Кандидаты часто путают эти две вещи, создавая dependencies, когда их медленный запрос содержит GROUP BY по коррелированным столбцам, или наоборот. Для оптимальных результатов укажите оба типа: CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).

Почему расширенная статистика может не помочь в запросах, использующих условия OR по коррелированным столбцам?

Расширенная статистика на данный момент помогает только с предложениями AND, где происходит умножение селективности. Когда вы фильтруете с OR (например, city = 'Springfield' OR state = 'IL'), PostgreSQL вычисляет селективность с использованием формулы P(A) + P(B) - P(A ∩ B), и она не может применить коэффициенты зависимости к пересечению, потому что статистика отслеживает совместную селективность для соединений, а не дизъюнкций. Кандидаты часто упускают это ограничение и пытаются использовать CREATE STATISTICS для исправления ошибок кардинальности на основе OR, что требует переписывания запросов (например, разделение на ветви UNION ALL) или применения частичных индексов вместо этого.