pg_stat_statements tracks planning and execution statistics of all SQL statements executed by a server.
Find the top 10 time-consuming queries
SELECT
round(
(
100 * total_exec_time / sum(total_exec_time) OVER ()
):: numeric,
2
) percent,
round(total_exec_time :: numeric, 2) AS total,
calls,
round(mean_exec_time :: numeric, 2) AS mean,
substring(query, 1, 200)
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT
10;
Find the queries that are writing to the temp the most Read More
select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by
temp_blks_written desc;
Find the queries that are reading from temp the most
select queryid, left(query,40), calls, temp_blks_read,
temp_blks_written from pg_stat_statements order by temp_blks_read
desc;
Find the queries that are reading from disk the most
select queryid, left(query,40), calls, local_blks_read,
local_blks_read/calls as avg_read_per_call from pg_stat_statements
order by local_blks_read desc;
Find the queries with the highest execution times
select queryid, left(query,40), mean_exec_time, max_exec_time from
pg_stat_statements where calls > 10 order by mean_exec_time desc,
max_exec_time desc;
Cheers!
Leave a Reply