1. Мониторинг в реальном времени через pg_stat_activity

Рассмотрим представление для мониторинга текущих активных соединений и запросов через pg_stat_activity
Запрос к pg_stat_activity покажет, какие запросы работают в БД в конкретный момент времени (при обращении к pg_stat_activity):

select * from pg_stat_activity where state <> 'idle'

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

2. Расширение для сбора статистики выполнения
запросов pg_stat_statements

• Сохраняет информацию о частоте выполнения запросов, общем времени выполнения, времени
ожидания и других параметрах.
• Используется для анализа наиболее "тяжелых" запросов.

2.1 Настройка

Чтобы найти топ-100 самых долгих запросов в PostgreSQL, можно воспользоваться системными представлениями pg_stat_statements или pg_stat_activity.

Способ 1: Использование pg_stat_statements (Рекомендуется)

...

Чтобы включить

pg_stat_statements,

...

необходимо выполнить

...

запрос

CREATE EXTENSION pg_stat_statements;

А также необходимо в postgresql.conf задать настройки сбора статистики:

SELECT query,
       calls,
       total_time,
       mean_time,
       max_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 100;

Что делает этот запрос?

• Показывает 100 самых долгих запросов по суммарному времени выполнения (total_time).
• mean_time – среднее время выполнения.
• max_time – самое долгое выполнение.
• calls – сколько раз запрос выполнялся.

Способ 2: Мониторинг текущих долгих запросов (pg_stat_activity)

# Подключение библиотеки pg_stat_statements. Если в
shared_preload_libraries уже указаны другие библиотеки, просто добавьте
pg_stat_statements через запятую, например
'pg_stat_statements,other_library':
shared_preload_libraries = 'pg_stat_statements'
# Максимальное количество запросов, статистика которых будет храниться:
pg_stat_statements.max = 5000
# Типы запросов, которые будут отслеживаться (all, top, или none)
pg_stat_statements.track = all
# Сохранять статистику между перезапусками:
pg_stat_statements.save = on
# Включать параметр queryid в статистику (только для PostgreSQL 14 и выше)
compute_query_id = on

2.2 Сбор статистики

Чтобы собрать статистику, можно использовать следующие варианты запросов:
• Сбор самых долгих (по общему времени выполнения) запросов

...

:

SELECT pidqueryid,
 	   query,
 	   age(clock_timestamp(), query_start) AS duration,
       state,
       querycalls,
 	   total_time,
 	   mean_time,
 	   max_time,
 	   rows
FROM pg_stat_activity
WHERE state != 'idle'
statements
ORDER BY durationtotal_time DESC
LIMIT 100;

Этот запрос показывает:

• Запросы, которые прямо сейчас выполняются дольше всего.
• duration – время выполнения запроса.
• state – статус запроса (например, active или idle in transaction).

Как включить pg_stat_statements, если он отключен?

...

• Сбор самых долгих (по среднему времени выполнения) запросов:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;				

А затем добавить в postgresql.conf:

shared_preload_libraries = 'SELECT queryid,
 	   query,
       calls,
       total_time,
       mean_time,
       max_time,
       rows
FROM pg_stat_statements'
pg_stat_statements.track = all        

...

Дополнительная информация:

...

ORDER BY mean_time DESC
LIMIT 100;

• Сбор самых долгих (по максимальному времени выполнения) запросов:

...

SELECT 

...

queryid,
  

...

...

...

...

...

...

query,

...

 	   calls,
	   total_time,
 	   mean_time,
 	   max_time,
 	   rows
FROM pg_stat_statements
ORDER BY max_time DESC
LIMIT 100;

Для анализа будут полезны все три вида статистики. 
Обратите внимание, параметр queryid доступен к использованию только в версиях
PostgreSQL 14 и выше. В случае использования версии PostgreSQL ниже 14, параметр
queryid необходимо исключить из запроса.

После сбора статистики вы можете отправить информацию на анализ в 
Отдел сопровождения и внедрения: support@iqstore.ru.

1.3 PWR-отчёт

В PostgresPro, коммерческом форке PostgreSQL, есть специальный инструмент, который
можно считать аналогом AWR-отчёта Oracle — pgpro_pwr.
Подробнее информацию по настройке см. в документации вендора.

...

...