Queries for pg_stat_statements

Создано 2023-05-19 · Обновлено 2023-05-19

TL;DR: полезные запросы к pg_stat_statements в PostgresSQL для поиска проблемных запросов.

---

IOPS based performance:

select
	shared_blks_hit + shared_blks_read + shared_blks_dirtied + shared_blks_written + local_blks_hit + local_blks_read + local_blks_dirtied + local_blks_written + temp_blks_read + temp_blks_written as total_buffers,
	(total_exec_time + total_plan_time)::int as total_time,
	calls,
	shared_blks_hit as sbh,
	shared_blks_read as sbr,
	shared_blks_dirtied as sbd, 
	shared_blks_written as sbw,
	local_blks_hit as lbh,
	local_blks_read as lbr,
	local_blks_dirtied as lbd,
	local_blks_written as lbw,
	temp_blks_read as tbr,
	temp_blks_written as tbr,
	query
from
	pg_stat_statements
order by
	total_buffers desc
limit 50;

Top slowest:

select
	(mean_exec_time + mean_plan_time)::int as mean_time,
	mean_exec_time::int,
	mean_plan_time::int,
	calls,
	query
from
	pg_stat_statements
--where
--	userid = 99999
--	and calls > 1
order by
	mean_time desc
limit 50;

Total consuming queries:

select
	(total_exec_time + total_plan_time)::int as total_time,
	total_exec_time::int,
	total_plan_time::int,
	mean_exec_time::int,
	calls,
	query
from
	pg_stat_statements
order by
	total_time desc
limit 50;

Link:: https://www.pgmustard.com/blog/queries-for-pg-stat-statements