Анализ планов выполнения SQL запросов PostgreSQL с помощью DeepSeek и философской инструкции: кейс снижения hit ratio с 93,5% до 81,8%

Экспериментальная верификация эффективности промптов для нейросетевой модели DeepSeek при анализе производительности СУБД PostgreSQL на основе отчётов pgpro_pwr.

DeepSeek: производительность PostgreSQL под контролем

Max: PG_EXPECTO

GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL

Philosophical_instruction_BETA_v5.1.md - Философское ядро + процедурный скелет автономного AI-агента с встроенной самопроверкой. Эпистемология, этика честности, научный метод, think pipeline (CoVe, ToT, Pre-Mortem, Red Teaming, 7 Грехов). Максимальная правдивость, защита от галлюцинаций и prompt injection.

GitFlic - pg_expecto - статистический анализ производительности и ожиданий СУБД PostgreSQL

Глоссарий терминов | Postgres DBA | Дзен

Предисловие

В работе представлены результаты применения коллекции специализированных промптов на русском языке, разработанных для нейросетевой модели DeepSeek, с целью выявления причин деградации производительности PostgreSQL при переходе с версии 15.14 на 17.7 и изменении ряда конфигурационных параметров. В условиях невозможности использования разностного отчёта pgpro_pwr из-за несовместимости идентификаторов запросов (Query ID) был реализован подход к сравнительному анализу двух независимых отчётных периодов путём сопоставления метрик нагрузки, конфигурации и планов выполнения с помощью запросов к нейросети DeepSeek. Основное внимание уделено проверке гипотезы о влиянии настроек планировщика (online_analyze, generic_plan_fuzz_factor, JIT) на рост времени ввода-вывода и снижение эффективности буферного кеша.

Задача

Проведение экспериментальной верификации набора текстовых подсказок (промптов) для нейросетевой модели DeepSeek, применяемых с целью анализа отчётных данных, формируемых pgpro_pwr.

Проблема СУБД PostgreSQL:

Начало цикла

Текущий статус

⚠️Кэш-хит для запросов (query-level hit ratio, рассчитан из Load distribution)⬇️

• 93.5% → 81.8% (снижение на 11.7 п.п.)
• (Подтверждено) Рассчитан как (Blocks fetched – Shared blocks read) / Blocks fetched.

⚠️Значительное падение доли чтений из буферного кэша.⚠️

• ℹ️Общий кэш-хит базы данных DB-4 (предоставлен пользователем)
• 94.43% → 86.97% (снижение на 7.46 п.п.)⬇️
• (Подтверждено) Общий hit ratio упал заметно меньше, чем hit ratio по запросам. Это указывает, что деградация сконцентрирована именно в пользовательской нагрузке, а не в системных процессах (фоновая запись, контрольные точки, vacuum).⚠️

Входные данные для анализа

• Период 1 : pgpro_pwr_5635_5636.clear.html - 2026-04-10 09:00-10:00 : Версия 15
• Период 2 : pgpro_pwr.93-94.clear.html - 2026-04-24 09:00-10:00 : Версия 17

Уточнение:

Использование разностного отчета pgpro_pwr невозможно по причине смены хеш-значений для "Query ID"

Коллекция промптов для нейросети DeepSeek (на русском языке), предназначенных для анализа и оптимизации производительности СУБД PostgreSQL.

deepseek-pg-perf-prompts

1. Формирование сравнительной таблицы параметров СУБД-> table.result.txt

2. Получение профилей нагрузки-> load_distribution.1.txt / load_distribution.2.txt

3.Анализ конфигурационных параметров СУБД и распределения нагрузки, формулировка гипотезы о проблеме -> analyze.txt

⚠️Важное уточнение

Особенность формировании гипотезы :"при принятом допущении, что характер нагрузки на СУБД в периоды отчетов "отчет-5635_5636" и "отчет-93-94" кардинально не изменился."⚠️

Гипотеза о влиянии конфигурации на показатели Load distribution

Принятое допущение: характер нагрузки (типы запросов, их распределение, интенсивность) в периоды отчётов кардинально не изменился.

ℹ️Гипотеза (основная):

Сочетание отключения online_analyze, выключения JIT-компиляции, изменения generic_plan_fuzz_factor и перехода на версию 17.7 с иной моделью стоимости привело к выбору планировщиком менее оптимальных планов запросов – в частности, к более частому использованию последовательных сканирований вместо индексных. Это вызвало резкое увеличение физических чтений (shared blocks read), падение hit ratio, рост времени ввода-вывода и, как следствие, общего времени выполнения. Рост объёма WAL может частично объясняться тем, что возросшее количество физических чтений вынуждает чаще вытеснять «грязные» страницы из буферного кеша, генерируя дополнительный WAL, либо указывает на то, что фактическая нагрузка всё же изменилась.

4. Анализ планов выполнения SQL запросов по разделу "Top SQL by I/O wait time" , уточнение гипотезы => analyze-Top_SQL.txt

⚠️Важное уточнение

Особенность формировании гипотезы :"при принятом допущении, что характер нагрузки на СУБД в периоды отчетов "отчет-5635_5636" и "отчет-93-94" кардинально не изменился."⚠️

Анализ планов по разделу "Top SQL by elapsed time"

Сравнительный анализ планов выполнения по разделу "Top SQL by I/O wait time"

Сопоставление выполнено по Query ID из файла queryid‑2.txt. Сравниваются три запроса, для которых установлено текстовое совпадение между периодами «отчет‑5635_5636» (PG 15.14, старые настройки) и «отчет‑93‑94» (PG 17.7, новые настройки).

1️⃣INSERT INTO _InfoRg16813

• Первый период (Query ID 7f58891f6efcac66): план – Insert on public._inforg16813 -> Result.
• Второй период (Query ID 11882bc12ecb7128): план идентичен первому периоду.
• ☑️Вывод: изменений нет. (Подтверждено)

2️⃣INSERT INTO BinaryData

• Первый период (Query ID ece33a053752847): план – Insert on public.binarydata -> Result.
• Второй период (Query ID 74fb45dc12c18919): план идентичен.
• ☑️Вывод: изменений нет. (Подтверждено)

3️⃣SELECT … FROM _InfoRg12488 … ORDER BY … LIMIT $5

Первый период (Query ID b932f4ddf25919b4): зафиксированы два плана:

• План 1: Limit -> Sort -> Index Scan using _inforg12488_1 (с условием по _fld12490rref = $1 и дополнительными фильтрами).
• План 2: Limit -> Sort -> Bitmap Heap Scan -> Bitmap Index Scan on _inforg12488_1 (Recheck Cond по тому же полю).

Второй период (Query ID 12e2db113ff929b0): зафиксированы три плана:

• План 1: Limit -> Sort -> Seq Scan on _inforg12488 (последовательное сканирование с фильтром).
• План 2: Limit -> Sort -> Bitmap Heap Scan -> Bitmap Index Scan on _inforg12488_1 (как в первом периоде).
• План 3: Limit -> Sort -> Index Scan using _inforg12488_1 (как в первом периоде).

☑️Вывод: во втором периоде появился вариант плана с Seq Scan, отсутствовавший в первом периоде. Одновременно увеличилось общее число вариантов плана для этого запроса (с 2 до 3). (Подтверждено)

Обобщение по планам

• Для двух INSERT-операторов планы не изменились.
• Для единственного SELECT-оператора, по которому возможно сравнение, во втором периоде планировщик дополнительно рассматривает (и, следовательно, может выбирать) последовательное сканирование, тогда как в первом периоде использовались только методы доступа по индексу.
• ☑️Это прямое свидетельство сдвига в поведении оптимизатора при новых настройках.

Дополнение гипотезы о влиянии конфигурационных параметров на показатели Load Distribution

Исходная гипотеза (analyze.txt, раздел 3) связывала падение hit ratio, рост физических чтений и времени ввода-вывода с изменением планов запросов в пользу менее селективных методов доступа (seq scan).

☑️Анализ планов выполнения подкрепляет эту гипотезу конкретным примером: для запроса, который в первом периоде всегда использовал индекс, во втором периоде стал доступен план с последовательным сканированием таблицы. Вероятно, этот случай не единичен, и подобные изменения затронули значительную часть нагрузки, что и вызвало наблюдаемый рост shared blocks read и падение hit ratio.

Уточнённая последовательность рассуждений и тезисов

• Количество выполненных запросов в DB‑4 практически не изменилось (+0.7%), однако общее время выполнения выросло в 3.3 раза, а время ввода-вывода – в 13.4 раза. Основной вклад в деградацию дали возросшие физические чтения. (Подтверждено)
• Cache hit ratio снизился с 93.5% до 81.8% при стабильном объёме разделяемой памяти. Это исключает уменьшение буферного пула как причину и указывает на изменение паттернов доступа к данным. (Подтверждено)
• Для одного из немногих доступных для сравнения запросов зафиксировано появление в новом периоде плана с Seq Scan, которого не было ранее. (Подтверждено)
• Аналогичные изменения могли произойти и для других запросов, составляющих основную нагрузку, что объясняет кратный рост физических чтений и падение hit ratio. (Вероятно)
• Сдвиг в планах согласуется с отключением online_analyze (устаревание статистики), снижением generic_plan_fuzz_factor (уменьшение штрафа для обобщённых планов), выключением JIT, а также с переходом на новую версию PG с иной моделью стоимости. (Вероятно)
• Сокращение использования временных блоков при одновременном росте чтений может указывать на замену операций с записью на диск (например, хеш-соединений) на операции с активным чтением данных (сортировка или merge join), что согласуется с общим ростом физических чтений. (Вероятно)
• Рост объёма WAL на 73% при неизменном количестве запросов частично объясняется увеличением фоновой записи «грязных» страниц, вытесняемых из-за возросшего потока чтений, но также может свидетельствовать о реальном увеличении объёма изменяемых данных, что требует отдельной проверки. (Вероятно)

☑️Таким образом, основная гипотеза не изменилась, но получила прямое подтверждение на уровне конкретного плана запроса. Вероятность гипотезы повышается.

Дополнение способов подтверждения и опровержения гипотезы

С учётом данных о планах выполнения добавляются следующие пункты (к уже перечисленным в analyze.txt):

☑️Для подтверждения:

• 📋Проанализировать распределение реально использовавшихся планов во втором периоде. Если через pg_stat_statements или расширенные отчёты pgpro_pwr доступна статистика по каждому planid (например, calls, total_time, shared_blks_read), можно установить долю запросов, выполненных с Seq Scan, и сравнить её с первым периодом. Значительный рост этой доли напрямую подтвердит гипотезу. (Предположение)
• 📋Для запроса SELECT … FROM _InfoRg12488 (Query ID 12e2db113ff929b0) оценить фактическую селективность условия _fld12490rref = $1. Если она высока (т.е. возвращается мало строк), выбор Seq Scan однозначно неоптимален и является прямым следствием ошибки планировщика при новых параметрах — это будет сильным подтверждением гипотезы. (Предположение)
• ❗[TO DO] Временно вернуть параметры online_analyze = on и generic_plan_fuzz_factor = 1 и проверить, исчезнет ли Seq Scan-план для этого запроса и восстановятся ли общие показатели hit ratio и I/O time (A/B-тестирование). (Предположение)[TO DO]❗

Для опровержения:

• ❓Если статистика использования планов покажет, что Seq Scan-план для данного запроса практически не выбирается (calls близко к нулю), то его появление в списке планов не повлияло на нагрузку, и гипотеза о массовом переходе на Seq Scan теряет основание. Потребуется искать иные причины роста физических чтений. (Предположение)
• ❓Если auto_explain (уже включён) зафиксирует, что в большинстве медленных запросов второго периода используются индексные доступы, а рост чтений вызван, например, обработкой существенно большего объёма данных (изменившийся характер нагрузки), то гипотеза о влиянии параметров планирования будет опровергнута. (Предположение)
• ❓Если значение enable_seqscan в первом периоде было off (или иным образом ограничено), а во втором — on, то различие в планах объясняется тривиальной настройкой, а не сложным влиянием online_analyze и generic_plan_fuzz_factor. (Неизвестно — значение enable_seqscan в отчётах не зафиксировано, требуется проверка.) (Предположение)

Недостающие данные, критичные для углублённого анализа (дополнительно к разделу 4 analyze.txt):

• Значения enable_seqscan, enable_indexscan, enable_bitmapscan в обоих периодах. [Информация доступна для анализа]☑️
• Статистика использования отдельных планов (planid) для ключевых запросов: количество вызовов, общее время, прочитанные блоки.[Информация доступна для анализа]☑️
• Оценка селективности предикатов для проблемного запроса (значения параметров $1, $6 и т.д.) и распределение данных в таблице _InfoRg12488.
• Полный список запросов с наибольшим вкладом в total_time и shared blocks read в обоих периодах и их планы, чтобы оценить масштаб явления. [Информация доступна для анализа]☑️

Общий технический итог

Установлено, что при практически неизменном количестве выполненных запросов (рост на 0,7%) общее время выполнения увеличилось в 3,3 раза, а время ввода-вывода – в 13,4 раза, что сопровождалось падением коэффициента попаданий в буферный кеш с 93,5% до 81,8%. Сравнительный анализ планов выполнения для трёх текстуально совпадающих запросов выявил появление во втором периоде варианта с последовательным сканированием таблицы для SELECT-оператора, при том что в первом периоде использовались только индексные методы доступа. Данный факт, согласующийся с изменениями конфигурации (online_analyze.enable = off, generic_plan_fuzz_factor = 0,9, jit = off), подтверждает гипотезу о смещении выбора планов в сторону менее селективных сканирований как основной причине роста физических чтений и деградации производительности. Рост объёма WAL на 73% при стабильной частоте запросов требует дополнительной проверки, но может быть следствием усиленной фоновой записи грязных страниц при вытеснении из буферного пула.

Послесловие

Полученные результаты демонстрируют принципиальную применимость методики, основанной на последовательном применении промптов к отчётам pgpro_pwr, для выявления тонких эффектов изменения параметров планировщика и версии СУБД. Однако надёжность выводов ограничена отсутствием в исходных данных точных значений shared_buffers, work_mem, статистики pg_stat_user_tables и метрик ожиданий (pg_stat_wait_events). Для окончательной верификации гипотезы необходима постановка контролируемого А/В-эксперимента с возвратом ключевых параметров (online_analyze, generic_plan_fuzz_factor, jit) к исходным значениям на целевой системе, а также анализ распределения фактически используемых планов по planid через pg_stat_statements. Предложенный подход может быть масштабирован на задачи регрессионного анализа производительности при миграциях PostgreSQL с использованием нейросетевых моделей в качестве ассистирующего инструмента.