В процессе эксплуатации систем на базе 1С:Предприятие и PostgreSQL системные администраторы часто сталкиваются с парадоксальной ситуацией: две абсолютно идентичные базы данных (поднятые из одного дампа, работающие на одном сервере) демонстрируют разную производительность. В одной базе отчет формируется за секунды, а в другой тот же самый процесс может занимать десятки минут. Причина этого кроется в механизмах планирования запросов PostgreSQL. Рассмотрим подробно, почему так происходит и как привести систему к стабильно высокой скорости работы.
Одной из главных особенностей оптимизатора PostgreSQL является его "чрезмерный оптимизм" при оценке стоимости выполнения вложенных циклов (Nested Loop). Вложенный цикл — это метод соединения двух таблиц, при котором для каждой строки из первой таблицы просматриваются соответствующие строки во второй таблице. Если вторая таблица имеет подходящий индекс, этот метод кажется оптимизатору очень дешевым.
Проанализируем ситуацию, характерную для 1С: платформа генерирует сложные SQL-запросы с огромным количеством подзапросов и временных таблиц. В таких условиях оптимизатор PostgreSQL часто ошибается в оценке количества строк, которые вернут подзапросы. Если он ожидает 10 строк, а на деле их оказывается 1 000 000, выбранный Nested Loop превращает выполнение запроса в катастрофу.
Выясним, как быстро стабилизировать ситуацию. Самым простым и действенным методом (хотя и часто называемым "костылем") является отключение принудительного использования вложенных циклов. Для этого в конфигурационном файле postgresql.conf или непосредственно в сессии выполнения запроса устанавливается параметр:
SET enable_nestloop = off;
Важно понимать, что этот параметр не запрещает вложенные циклы полностью. Он просто устанавливает их стоимость настолько высокой, что оптимизатор выберет их только в самом крайнем случае, когда другие способы соединения (например, Hash Join или Merge Join) технически невозможны. Как показывает практика, для тяжелых отчетов 1С это решение радикально сокращает время выполнения, устраняя тот самый "радикальный тупизм" планировщика.
Когда количество таблиц или соединений в одном запросе превышает определенный порог, PostgreSQL перестает перебирать все возможные варианты планов (так как это заняло бы слишком много времени) и переходит к использованию генетического алгоритма поиска (Genetic Query Optimizer). Рассмотрим параметр geqo_threshold. По умолчанию он равен 12. Это означает, что если в запросе участвует более 12 таблиц, план может стать недетерминированным.
В разных базах, даже с одинаковыми данными, генетический алгоритм может "выбросить" разные результаты поиска пути. Для систем 1С, где запросы часто объединяют десятки таблиц через левые соединения, рекомендуется увеличивать этот параметр:
geqo_threshold = 18
Это заставит планировщик дольше использовать стандартный перебор вариантов, что делает планы запросов более предсказуемыми и стабильными между разными базами.
1С активно использует вложенные подзапросы. То, как PostgreSQL будет их "разворачивать" и объединять с основным телом запроса, регулируется параметрами from_collapse_limit и join_collapse_limit. Если эти значения установлены в стандартные 8, то сложные конструкции 1С не оптимизируются должным образом, и СУБД выполняет их "как есть", что часто ведет к неоптимальным планам.
Для повышения эффективности работы 1С на PostgreSQL рекомендуется увеличить эти лимиты до 20 или более. Это даст оптимизатору больше пространства для маневра при поиске лучшего порядка объединения таблиц.
Посмотрим на параметры, которые напрямую влияют на выбор между сканированием индекса и полным сканированием таблицы. Планировщик рассчитывает стоимость доступа к данным, основываясь на параметрах seq_page_cost (стоимость последовательного чтения) и random_page_cost (стоимость случайного доступа).
По умолчанию random_page_cost равен 4.0, что рассчитано на медленные HDD-диски. Если ваши базы данных размещены на быстрых SSD или NVMe накопителях, эта оценка вводит оптимизатор в заблуждение. Он считает случайный доступ (чтение по индексу) слишком дорогим и может выбрать неэффективный план. Проанализируем настройки и приведем их к современным стандартам для SSD:
random_page_cost = 1.1
Это изменение сделает индексное чтение более "привлекательным" для планировщика, что часто исправляет проблемы с производительностью в базах, где статистика кажется корректной, но планы все равно строятся неудачно.
Сброс статистики, о котором упоминалось в обсуждении, не всегда является панацеей. Проблема может заключаться не в "замусоренности" статистики, а в ее детализации. Стандартное значение default_statistics_target в 100 единиц (корзин гистограммы) может быть недостаточным для огромных таблиц итогов регистров 1С.
Если в одной базе распределение данных чуть более специфично, чем в другой, стандартной статистики может не хватить для правильной оценки селективности условий в ГДЕ. Попробуем увеличить глубину анализа:
ALTER SYSTEM SET default_statistics_target = 500;
-- После изменения обязательно выполним сбор статистики для всей базы
ANALYZE;
Важно помнить, что PostgreSQL использует механизм подготовленных запросов. Первые пять раз запрос выполняется с "индивидуальным" планом (custom plan), построенным под конкретные параметры. На шестой раз система пытается создать "общий" план (generic plan).
Если в одной базе при первых запусках попались параметры, для которых выгоден специфический план, а в другой базе — иные параметры, то "общие" планы в этих базах могут зафиксироваться по-разному. В современных версиях PostgreSQL (начиная с 12-й) мы можем управлять этим процессом через plan_cache_mode. Если вы видите нестабильность, попробуйте установить:
SET plan_cache_mode = force_custom_plan;
Это заставит систему всегда строить план заново под текущие параметры запроса, исключая ошибки, связанные с неудачным "общим" кэшированным планом.
Для исправления ситуации с разными планами запросов в идентичных базах 1С необходимо выполнить следующие шаги:
enable_nestloop = off для сессии или всей базы, чтобы исключить риск катастрофических ошибок при оценке стоимости циклов в подзапросах.random_page_cost до 1.1–1.5.default_statistics_target до 500 и выполнить полный ANALYZE.geqo_threshold, from_collapse_limit и join_collapse_limit до 18–20.Такой комплексный подход не только решит проблему с конкретным отчетом, но и сделает работу всей системы 1С:Предприятие на PostgreSQL более предсказуемой и быстрой.