PostgreSQL - pg_stat_statements

Enable pg_stat_statements

1. Open postgresql.conf.
2. Search of 'shared_preload_libraries' parameter and set it to 'pg_stat_statements'.
         shared_preload_libraries = 'pg_stat_statements'
    pg_stat_statements.track = all
3. Save the file and restart services.
4. Connect using pgadmin and check for pg_stat_statements extension.

    SELECT *

FROM

       pg_available_extensions

WHERE

       name = 'pg_stat_statements'

       and installed_version is not null

5. If there is no extension, create one.

   CREATE EXTENSION pg_stat_statements;

6. Check, whether enabled

        SELECT * FROM pg_stat_statements

Reset statistics counters

Reset statistics counters

SELECT pg_stat_statements_reset();

Reset statistics counters

SELECT pg_stat_reset();

All Statements

SELECT query AS "Query_Text",

calls AS "Execution_Count",

total_time AS "Total_Execution_Time(ms)",

round((100 * total_time /

    sum(total_time::numeric) OVER ())::numeric, 2) AS "Percentage_Time(%)", 

shared_blks_hit AS "Total_Cache_Hits(Shared_Blocks)",

shared_blks_read AS "Total_Reads(Shared_Blocks)",

CASE calls

WHEN 0 THEN 0

ELSE blk_read_time / calls 

END AS "ReadTime_Per_Query",

CASE rows 

WHEN 0 THEN 0

ELSE blk_read_time / rows 

END AS "ReadTime_Per_Row",

blk_read_time AS "Total_Read_Time(ms)",

shared_blks_written AS "Total_Writes(Shared_Blocks)",

CASE calls

WHEN 0 THEN 0

ELSE blk_write_time / calls 

END AS "WriteTime_Per_Query",

CASE rows 

WHEN 0 THEN 0

ELSE blk_write_time / rows 

END AS "WriteTime_Per_Row",

blk_write_time AS "Total_Write_Time(ms)",

shared_blks_dirtied AS "Total_Dirtied(Shared_Blocks)",

temp_blks_read AS "TempReads(Blocks)",

temp_blks_written AS "TempWrites(Blocks)",

local_blks_hit AS "Total_Cache_Hits(Local_Blocks)",

local_blks_read AS "Total_Reads(Local_Blocks)",

local_blks_written AS "Total_Writes(Local_Blocks)",

local_blks_dirtied AS "Total_Dirtied(Local_Blocks)"

FROM pg_stat_statements

ORDER BY "Percentage_Time(%)" DESC

SQL ordered by executions

SELECT  roles.rolname, 
statements.query AS "Query_Text",
statements.calls AS "Execution_Count",
statements.total_time AS "Total_Execution_Time(ms)"
FROM pg_stat_statements AS statements
INNER JOIN pg_stat_database AS db ON db.datid = statements.dbid
INNER JOIN pg_roles AS roles ON roles.oid = statements.userid
-- WHERE  db.datname = 'nimbus_0_production' AND roles.rolname NOT LIKE '%backup' AND statements.query NOT LIKE '%FROM pg_%' AND roles.rolname IN ('nimbus', 'wfs') AND statements.query NOT LIKE '%from pg_%'
ORDER BY statements.calls DESC

LIMIT 50

SQL ordered by elapsed time

SELECT  roles.rolname, 
statements.query AS "Query_Text",
statements.total_time AS "Total_Execution_Time(ms)",
CASE statements.calls 
WHEN 0 then 0
ELSE statements.total_time / statements.calls 
END AS "TimePerExecution(ms)",
round((100 * statements.total_time /
        sum(statements.total_time::numeric) OVER ())::numeric, 2) AS "Percentage_Time(%)",
        statements.calls AS "Execution_Count"
FROM pg_stat_statements AS statements
INNER JOIN pg_stat_database ON pg_stat_database.datid = statements.dbid
INNER JOIN pg_roles roles ON roles.oid = statements.userid
-- WHERE pg_stat_database.datname = 'nimbus_0_production' AND roles.rolname NOT LIKE '%backup' AND statements.query NOT LIKE '%FROM pg_%' AND roles.rolname IN ('nimbus', 'wfs') AND statements.query NOT LIKE '%from pg_%'
ORDER BY "Percentage_Time(%)" DESC
LIMIT 50

SQL with highest disk reads 

SELECT  rolname, 
query AS "Query_Text",
total_time  AS "Total_Execution_Time(ms)",
(COALESCE(shared_blks_read,0) +  COALESCE(local_blks_read,0) +  COALESCE(temp_blks_read,0)) AS "Total_Reads(Blocks)",
statements.blk_read_time AS "Total_Read_Time(ms)",
CASE calls
 WHEN 0 THEN 0
 ELSE (COALESCE(shared_blks_read,0) +  COALESCE(local_blks_read,0) +  COALESCE(temp_blks_read,0)) / calls 
END AS "ReadTime_Per_Query"
FROM pg_stat_statements AS statements
INNER JOIN pg_stat_database ON pg_stat_database.datid = statements.dbid
INNER JOIN pg_roles roles ON roles.oid = userid
-- WHERE pg_stat_database.datname = 'nimbus_0_production' AND rolname NOT LIKE '%backup' AND query NOT LIKE '%_pg%'
ORDER BY "Total_Reads(Blocks)" DESC

LIMIT 50

SQL with highest disk writes

SELECT  rolname, 
query AS "Query_Text",
total_time AS "Total_Execution_Time(ms)",
(COALESCE(shared_blks_written,0) +  COALESCE(local_blks_written,0) +  COALESCE(temp_blks_written,0)) AS "Total_Writes(Blocks)",
statements.blk_read_time AS "Total_Write_Time(ms)",
CASE calls
WHEN 0 THEN 0
ELSE (COALESCE(shared_blks_written,0) +  COALESCE(local_blks_written,0) +  COALESCE(temp_blks_written,0)) / calls 
END AS "WriteTime_Per_Query"
FROM pg_stat_statements AS statements
INNER JOIN pg_stat_database ON pg_stat_database.datid = statements.dbid
INNER JOIN pg_roles roles ON roles.oid = userid
--  WHERE pg_stat_database.datname = 'nimbus_0_production' AND rolname NOT LIKE '%backup' AND query NOT LIKE '%_pg%'
ORDER BY "Total_Writes(Blocks)" DESC
LIMIT 50