PostgreSQL – Get The Top 10 Time-Consuming Queries in AWS RDS PostgreSQL and TencentDB for PostgreSQL

Here is a script to get the Top 10 Time-Consuming Queries (This will work for PostgreSQL version 13 and above)

We will make use of the pg_stat_statements view. This view will tell us which types of queries are slow and how often these queries are called.

In AWS RDS, this module is already installed by default. So, all you need to do is to create the extension before running the script.

create extension pg_stat_statements;

In Tencent DB for PostgreSQL, both module and the extension are enabled by default. So, there is no need to do anything before running the query.

Disable expanded display

\x

Script to get Top 10 Time Consuming Queries

SELECT 
  t2.rolname, 
  t3.datname, 
  queryid, 
  calls, 
  (
    total_plan_time + total_exec_time
  ) / 1000 as total_time_seconds, 
  (min_plan_time + min_exec_time) / 1000 as min_time_seconds, 
  (max_plan_time + max_exec_time) / 1000 as max_time_seconds, 
  (mean_plan_time + mean_exec_time) / 1000 as mean_time_seconds, 
  (
    stddev_plan_time + stddev_exec_time
  ) / 1000 as stddev_time_seconds, 
  rows, 
  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, 
  blk_read_time / 1000 as blk_read_time_seconds, 
  blk_write_time / 1000 as blk_write_time_seconds 
FROM 
  pg_stat_statements t1 
  JOIN pg_roles t2 ON (t1.userid = t2.oid) 
  JOIN pg_database t3 ON (t1.dbid = t3.oid) 
WHERE 
  t2.rolname != 'rdsadmin' 
  AND queryid IS NOT NULL;

Cheers!