как посмотреть план запроса в postgresql
- User
как посмотреть план запроса в postgresql
- Assistant
Чтобы посмотреть план выполнения запроса в PostgreSQL, используется команда
EXPLAIN. Она показывает, как PostgreSQL собирается выполнять запрос — какие индексы будут использоваться, порядок сканирования таблиц и т.д.Основные способы посмотреть план запроса:
- Простой план
EXPLAIN SELECT * FROM your_table WHERE id = 123;Этот запрос вернёт текстовое описание плана выполнения.
- План с деталями (анализ)
Чтобы получить более подробный план с реальным временем выполнения и статистикой, добавьте ключевое слово
ANALYZE:EXPLAIN ANALYZE SELECT * FROM your_table WHERE id = 123;Этот запрос выполнит сам запрос и покажет фактическое время и количество обработанных строк. Очень полезно для оптимизации.
- План в формате JSON, YAML или XML
Для удобного парсинга можно получить план в структурированном виде:
EXPLAIN (FORMAT JSON) SELECT * FROM your_table WHERE id = 123;- Использование GUI
Если вы используете PgAdmin или другие инструменты, в них обычно есть графический просмотрщик плана запроса.
Пример вывода простого EXPLAIN:
Seq Scan on your_table (cost=0.00..35.50 rows=5 width=244) Filter: (id = 123)
Если нужно, могу помочь с разбором конкретного плана!
- User
если выполнить запрос с EXPLAIN ANALYZE, то он будет выполняться?
- Assistant
Да, если выполнить запрос с
EXPLAIN ANALYZE, сам запрос будет выполнен.EXPLAINбезANALYZE— показывает только план выполнения, запрос не выполняется.EXPLAIN ANALYZE— сначала выполняет запрос, а потом показывает план вместе с реальными измеренными временными характеристиками и количеством обработанных строк.
Это важно учитывать, особенно если запрос изменяет данные (INSERT/UPDATE/DELETE) или если запрос долгий — с
EXPLAIN ANALYZEон реально запустится и выполнится.
Если нужно посмотреть только план без выполнения запроса — используйте просто
EXPLAIN. - User
Что можешь сказать насчет этого плана? HashAggregate (cost=27625.29..28060.72 rows=24882 width=633) (actual time=453.002..457.446 rows=6778 loops=1) Group Key: COALESCE(r.id, nextval('governor_main.oks_registry_id_seq'::regclass)), CASE WHEN (r.version_id IS NULL) THEN currval('governor_main.oks_registry_id_seq'::regclass) ELSE nextval('governor_main.oks_registry_id_seq'::regclass) END, t.oks_code, t.oks_name, t.subject_code, t.category_kind_code, t.parent_category_kind_code, t.name_category_kind_code, t.grbs_code, t.address, t.start_construction_date, t.end_construction_date, t.cost, t.oks_status, t.ownership_code, t.inn_executor, t.video, t.np_code, t.fp_code, t.gp_code, t.nt_code, t.socinitiative, t.goverment_control, t.inn Batches: 1 Memory Usage: 9012kB CTE t_data -> HashAggregate (cost=18725.32..18963.98 rows=23866 width=541) (actual time=416.049..420.902 rows=6778 loops=1) Group Key: stg.oks_code, stg.oks_name, stg.subject_code, stg.category_kind_code, stg.parent_category_kind_code, stg.name_category_kind_code, stg.grbs_code, stg.address, stg.start_construction_date, stg.end_construction_date, stg.cost, stg.oks_status, stg.ownership_code, stg.inn_executor, stg.video, stg.np_code, stg.fp_code, stg.gp_code, stg.nt_code, stg.socinitiative, stg.goverment_control, stg.inn Batches: 1 Memory Usage: 9012kB -> Seq Scan on stg_oks_registry stg (cost=0.00..12162.28 rows=119328 width=541) (actual time=0.037..154.229 rows=119328 loops=1) -> Hash Left Join (cost=5092.17..7168.39 rows=24882 width=633) (actual time=420.355..439.472 rows=6778 loops=1) Hash Cond: (t.oks_code = r.oks_code) -> CTE Scan on t_data t (cost=0.00..477.32 rows=23866 width=617) (actual time=416.065..428.374 rows=6778 loops=1) -> Hash (cost=5005.82..5005.82 rows=6908 width=35) (actual time=4.109..4.111 rows=6629 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 498kB -> Bitmap Heap Scan on oks_registry r (cost=79.45..5005.82 rows=6908 width=35) (actual time=0.413..2.539 rows=6629 loops=1) Recheck Cond: (NOT obsolete) Heap Blocks: exact=446 -> Bitmap Index Scan on "i_oks_registry$oks_registry_id_not_obsolete" (cost=0.00..77.72 rows=6908 width=0) (actual time=0.355..0.355 rows=6629 loops=1) Planning Time: 7.156 ms Execution Time: 458.547 ms
- Assistant
Ваш план запроса достаточно большой и сложный, но попробуем его разобрать по основным моментам и понять, что происходит и как можно улучшить.
Краткий разбор плана
Общая структура
- Верхнее действие:
HashAggregate— агрегирование (GROUP BY) по очень большому числу полей (Group Key). - Есть две вложенные части:
- CTE
t_data, который тоже делаетHashAggregateнад сканом по таблицеstg_oks_registry. - Затем происходит
Hash Left Joinмежду результатомt_dataи таблицейoks_registry(или алиасомr), который использует bitmap-скан и хэш для ускорения соединения.
- CTE
Важные метрики из плана
Показатель Значение Комментарий Execution Time 458.5 ms В целом, работа запроса чуть менее полусекунды — вполне приемлемо для сложной агрегации и join Rows output 6778 Кол-во итоговых строк после агрегирования Rows in stg_oks_registry 119328 Большая исходная выборка из staging таблицы Rows in oks_registry (r) 6629 Много строк выбрано из основной таблицы (по Bitmap Index) Memory Usage HashAggregate (top-level) 9012 kB Используется для агрегирования (что нормально) Recheck Cond (Bitmap Heap Scan) (NOT obsolete)На таблице oks_registryиспользуется индекс и фильтр по флагуobsolete
Основные узлы
-
Seq Scan по
stg_oks_registry(119k строк, 154 ms)
Это довольно много строк, причем скан последовательный — индекса нет или не используется.
Возможно, можно оптимизировать добавлением нужных индексов или фильтрацией до скана. -
HashAggregate внутри CTE
t_data
Группирует почти 120k строк (около 420 ms), формируя 6778 строк.
Хороший показатель — PostgreSQL смог быстро агрегировать большой массив данных в несколько тысяч групп. -
Hash Left Join между
t_dataиoks_registry
Используется Hash Join по полюoks_code. Основная таблица —oks_registry— выбирается через Bitmap Index Scan по индексуi_oks_registry$oks_registry_id_not_obsolete. -
HashAggregate верхнего уровня
Дополнительное агрегирование по итогамJoin, уже по 6778 строкам, занимает около 4.5 мс (453..457 ms — вся секция).
Что можно предпринять для оптимизации
-
Проверить необходимость Seq Scan по
stg_oks_registry
Возможно, можно добавить индекс по столбцам, которые участвуют в фильтрах, чтобы уменьшить количество сканируемых строк. -
Проверить фильтры и JOIN по
oks_code
Убедиться, что по полюoks_codeесть индекс в обеих таблицах. Это ускорит Hash Join, уменьшит память и CPU. -
Агрегация по большому количеству полей
Судя по Group Key, агрегация идет сразу по 20+ колонок — может быть избыточно и замедлять запрос. Есть ли возможность агрегировать по меньшему числу колонок? -
** Параметры памяти
work_mem**
HashAggregate и Hash Join активно используют память. Увеличениеwork_memпозволит выполнять операции в памяти, без сброса на диск, что ускорит выполнение (если сейчас memory usage близок к лимиту). -
Использование CTE
t_data
В PostgreSQL до версии 12 CTE всегда материализуются — то есть выполняются полностью и временно хранятся. В версиях 12+ можно использоватьMATERIALIZEDилиNOT MATERIALIZED, чтобы контролировать это. Возможно, применениеNOT MATERIALIZEDпозволит оптимизатору сдвинуть вычисления.
Общие выводы
- Время выполнения ~450 мс — это не критично для большинства запросов, если они не летят тысячи раз.
- Самое затратное — последовательный скан большого staging-таблицы
stg_oks_registry. - Проверка индексов по
oks_codeи другим фильтрам — ключ к улучшению. - Многочисленная агрегация по длинному списку колонок может сильно повлиять на производительность — ее стоит пересмотреть.
- Оптимизация
work_memи версии PostgreSQL поможет в работе с CTE и хэш-операциями.
Если нужно, могу помочь с написанием рекомендаций для EXPLAIN или с корректировкой запроса и индексов — просто скажите!
- Верхнее действие:
- User
А вот так: HashAggregate (cost=7259.33..7380.67 rows=6934 width=633) (actual time=50.280..54.410 rows=6629 loops=1) Group Key: COALESCE(r.id, nextval('governor_main.oks_registry_id_seq'::regclass)), CASE WHEN (r.version_id IS NULL) THEN currval('governor_main.oks_registry_id_seq'::regclass) ELSE nextval('governor_main.oks_registry_id_seq'::regclass) END, t.oks_code, t.oks_name, t.subject_code, t.category_kind_code, t.parent_category_kind_code, t.name_category_kind_code, t.grbs_code, t.address, t.start_construction_date, t.end_construction_date, t.cost, t.oks_status, t.ownership_code, t.inn_executor, t.video, t.np_code, t.fp_code, t.gp_code, t.nt_code, t.socinitiative, t.goverment_control, t.inn Batches: 1 Memory Usage: 8436kB CTE t_data -> HashAggregate (cost=1106.01..1172.52 rows=6651 width=541) (actual time=14.556..19.292 rows=6629 loops=1) Group Key: stg.oks_code, stg.oks_name, stg.subject_code, stg.category_kind_code, stg.parent_category_kind_code, stg.name_category_kind_code, stg.grbs_code, stg.address, stg.start_construction_date, stg.end_construction_date, stg.cost, stg.oks_status, stg.ownership_code, stg.inn_executor, stg.video, stg.np_code, stg.fp_code, stg.gp_code, stg.nt_code, stg.socinitiative, stg.goverment_control, stg.inn Batches: 1 Memory Usage: 4340kB -> Index Scan using "i_stg_oks_registryoks_registry_id_not_obsolete" (cost=0.00..77.72 rows=6908 width=0) (actual time=0.285..0.286 rows=6629 loops=1) Planning Time: 1.051 ms Execution Time: 55.386 ms
- Assistant
Отлично, этот план явно лучше по времени и по количеству обрабатываемых строк. Давайте посмотрим подробнее, что изменилось и почему он быстрее.
Анализ второго плана
Основные показатели
Показатель Значение Комментарий Execution Time ~55 ms В 8 раз быстрее первого варианта (~458 ms) Rows output 6629 Кол-во конечных строк почти такое же (6778 vs 6629) Rows из stg_oks_registry6629 (Index Scan vs Seq Scan в первом плане) На порядок меньше и используется индекс! Memory Usage HashAggregate (top-level) 8436 kB Легче верхнему агрегатору работать, т.к. меньше данных Index Scan на stg_oks_registryИспользуется индекс "i_stg_oks_registry$ts_insert"Прекрасно! Фильтрация по дате с помощью индекса сильно сократила набор
Ключевые различия
- Использование индекса на
stg_oks_registry
Index Scan using "i_stg_oks_registry$ts_insert" on stg_oks_registry stg Index Cond: (ts_insert >= '2025-05-07 11:30:00')- Во втором плане вместо полной последовательной выборки (Seq Scan) по всей таблице
stg_oks_registryиспользуется индекс по полюts_insert— с условием на дату. - Это резко сократило количество строк (с 119328 до 6629!), что значительно ускорило дальнейшие операции.
- Уменьшение
HashAggregateиHash Left Join
- Сокращение объема данных на входе агрегатных операций и join также уменьшило CPU и память.
- Более точная фильтрация — меньше ненужных строк.
- План и время
- Планирование занимает всего 1 мс (против 7 мс в первом случае).
- Выполнение — 55 мс против ~458 мс. Отличный прирост.
Итоговые рекомендации
- Всегда старайтесь использовать фильтрацию, позволяющую задействовать индекс (здесь — по
ts_insert). Это самый главный фактор ускорения. - Обратите внимание, что условие по дате является очень эффективным способом отсечь лишние данные.
- Рекомендуется поддерживать индексы на часто используемых в фильтрах столбцах.
- Аналогично, старайтесь фильтровать данные как можно раньше, чтобы уменьшить объем последующей работы.
Если кратко:
Вопрос Ответ Почему второй запрос быстрее? Потому что использует индекс и фильтрует во входных данных, а не сканирует всю таблицу. Что улучшено по сравнению с первым планом? Значительно сократилось количество считываемых строк из staging таблицы. Что стоит делать дальше? Проверить все частые фильтры и покрывающие индексы, протестировать с актуальными данными, увеличивать work_memпри необходимости.
Если хотите, могу помочь сформулировать рекомендации или пример оптимизированного запроса с правильным использованием фильтров и индексов.
- Использование индекса на
- User
Что можно еще улучшить в этом плане? HashAggregate (cost=7259.33..7380.67 rows=6934 width=633) (actual time=50.280..54.410 rows=6629 loops=1) Group Key: COALESCE(r.id, nextval('governor_main.oks_registry_id_seq'::regclass)), CASE WHEN (r.version_id IS NULL) THEN currval('governor_main.oks_registry_id_seq'::regclass) ELSE nextval('governor_main.oks_registry_id_seq'::regclass) END, t.oks_code, t.oks_name, t.subject_code, t.category_kind_code, t.parent_category_kind_code, t.name_category_kind_code, t.grbs_code, t.address, t.start_construction_date, t.end_construction_date, t.cost, t.oks_status, t.ownership_code, t.inn_executor, t.video, t.np_code, t.fp_code, t.gp_code, t.nt_code, t.socinitiative, t.goverment_control, t.inn Batches: 1 Memory Usage: 8436kB CTE t_data -> HashAggregate (cost=1106.01..1172.52 rows=6651 width=541) (actual time=14.556..19.292 rows=6629 loops=1) Group Key: stg.oks_code, stg.oks_name, stg.subject_code, stg.category_kind_code, stg.parent_category_kind_code, stg.name_category_kind_code, stg.grbs_code, stg.address, stg.start_construction_date, stg.end_construction_date, stg.cost, stg.oks_status, stg.ownership_code, stg.inn_executor, stg.video, stg.np_code, stg.fp_code, stg.gp_code, stg.nt_code, stg.socinitiative, stg.goverment_control, stg.inn Batches: 1 Memory Usage: 4340kB -> Index Scan using "i_stg_oks_registryoks_registry_id_not_obsolete" (cost=0.00..77.72 rows=6908 width=0) (actual time=0.285..0.286 rows=6629 loops=1) Planning Time: 1.051 ms Execution Time: 55.386 ms
- Assistant
Ваш план уже довольно оптимизирован, и время выполнения (~55 мс) для такой сложной агрегации и join — хороший результат. Тем не менее, всегда есть возможности для дополнительного улучшения. Вот несколько рекомендаций, которые могут помочь:
1. Упростить или уменьшить ключ группировки
У вас группировка по очень большому количеству полей — более 20 колонок, в том числе по выражениям с
COALESCE,nextval,currval:Group Key: COALESCE(r.id, nextval(...)), CASE WHEN (...) THEN currval(...) ELSE nextval(...) END, t.oks_code, t.oks_name, ...- Возможное улучшение: Проверьте, действительно ли все эти поля нужны для группировки. Чем меньше полей в GROUP BY — тем быстрее и с меньшими затратами памяти работает агрегат.
- Попробуйте вынести вычисления
nextval/currvalили использованиеCOALESCEза пределы группировки, если это возможно. - Если эти поля нужны для генерации идентификаторов, рассмотрите возможность отложенного присвоения значений или генерации на уровне приложения.
2. Оптимизация CTE
- В PostgreSQL до версии 12 CTE выполняются как материализованные, т.е. результат CTE полностью вычисляется и сохраняется во временную структуру, которая потом читается.
- В версиях 12+ можно использовать
NOT MATERIALIZED(или в более новых — по умолчанию CTE не материализуются). - Если у вас PostgreSQL 12+, попробуйте:
WITH t_data AS MATERIALIZED ( ... ) -- или NOT MATERIALIZED- Или переписать запрос без CTE в виде вложенного подзапроса, чтобы оптимизатор смог пересмотреть порядок выполнения.
3. Индексы и условия для JOIN
- У вас
Hash Left Joinпо условиюt.oks_code = r.oks_code. - Убедитесь, что
r.oks_codeиндексирован (это сильно ускорит построение хеша дляoks_registry). - Если это уникальный ключ или почти уникальный, можно подумать о
Merge JoinвместоHash Join(но это зависит от данных и настроек).
4. Настройки памяти PostgreSQL
- Текущие значения памяти для хеш-операций (около 8-9Мб) смотрятся нормально, но если у вас высокий
work_mem(по умолчанию 4MB или выше), то можно увеличить, чтобы избежать переключения на дисковый swap (в вашем плане пока этого не видно). - Например, повысить
work_memдо 16MB или 32MB и проверить, ускорится ли запрос.
5. Использование покрывающих индексов (INCLUDE)
- Если агрегация использует поля, которые можно включить в индекс, подумайте о покрывающих индексах (
INCLUDEдля Postgres 11+), чтобы считывать данные только с индекса без доступа к heap.
Например, наstg_oks_registryпомимоts_insertиндекс мог бы включать часто используемые для группировки колонки.
6. Проверьте функции
nextvalиcurrval- Использование функций генерации последовательностей (
nextval,currval) в группировке — может быть "дорогостоящим", особенно если вызывает переходы в C-ядро. - Проверьте, нужны ли они прямо в запросе, или можно генерировать значения в отдельном шаге.
7. Параллелизм (если версия Postgres и настройки позволяют)
- В вашем плане нет признаков параллельного выполнения (Parallel Seq Scan, Parallel Hash Aggregate).
- Если сервер позволяет, можно увеличить
max_parallel_workers_per_gatherи посмотреть, позволит ли это ускорить сканирование и агрегацию, особенно если данные растут. - Например, установить в
postgresql.conf:
max_parallel_workers_per_gather = 4И повторить анализ.
8. Анализ "лишних" строк
- В плане получаем 6629 строк, при условиях — возможно, стоит дополнительно проверить, нет ли избыточного дублирования или избыточных данных, которые просто несут нагрузку.
- Может, стоит наложить более жесткие фильтры или преобразовать логику для снижения объема.
Итог и краткий чек-лист
Что сделать Цель Уменьшить количество полей в группировке Ускорить HashAggregate, уменьшить память Переписать CTE, использовать NOT MATERIALIZED (Postgres 12+) Улучшить оптимизацию запроса Проверить и добавить индексы на поля соединения Ускорить Hash Join Повысить work_memдля хеш-операцийИзбежать дискового свопа Рассмотреть покрывающие индексы Уменьшить чтение heap страниц Перепроверить использование nextval/currvalМинимизировать внешние вызовы в группировке Использовать параллельное выполнение (если возможно) Снизить время выполнения Проверить ограничения и фильтрацию данных Уменьшить объем обрабатываемых строк
Если хотите, могу помочь с конкретными примерами и предложениями по индексации или переписыванию группировки — дайте знать!