Skip to content
TheChatArchiveThe Chat Archive
gpt-4.1-mini-2025-04-14· OpenAIAi2 WildChat-4.8M (AI2)

как посмотреть план запроса в postgresql

  1. User

    как посмотреть план запроса в postgresql

  2. Assistant

    Чтобы посмотреть план выполнения запроса в PostgreSQL, используется команда EXPLAIN. Она показывает, как PostgreSQL собирается выполнять запрос — какие индексы будут использоваться, порядок сканирования таблиц и т.д.

    Основные способы посмотреть план запроса:

    1. Простой план
    EXPLAIN SELECT * FROM your_table WHERE id = 123;
    

    Этот запрос вернёт текстовое описание плана выполнения.

    1. План с деталями (анализ)

    Чтобы получить более подробный план с реальным временем выполнения и статистикой, добавьте ключевое слово ANALYZE:

    EXPLAIN ANALYZE SELECT * FROM your_table WHERE id = 123;
    

    Этот запрос выполнит сам запрос и покажет фактическое время и количество обработанных строк. Очень полезно для оптимизации.

    1. План в формате JSON, YAML или XML

    Для удобного парсинга можно получить план в структурированном виде:

    EXPLAIN (FORMAT JSON) SELECT * FROM your_table WHERE id = 123;
    
    1. Использование GUI

    Если вы используете PgAdmin или другие инструменты, в них обычно есть графический просмотрщик плана запроса.


    Пример вывода простого EXPLAIN:

    Seq Scan on your_table  (cost=0.00..35.50 rows=5 width=244)
      Filter: (id = 123)
    

    Если нужно, могу помочь с разбором конкретного плана!

  3. User

    если выполнить запрос с EXPLAIN ANALYZE, то он будет выполняться?

  4. Assistant

    Да, если выполнить запрос с EXPLAIN ANALYZE, сам запрос будет выполнен.

    • EXPLAIN без ANALYZE — показывает только план выполнения, запрос не выполняется.
    • EXPLAIN ANALYZE — сначала выполняет запрос, а потом показывает план вместе с реальными измеренными временными характеристиками и количеством обработанных строк.

    Это важно учитывать, особенно если запрос изменяет данные (INSERT/UPDATE/DELETE) или если запрос долгий — с EXPLAIN ANALYZE он реально запустится и выполнится.


    Если нужно посмотреть только план без выполнения запроса — используйте просто EXPLAIN.

  5. 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

  6. Assistant

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


    Краткий разбор плана

    Общая структура

    • Верхнее действие: HashAggregate — агрегирование (GROUP BY) по очень большому числу полей (Group Key).
    • Есть две вложенные части:
      • CTE t_data, который тоже делает HashAggregate над сканом по таблице stg_oks_registry.
      • Затем происходит Hash Left Join между результатом t_data и таблицей oks_registry (или алиасом r), который использует bitmap-скан и хэш для ускорения соединения.

    Важные метрики из плана

    ПоказательЗначениеКомментарий
    Execution Time458.5 msВ целом, работа запроса чуть менее полусекунды — вполне приемлемо для сложной агрегации и join
    Rows output6778Кол-во итоговых строк после агрегирования
    Rows in stg_oks_registry119328Большая исходная выборка из 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

    Основные узлы

    1. Seq Scan по stg_oks_registry (119k строк, 154 ms)
      Это довольно много строк, причем скан последовательный — индекса нет или не используется.
      Возможно, можно оптимизировать добавлением нужных индексов или фильтрацией до скана.

    2. HashAggregate внутри CTE t_data
      Группирует почти 120k строк (около 420 ms), формируя 6778 строк.
      Хороший показатель — PostgreSQL смог быстро агрегировать большой массив данных в несколько тысяч групп.

    3. Hash Left Join между t_data и oks_registry
      Используется Hash Join по полю oks_code. Основная таблица — oks_registry — выбирается через Bitmap Index Scan по индексу i_oks_registry$oks_registry_id_not_obsolete.

    4. HashAggregate верхнего уровня
      Дополнительное агрегирование по итогам Join, уже по 6778 строкам, занимает около 4.5 мс (453..457 ms — вся секция).


    Что можно предпринять для оптимизации

    1. Проверить необходимость Seq Scan по stg_oks_registry
      Возможно, можно добавить индекс по столбцам, которые участвуют в фильтрах, чтобы уменьшить количество сканируемых строк.

    2. Проверить фильтры и JOIN по oks_code
      Убедиться, что по полю oks_code есть индекс в обеих таблицах. Это ускорит Hash Join, уменьшит память и CPU.

    3. Агрегация по большому количеству полей
      Судя по Group Key, агрегация идет сразу по 20+ колонок — может быть избыточно и замедлять запрос. Есть ли возможность агрегировать по меньшему числу колонок?

    4. ** Параметры памяти work_mem **
      HashAggregate и Hash Join активно используют память. Увеличение work_mem позволит выполнять операции в памяти, без сброса на диск, что ускорит выполнение (если сейчас memory usage близок к лимиту).

    5. Использование CTE t_data
      В PostgreSQL до версии 12 CTE всегда материализуются — то есть выполняются полностью и временно хранятся. В версиях 12+ можно использовать MATERIALIZED или NOT MATERIALIZED, чтобы контролировать это. Возможно, применение NOT MATERIALIZED позволит оптимизатору сдвинуть вычисления.


    Общие выводы

    • Время выполнения ~450 мс — это не критично для большинства запросов, если они не летят тысячи раз.
    • Самое затратное — последовательный скан большого staging-таблицы stg_oks_registry.
    • Проверка индексов по oks_code и другим фильтрам — ключ к улучшению.
    • Многочисленная агрегация по длинному списку колонок может сильно повлиять на производительность — ее стоит пересмотреть.
    • Оптимизация work_mem и версии PostgreSQL поможет в работе с CTE и хэш-операциями.

    Если нужно, могу помочь с написанием рекомендаций для EXPLAIN или с корректировкой запроса и индексов — просто скажите!

  7. 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_registrytsinsert"onstgoksregistrystg(cost=0.29..740.21rows=6651width=541)(actualtime=0.081..2.146rows=6629loops=1)IndexCond:(tsinsert>=′2025−05−0711:30:00′::timestampwithouttimezone)−>HashLeftJoin(cost=5092.17..5670.77rows=6934width=633)(actualtime=18.636..37.411rows=6629loops=1)HashCond:(t.okscode=r.okscode)−>CTEScanontdatat(cost=0.00..133.02rows=6651width=617)(actualtime=14.561..26.686rows=6629loops=1)−>Hash(cost=5005.82..5005.82rows=6908width=35)(actualtime=3.998..3.999rows=6629loops=1)Buckets:8192Batches:1MemoryUsage:498kB−>BitmapHeapScanonoksregistryr(cost=79.45..5005.82rows=6908width=35)(actualtime=0.330..2.461rows=6629loops=1)RecheckCond:(NOTobsolete)HeapBlocks:exact=446−>BitmapIndexScanon"ioksregistryts_insert" on stg_oks_registry stg (cost=0.29..740.21 rows=6651 width=541) (actual time=0.081..2.146 rows=6629 loops=1) Index Cond: (ts_insert >= '2025-05-07 11:30:00'::timestamp without time zone) -> Hash Left Join (cost=5092.17..5670.77 rows=6934 width=633) (actual time=18.636..37.411 rows=6629 loops=1) Hash Cond: (t.oks_code = r.oks_code) -> CTE Scan on t_data t (cost=0.00..133.02 rows=6651 width=617) (actual time=14.561..26.686 rows=6629 loops=1) -> Hash (cost=5005.82..5005.82 rows=6908 width=35) (actual time=3.998..3.999 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.330..2.461 rows=6629 loops=1) Recheck Cond: (NOT obsolete) Heap Blocks: exact=446 -> Bitmap Index Scan on "i_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

  8. Assistant

    Отлично, этот план явно лучше по времени и по количеству обрабатываемых строк. Давайте посмотрим подробнее, что изменилось и почему он быстрее.


    Анализ второго плана

    Основные показатели

    ПоказательЗначениеКомментарий
    Execution Time~55 msВ 8 раз быстрее первого варианта (~458 ms)
    Rows output6629Кол-во конечных строк почти такое же (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"Прекрасно! Фильтрация по дате с помощью индекса сильно сократила набор

    Ключевые различия

    1. Использование индекса на 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!), что значительно ускорило дальнейшие операции.
    1. Уменьшение HashAggregate и Hash Left Join
    • Сокращение объема данных на входе агрегатных операций и join также уменьшило CPU и память.
    • Более точная фильтрация — меньше ненужных строк.
    1. План и время
    • Планирование занимает всего 1 мс (против 7 мс в первом случае).
    • Выполнение — 55 мс против ~458 мс. Отличный прирост.

    Итоговые рекомендации

    • Всегда старайтесь использовать фильтрацию, позволяющую задействовать индекс (здесь — по ts_insert). Это самый главный фактор ускорения.
    • Обратите внимание, что условие по дате является очень эффективным способом отсечь лишние данные.
    • Рекомендуется поддерживать индексы на часто используемых в фильтрах столбцах.
    • Аналогично, старайтесь фильтровать данные как можно раньше, чтобы уменьшить объем последующей работы.

    Если кратко:

    ВопросОтвет
    Почему второй запрос быстрее?Потому что использует индекс и фильтрует во входных данных, а не сканирует всю таблицу.
    Что улучшено по сравнению с первым планом?Значительно сократилось количество считываемых строк из staging таблицы.
    Что стоит делать дальше?Проверить все частые фильтры и покрывающие индексы, протестировать с актуальными данными, увеличивать work_mem при необходимости.

    Если хотите, могу помочь сформулировать рекомендации или пример оптимизированного запроса с правильным использованием фильтров и индексов.

  9. 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_registrytsinsert"onstgoksregistrystg(cost=0.29..740.21rows=6651width=541)(actualtime=0.081..2.146rows=6629loops=1)IndexCond:(tsinsert>=′2025−05−0711:30:00′::timestampwithouttimezone)−>HashLeftJoin(cost=5092.17..5670.77rows=6934width=633)(actualtime=18.636..37.411rows=6629loops=1)HashCond:(t.okscode=r.okscode)−>CTEScanontdatat(cost=0.00..133.02rows=6651width=617)(actualtime=14.561..26.686rows=6629loops=1)−>Hash(cost=5005.82..5005.82rows=6908width=35)(actualtime=3.998..3.999rows=6629loops=1)Buckets:8192Batches:1MemoryUsage:498kB−>BitmapHeapScanonoksregistryr(cost=79.45..5005.82rows=6908width=35)(actualtime=0.330..2.461rows=6629loops=1)RecheckCond:(NOTobsolete)HeapBlocks:exact=446−>BitmapIndexScanon"ioksregistryts_insert" on stg_oks_registry stg (cost=0.29..740.21 rows=6651 width=541) (actual time=0.081..2.146 rows=6629 loops=1) Index Cond: (ts_insert >= '2025-05-07 11:30:00'::timestamp without time zone) -> Hash Left Join (cost=5092.17..5670.77 rows=6934 width=633) (actual time=18.636..37.411 rows=6629 loops=1) Hash Cond: (t.oks_code = r.oks_code) -> CTE Scan on t_data t (cost=0.00..133.02 rows=6651 width=617) (actual time=14.561..26.686 rows=6629 loops=1) -> Hash (cost=5005.82..5005.82 rows=6908 width=35) (actual time=3.998..3.999 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.330..2.461 rows=6629 loops=1) Recheck Cond: (NOT obsolete) Heap Blocks: exact=446 -> Bitmap Index Scan on "i_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

  10. 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Минимизировать внешние вызовы в группировке
    Использовать параллельное выполнение (если возможно)Снизить время выполнения
    Проверить ограничения и фильтрацию данныхУменьшить объем обрабатываемых строк

    Если хотите, могу помочь с конкретными примерами и предложениями по индексации или переписыванию группировки — дайте знать!