COALESCE искажает оценку строк PostgreSQL: как исправить план без правки запроса 1С

Индекс есть, но PostgreSQL всё равно читает таблицу последовательно или выбирает тяжёлое соединение. В условии запроса стоит COALESCE(column, value) = constant. Планировщик оценивает уже не столбец, а выражение.

Без отдельной статистики выражения PostgreSQL закладывает селективность 0,5%. Для таблицы на 10 млн строк получается:

10 000 000 × 0,005 = 50 000 строк

Под эти 50 тысяч строк планировщик и строит план. Но условие может вернуть пять строк или два миллиона. Обычная статистика столбца такого расхождения не исправит.

Администратору нужно найти неверную оценку, создать статистику точного выражения и снять новый план. Запрос 1С менять не придётся.

Почему статистика столбца не работает внутри COALESCE

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

Но условие с COALESCE обращается не к самому столбцу:

WHERE COALESCE(_Fld1234RRef, '\x00000000000000000000000000000000'::bytea)
 = '\xAABBCCDDEEFF00112233445566778899'::bytea

Статистика _Fld1234RRef описывает значения поля. О распределении результата COALESCE(...) она ничего не знает.

В учебнике RockData по оценке селективности выражений показано поведение PostgreSQL для условия «вызов функции равен константе». Если статистики выражения нет, планировщик берёт оценку 0,5%. Документация PostgreSQL 18 в разделе CREATE STATISTICS описывает отдельный объект статистики для таких выражений.

Индекс ускоряет доступ к строкам, но не исправляет оценку их количества. Сначала планировщик решает, сколько строк получит. Затем выбирает способ чтения и соединения.

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

Сначала исключите устаревшую статистику

Не создавайте новый объект статистики после первого медленного запуска. Сначала обновите сведения о таблице:

ANALYZE public.table_name;

Этот шаг особенно нужен после массовой загрузки. В материале BSwen о статистике планировщика указано, что COPY сам не обновляет статистику таблицы. Автоматический ANALYZE запускается, только когда число изменений достигает настроенного порога.

После ANALYZE снова получите фактический план:

EXPLAIN (ANALYZE, BUFFERS)
SELECT...
FROM public.table_name
WHERE COALESCE(column_name, replacement_value) = constant_value;

EXPLAIN ANALYZE не имитирует выполнение, а запускает запрос. На боевой базе сначала проверьте, меняет ли команда данные. Для UPDATE, DELETE и INSERT используйте копию базы либо транзакцию с последующим ROLLBACK, если побочные действия запроса это допускают.

Найдите в плане узел с фильтром COALESCE. Сопоставьте расчётное rows с фактическим actual rows:

Seq Scan on table_name
 (cost=... rows=50000...)
 (actual time=... rows=1842390 loops=1)
 Filter: (COALESCE(...) =...)

Здесь планировщик ждал 50 тысяч строк, а получил 1 842 390. Ошибка появилась ещё до выполнения запроса. Время узла показывает последствие, а разница между rows и actual rows объясняет выбор неудачного плана.

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

Создайте статистику точного выражения

Статистика выражений появилась в PostgreSQL 14. Сервер не создаёт такие объекты автоматически. Нужное выражение администратор задаёт через CREATE STATISTICS.

Для выражения из запроса команда выглядит так:

CREATE STATISTICS st_table_coalesce_ref
ON (
 COALESCE(
 _Fld1234RRef,
 '\x00000000000000000000000000000000'::bytea
 )
)
FROM public.table_name;

В имени объекта укажите таблицу и выражение. Тогда статистику будет проще найти после обновления конфигурации 1С или изменения структуры базы.

Команда создаёт объект, но ещё не наполняет его данными. Следом запустите:

ANALYZE public.table_name;

RockData отдельно указывает на точное совпадение выражений. В статистике и запросе должны совпадать функция, порядок аргументов, значение замены и приведения типов.

Например, эти выражения нельзя считать одной статистической сущностью:

COALESCE(column_name, 0)

COALESCE(column_name, 0::bigint)

После внутренних преобразований итоговый тип может совпасть. Но строить диагностику на этом предположении не нужно. Скопируйте выражение целиком из фактического плана или SQL-запроса.

Если 1С формирует несколько вариантов COALESCE для одного столбца, статистика первого выражения не покроет остальные. Начните с варианта, у которого сильнее всего расходятся rows и actual rows.

Проверьте объект и собранные данные

По документации PostgreSQL 18 метаданные расширенной статистики хранятся в pg_statistic_ext. Проверьте имя объекта, таблицу и выражение:

SELECT
 se.oid,
 se.stxname,
 se.stxrelid::regclass AS table_name,
 pg_get_statisticsobjdef AS definition
FROM pg_statistic_ext AS se
WHERE se.stxname = 'st_table_coalesce_ref';

Строка в результате подтверждает лишь то, что объект создан. Сведения, собранные для выражения, смотрите в pg_stats_ext_exprs:

SELECT
 schemaname,
 tablename,
 statistics_name,
 expr,
 null_frac,
 n_distinct,
 most_common_vals,
 most_common_freqs
FROM pg_stats_ext_exprs
WHERE statistics_name = 'st_table_coalesce_ref';

Представление pg_stats_ext_exprs показывает статистику, которую ANALYZE собрал для выражения. Если первый запрос нашёл объект, а второй не вернул строк, проверьте права и повторите ANALYZE.

Не судите об исправлении только по времени запроса. На него влияют кэш, параллельная нагрузка, блокировки и состояние дисков. Смотрите на два признака: расчётное число строк приблизилось к фактическому, а новая оценка изменила план.

Сравните планы до и после

Повторно снимите план той же командой и с теми же параметрами:

EXPLAIN (ANALYZE, BUFFERS)
SELECT...
FROM public.table_name
WHERE COALESCE(column_name, replacement_value) = constant_value;

Общая стоимость плана здесь мало что объяснит. Сравнивайте одинаковые узлы и выпишите четыре значения:

Исправление видно по сближению расчётных и фактических строк. Сама по себе смена Seq Scan на Index Scan ничего не доказывает. Обратная смена — тоже.

Если оценка стала точнее, а запрос по-прежнему тормозит, поднимайтесь по плану выше. Начиная с PostgreSQL 12 поведение WITH зависит от использования CTE и указаний MATERIALIZED или NOT MATERIALIZED. Порядок проверки описан в материале про правила материализации CTE в PostgreSQL.

Для системной подготовки пригодится маршрут изучения PostgreSQL 18 перед настройкой базы 1С. В нём собраны работа с EXPLAIN, pg_stat_statements и проверка параметров на стенде.

Когда увеличить объём статистики

Если выборка плохо описывает распределение выражения, повысьте цель созданного объекта:

ALTER STATISTICS st_table_coalesce_ref SET STATISTICS 500;
ANALYZE public.table_name;

Документация PostgreSQL предусматривает для этого команду ALTER STATISTICS. Значение 500 — не готовая рекомендация для любой базы. Это стартовая проверка с большим объёмом сведений по сравнению с меньшей целью.

После изменения снова сопоставьте rows и actual rows. Если они не сближаются, дальнейшее повышение цели может только замедлить ANALYZE и увеличить объём системного каталога.

Не копируйте значение цели из одной базы в другую вслепую. Распределение ссылок, доля NULL и частота значений зависят от содержимого конкретной информационной базы.

Где статистика выражения не поможет

Расширенная статистика влияет на оценку, только если планировщик узнаёт выражение из запроса. Другой аргумент COALESCE, новое приведение типа или дополнительная функция требуют отдельной проверки.

Она не заменяет свежую статистику всей таблицы. Если после загрузки данных ANALYZE не запускался, сначала обновите обычную статистику.

Есть и ограничение для соединений. Документация PostgreSQL 18 предупреждает: расширенная статистика пока не участвует в оценке селективности условий соединения таблиц. Если ошибка возникает на JOIN, статистика выражения в фильтре не гарантирует точную оценку самого соединения.

Индекс отвечает за другую часть работы. Он даёт способ получить строки, но планировщик выбирает этот способ по ожидаемому объёму. Пока оценка неверна, PostgreSQL не обязан использовать индекс.

Порядок проверки

Если запрос 1С фильтрует данные через COALESCE, а расчётное число строк расходится с фактическим, проверьте план по порядку:

1. Выполните ANALYZE для таблицы и исключите устаревшую статистику.
2. Снимите EXPLAIN (ANALYZE, BUFFERS) с теми же параметрами запроса.
3. Найдите узел с COALESCE и сравните rows с actual rows.
4. На PostgreSQL 14 или новее создайте статистику точного выражения.
5. Снова выполните ANALYZE и проверьте pg_stats_ext_exprs.
6. Повторите фактический план и сравните оценки одного узла до и после.

Рабочий критерий один: планировщик точнее оценивает число строк. Если оценка исправилась, а задержка осталась, переходите к следующему проблемному узлу — CTE, соединению, сортировке или повторному чтению.