Почему PostgreSQL ошибается в планах: статистика и селективность

Планировщик PostgreSQL - штука умная, но не всезнающая. Он принимает решения на основе собранной статистики и заложенных в него эвристик. Когда статистика в порядке, план получается быстрым. Когда в оценках появляется дыра, механизм спотыкается - и вот уже безобидный SELECT уходит в seq scan полуторамиллиардной таблицы вместо молниеносного индекса. Разбираемся, где именно планировщик промахивается, как это увидеть и чем исправить.

Как планировщик PostgreSQL выбирает путь

Стоимостная модель: не реальное время, а условные единицы

Планировщик не умеет замерять реальную скорость выполнения. Он оперирует условными единицами стоимости - cost. Каждая операция (последовательное чтение страницы, обращение к индексу, обработка строки) получает числовой вес. Эти веса настроены параметрами вроде seq_page_cost, random_page_cost, cpu_tuple_cost. Итоговая стоимость плана - сумма стоимостей всех его узлов. Побеждает путь с наименьшей оценкой.

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

Роль статистики: откуда берутся числа в pg_stats

Статистика живёт в системном каталоге pg_statistic, для удобства доступна через представление pg_stats. Команда ANALYZE (ручная или автоматическая через autovacuum) собирает по каждой колонке выборку строк и вычисляет:

долю NULL-значений;
среднюю ширину значения;
число уникальных значений (n_distinct);
список наиболее частых значений и их частоты (MCV - Most Common Values);
гистограмму распределения остальных значений (границы корзин histogram_bounds).

Именно эти данные становятся фундаментом для оценки того, сколько строк пройдёт через условие WHERE. И сразу важное уточнение: ANALYZE работает с ограниченной выборкой (по умолчанию до default_statistics_target * 300 строк), поэтому все показатели - приблизительные. Отсюда и берутся ошибки.

Селективность - мост между условием WHERE и числом ожидаемых строк

Селективность - это доля строк, удовлетворяющих условию. Если в таблице миллион записей, а условие WHERE status = 'active' отсеивает 20% из них, селективность равна 0,2, и планировщик ожидает 200 000 строк. На основе этой оценки он решает, стоит ли использовать индекс, какой способ соединения выбрать и сколько памяти выделить для хеш-таблицы.

Для простых сравнений с константой селективность выводится из MCV и гистограммы. Для операторов, про которые у планировщика нет статистической информации (например, пользовательские функции), применяется фиксированное значение по умолчанию. И это тот самый тонкий лёд, на котором часто всё и ломается.

Почему оценки расходятся с реальностью: четыре корневые причины

Устаревшая или неточная статистика (недособранный ANALYZE)

Самый банальный сценарий: только что загрузили несколько миллионов строк, а ANALYZE ещё не отработал. Планировщик видит старую картину, думает, что в таблице три строки, и выбирает nested loop вместо hash join. Или наоборот: после массового удаления статистика утверждает, что данных полно, а на самом деле таблица почти пуста.

Но даже после ANALYZE оценки могут оставаться неточными. Выборка есть выборка: если реальное распределение сильно неравномерное и редкие значения попали в выборку случайно, n_distinct может занижаться или завышаться. Официальная документация так и говорит: оценки числа уникальных значений «могут быть довольно неточными, особенно при больших таблицах».

Неверный взгляд на многоколоночные фильтры: проблема зависимостей

Когда в запросе есть фильтр по двум и более колонкам, например:

WHERE status = 'shipped' AND created_at >= '2024-01-01'

планировщик по умолчанию считает условия независимыми. Он вычисляет селективность для status = 'shipped', отдельно для created_at >= ... и просто перемножает их. Если в реальности отгруженные заказы чаще всего созданы в последние месяцы, перемножение даёт оценку в десятки раз ниже реальности. Планировщик думает, что строк мало, и выбирает index scan + nested loop, хотя фактически придётся перелопатить горы данных - hash join с последовательным сканированием оказался бы куда быстрее.

Та же беда с GROUP BY по нескольким полям. Без многоколоночной статистики планировщик сильно ошибается в оценке числа уникальных комбинаций, а от этого зависит размер хеш-таблицы и общая стоимость агрегации. Отсюда растут ноги у классической ситуации: EXPLAIN показывает ожидание 10 групп, а по факту их 200 000.

Слепые зоны: выражения, функции и операторы, к которым статистика не привязана

По умолчанию PostgreSQL не собирает статистику по вычисляемым выражениям. Фильтр WHERE lower(email) = 'alice@example.com' или WHERE (data->>'price')::numeric > 100 оперирует результатом функции, для которого нет гистограмм и MCV. Планировщик вынужден применять эвристику «по умолчанию».

Особенно коварно это проявляется с JSONB. Долгое время оператор @> использовал фиксированную селективность, что приводило к жутким перекосам. В современных версиях оператор научился заглядывать в MCV и гистограммы, но при недостаточном statistics_target картина всё равно может оказаться размытой. В итоге планировщик предполагает десяток подходящих документов, а реально их сто тысяч - и уходит в неоправданно дорогой nested loop.

Дефолтные селективности по умолчанию и когда они катастрофически врут

Если планировщик вообще не понимает природу оператора (например, пользовательская функция, неизвестный оператор), он подставляет фиксированные значения селективности. Для операторов равенства это примерно 0.5%, для диапазонных сравнений - порядка 33%. Эти цифры совершенно не привязаны к вашим данным. Если функция на самом деле возвращает 90% строк, планировщик, свято веря в свои полпроцента, выберет index scan и nested loop для мизерного ожидаемого объёма, а в реальности хеш-соединение отработало бы в разы быстрее.

Пример из практики: написали функцию is_premium(customer) и используете её в WHERE. Статистика по телу функции отсутствует. Оценка селективности - всё те же 0.5%. Если премиальных клиентов на самом деле 40% - план будет далёк от оптимального.

Диагностика «плохого плана»: на что смотреть в EXPLAIN и EXPLAIN ANALYZE

Сравнение estimated rows vs actual rows - главный маркер беды

Первый и самый надёжный приём - запустить EXPLAIN ANALYZE и сравнить ожидаемое количество строк (rows) с реальным (actual rows) в каждом узле плана. Расхождение в десятки и сотни раз - почти гарантия, что планировщик принял решение на ложных предпосылках.

Скажем, вы видите:

->  Index Scan using idx_orders_status on orders  (cost=0.29..8.30 rows=1 width=40) (actual time=0.150..124.560 rows=45321 loops=1)
      Index Cond: (status = 'pending')

Планировщик ждал одну строку, а получил 45 тысяч. Дальше по дереву эта ошибка накатывается снежным комом: nested loop, выбранный из-за ожидания одного цикла, превращается в катастрофу с 45 000 обращений к внутреннему индексу. Без EXPLAIN ANALYZE (просто EXPLAIN) вы бы увидели только оптимистичную единицу и гадали, почему запрос тормозит.

Как понять, что проблема именно в неправильной селективности, а не в стоимости сканирования

Иногда медленный план возникает не из-за оценки строк, а из-за того, что стоимостные параметры (random_page_cost, effective_cache_size) не соответствуют реальному железу. Например, на SSD random_page_cost по умолчанию 4.0 сильно завышен, и планировщик избегает индексов, предпочитая seq scan.

Отличить одно от другого можно так: если расхождения rows и actual rows нет, а план всё равно выбран seq scan там, где индекс был бы быстрее, - скорее всего, дело в стоимостной модели. Если же оценка строк в разы разъезжается с реальностью, лечить нужно именно статистику и селективность, а не крутить random_page_cost. Нередко встречаются гибридные случаи, но порядок действий всё равно такой: сначала добиваемся адекватных оценок числа строк, потом настраиваем стоимостные коэффициенты.

Инструменты исправления: от быстрых побед до тонкой настройки

Ручной ANALYZE и его варианты (для одной таблицы, одной колонки - когда помогает)

Самый простой шаг - сразу после массовой вставки или удаления выполнить ANALYZE table_name. Если проблема локализована в конкретной колонке, можно проанализировать только её: ANALYZE table_name (column_name). Это быстро и часто решает проблему внезапно «сломавшегося» плана.

Но если данные обновляются непрерывно и потоково, ручной ANALYZE лишь временный костыль. Нужно либо уменьшать пороги срабатывания autovacuum-анализа, либо настраивать детализацию статистики.

Увеличение детализации статистики: default_statistics_target и колоночные цели

Параметр default_statistics_target (по умолчанию 100) определяет максимальное число элементов в списке MCV и размер гистограммы. Чем больше значение, тем детальнее картина, которую получает планировщик, и тем точнее оценки селективности для неравномерных распределений.

Увеличить его глобально (SET default_statistics_target TO 1000) - грубый инструмент, который замедляет ANALYZE и увеличивает время планирования сложных запросов. Лучше повышать детализацию точечно для проблемных колонок:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;

Это даст более подробный MCV и гистограмму именно там, где нужна точность, не нагружая всю систему. Точное значение подбирается экспериментально - начните с 300–500 и посмотрите на стабильность планов.

Расширенная статистика (CREATE STATISTICS) для коррелированных колонок и групп

Когда проблема в зависимости между колонками, одной лишь детализацией не обойтись. Начиная с PostgreSQL 10 доступна расширенная статистика, которая умеет собирать:

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

Создаётся она так:

CREATE STATISTICS s_orders_status_date (dependencies, mcv) 
  ON status, created_at FROM orders;

После ANALYZE планировщик начинает видеть, что status='shipped' и created_at в последние месяцы идут рука об руку, и перестаёт занижать оценку в сотни раз. Это один из самых мощных рычагов исправления многоколоночных ошибок.

Статистика для выражений: зачем нужны expression indexes и статистика по функциональным индексам

Если вы часто фильтруете по функции, создайте индекс на выражение - и планировщик автоматически получит статистику для этого выражения. Например:

CREATE INDEX idx_lower_email ON users (lower(email));

После этого фильтр WHERE lower(email) = ... обзаведётся честными MCV и гистограммами, а не дефолтными догадками. Даже если индекс не нужен для сканирования, он даёт статистическую подсказку. В крайнем случае можно собрать статистику принудительно через ALTER TABLE ... ALTER COLUMN expr SET STATISTICS, но индекс на выражение - более естественный путь.

Аварийные и продвинутые приёмы: заморозка плана, подсказки для join, изменение табличных параметров

Когда исправить корень проблемы быстро нельзя, применяют временные обходные манёвры:

Заморозка плана через pg_hint_plan (стороннее расширение) или через переписывание запроса с выключением определённых типов сканов (SET enable_seqscan = off в рамках сессии). Это грубые рычаги, их стоит использовать только как аварийный переключатель, пока вы чините статистику.
Коррекция табличных параметров - random_page_cost, effective_cache_size, seq_page_cost. Если SSD-массив, снижение random_page_cost до 1.0–1.5 часто делает индексные сканы более привлекательными, но выполнять такое нужно с оглядкой на весь экземпляр.
Переписывание запроса - иногда можно разбить запрос на части или заменить OR на UNION, чтобы дать планировщику более точные оценки. Этот метод работает без изменения конфигурации, но усложняет поддержку.

Тактика предотвращения: как перестать гадать и начать жить

Autovacuum и его настройки для поддержания статистики в живом состоянии

Autovacuum не только чистит мёртвые строки, но и запускает ANALYZE. Параметры autovacuum_analyze_scale_factor и autovacuum_analyze_threshold определяют, как часто он просыпается. По умолчанию для анализа порог - 10% изменённых строк таблицы. Если таблица большая и обновляется волнами, 10% могут набегать слишком медленно, и статистика будет хронически отставать.

Для активно меняющихся таблиц стоит снизить scale_factor (например, до 0.01) или задать его на уровне конкретной таблицы через ALTER TABLE ... SET (autovacuum_analyze_scale_factor = 0.01). Главное - не впадать в другую крайность и не заставлять autovacuum анализировать одну и ту же таблицу непрерывно.

Мониторинг, который предупредит о деградации планов раньше пользователей

Лучший друг стабильных планов - слежение за расхождением ожидаемых и фактических строк. Можно периодически снимать EXPLAIN ANALYZE на критичных запросах и сравнивать rows с actual rows. Либо использовать расширение pg_stat_statements в связке с auto_explain, настроенным на запись планов медленных запросов. Как только в логах появляется план с разлётом оценок на порядки - пора смотреть статистику и, вероятно, запускать ANALYZE или донастраивать расширенную статистику.

FAQ: короткие ответы на частые боли

Почему EXPLAIN показывает index scan, а EXPLAIN ANALYZE использует seq scan?
EXPLAIN без ANALYZE не выполняет запрос и опирается только на оценки планировщика. Если планировщик недооценил число строк, он может посчитать index scan дешёвым и включить его в план. Реальное выполнение выявляет, что строк гораздо больше, и может либо всё равно использовать seq scan (если стоимость индекса с учётом реального объёма оказалась выше), либо план просто меняется из-за других факторов, хотя EXPLAIN ANALYZE показывает то, что реально выполнялось. Часто расхождение планов - первый звоночек к тому, что статистика врёт.

Как часто PostgreSQL автоматически обновляет статистику?
По умолчанию autovacuum запускает ANALYZE на таблице, когда с момента последнего анализа изменилось больше 10% строк (autovacuum_analyze_scale_factor) или набралось 50 изменённых строк (autovacuum_analyze_threshold). На практике частота зависит от интенсивности изменений. При массовых вставках порог срабатывает почти сразу, но если таблица растёт медленно, анализа можно ждать долго.

Что именно собирает команда ANALYZE и как это посмотреть?
ANALYZE просматривает случайную выборку строк и заполняет pg_statistic: количество уникальных значений, список частых значений с частотами, гистограмму, долю NULL. Посмотреть собранное можно запросом к pg_stats с указанием схемы, таблицы и колонки.

Почему после массового INSERT или DELETE план упал в производительности?
После массовой вставки статистика могла не успеть обновиться, и планировщик считает таблицу пустой (или наоборот - переоценивает число строк после удаления). Отсюда выбор неверного типа соединения или сканирования. Ручной ANALYZE восстанавливает адекватные оценки.

Что такое расширенная статистика и когда её нужно создавать?
Расширенная статистика (CREATE STATISTICS) собирает данные о зависимостях между колонками, о числе уникальных комбинаций и о многоколоночных частых значениях. Она нужна, когда в запросах регулярно фильтруют или группируют по нескольким колонкам, а оценки планировщика ошибаются в разы из-за того, что колонки не независимы.

Какое значение default_statistics_target поставить? Чем опасно слишком большое значение?
Универсальной цифры нет. Для большинства нагрузок дефолтные 100 неплохи. Если на отдельных колонках видны ошибки селективности, можно повысить до 300–1000 через ALTER COLUMN SET STATISTICS. Слишком высокое глобальное значение замедляет ANALYZE и увеличивает время планирования для сложных запросов, поэтому точечная настройка предпочтительнее.

Почему фильтр по двум колонкам SELECT … WHERE status = 'shipped' AND created_at > '2024-01-01' может быть оценён неверно?
Планировщик предполагает, что условия независимы, и перемножает их селективности. Если отгруженные заказы коррелируют с датой создания, реальное число строк будет гораздо больше перемноженной оценки. Решение - расширенная статистика с dependencies или mcv.

Поможет ли переписывание запроса (переформулировка WHERE), чтобы обмануть плохие оценки?
Временно - да. Например, замена OR на UNION даёт планировщику отдельные оценки для каждой части, которые могут быть точнее. Но это скорее тактический костыль. Лучше исправить статистику, ведь переписанный запрос сложнее поддерживать.

Заключение

Плохой план - почти всегда следствие плохой оценки числа строк. Планировщик не глуп, он честно перемножает те цифры, которые вы ему дали. Поэтому первым делом загляните в разницу между rows и actual rows в выводе EXPLAIN ANALYZE. А дальше по очереди: анализируете вручную, повышаете детализацию статистики на горячих колонках, добавляете расширенную статистику для корреляций и не забываете про индексы на выражениях. Эти шаги закрывают абсолютное большинство проблем с выбором плана. И самое надёжное - подружите EXPLAIN ANALYZE с мониторингом, чтобы узнавать о деградации планов не от разгневанных пользователей, а из собственных графиков.

Почему PostgreSQL выбирает медленный план: статистика, корреляции и фатальные ошибки оценки