PostgreSQL-Diagnostic-Queries – May 2019


psql queries to quickly Identify & resolve database performance problems



As a seasoned data store engineer, I often find myself in situations where a production application is down due to some sort of performance issue and I am asked “What’s wrong with the database?”. In almost all these situations, the database (and DBA) is automatically considered guilty until proven innocent. As a DBA, I need the tools and knowledge to help quickly determine the actual problem, if there is one, because maybe there’s nothing wrong with the database or the database server. My favorite approach to start with performance analysis is via PostgreSQL systems catalog

In below post, I am sharing bunch of PostgreSQL system catalog queries that can be used to troubleshoot database engine performance

Postgres system catalogs are a place where database management system stores schema metadata, such as information about tables and columns, and internal bookkeeping information. PostgreSQL’s system catalogs are regular tables.

Instance Details


1. Get server IP address, Postgres Version and Port number

SELECT inet_server_addr() AS "Server IP"
, version() AS "Postgres Version"
, setting AS "Port Number"
, current_timestamp::timestamp
FROM pg_settings WHERE name = 'port';

--Postgres query for the specific server version, in the standard major.minor.patch format

2. Get server version

SHOW server_version;

-- Version First Release Final Release
-- 11 October 18, 2018 November 9, 2023
-- 10 October 5, 2017 November 10, 2022
-- 9.6 September 29, 2016 November 11, 2021
-- 9.5 January 7, 2016 February 11, 2021
-- 9.4 December 18, 2014 February 13, 2020
-- 9.3 September 9, 2013 November 8, 2018
-- 9.2 September 10, 2012 November 9, 2017
-- 9.1 September 12, 2011 October 27, 2016
-- 9.0 September 20, 2010 October 8, 2015

3. Get system information

SELECT inet_server_addr() AS Server_IP --server IP address
, inet_server_port() AS Server_Port --server port
, current_database() AS Current_Database --Current database
, current_user AS Current_User --Current user
, pg_backend_pid() AS ProcessID --Current user pid
, pg_postmaster_start_time() AS Server_Start_Time --Last start time
, current_timestamp::timestamp - pg_postmaster_start_time()::timestamp AS Running_Since;

--Server up time

4. Get details of server parameter configuration from view pg_settings

SELECT * FROM pg_settings;							-- all parameters

SELECT name, unit, setting FROM pg_settings WHERE name ='port'                  
SELECT name, unit, setting FROM pg_settings WHERE name ='shared_buffers'        -- shared_buffers determines how much memory is dedicated for caching data
SELECT name, unit, setting FROM pg_settings WHERE name ='work_mem'              -- work memory required for each incoming connection
SELECT name, unit, setting FROM pg_settings WHERE name ='maintenance_work_mem'  -- work memory of maintenace type queries "VACUUM, CREATE INDEX etc."
SELECT name, unit, setting FROM pg_settings WHERE name ='wal_buffers'           -- Sets the number of disk-page buffers in shared memory for WAL
UNION ALL          
SELECT name, unit, setting FROM pg_settings WHERE name ='effective_cache_size'  -- used by postgres query planner
SELECT name, unit, setting FROM pg_settings WHERE name ='TimeZone'              -- server time zone

-- This gives you a lot of useful information about your postgres instance

5. Get OS information

SELECT version();

-- Gives you major OS version
OS version Wiki References
Mac OS-https://en.wikipedia.org/wiki/MacOS

6. Get location of data directory (this is where postgres stores the database files)

SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
SHOW data_directory;


7. Get max_connections configuration

SELECT name, setting, short_desc from pg_settingsWHERE name = 'max_connections';

8. Get total count of current user connections

SELECT COUNT(*) FROM pg_stat_activity;

9. Get total Active Vs Inactive connections

SELECT state, count(pid) 
FROM pg_stat_activity
GROUP BY state, datname
HAVING datname = '<your_database_name>'
ORDER BY count(pid) DESC;

-- One row per server process, showing database OID, database name, process ID, user OID, user name, current query, query's waiting status, time at which the current query began execution
-- Time at which the process was started, and client's address and port number. The columns that report data on the current query are available unless the parameter stats_command_string has been turned off.
-- Furthermore, these columns are only visible if the user examining the view is a superuser or the same as the user owning the process being reported on

Database Details

**** Switch to a user database that you are interested in *****

10. Get database current size (pretty size)

SELECT current_database(), pg_size_pretty(pg_database_size(current_database()));

11. Get details of TOP 20 objects in database

SELECT nspname AS schemaname
, cl.relname AS objectname
, CASE relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
, pg_size_pretty(pg_total_relation_size(cl.oid)) AS size
FROM pg_class cl
LEFT JOIN pg_namespace n ON (n.oid = cl.relnamespace)
LEFT JOIN pg_stat_user_tables s ON (s.relid =cl.oid)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(cl.oid) DESC

12. Get size of all tables

SELECT *, pg_size_pretty(total_bytes) AS total
, pg_size_pretty(index_bytes) AS INDEX
, pg_size_pretty(toast_bytes) AS toast
, pg_size_pretty(table_bytes) AS TABLE
FROM (SELECT *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM (
SELECT c.oid,nspname AS table_schema, relname AS TABLE_NAME
, c.reltuples AS row_estimate
, pg_total_relation_size(c.oid) AS total_bytes
, pg_indexes_size(c.oid) AS index_bytes
, pg_total_relation_size(reltoastrelid) AS toast_bytes
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE relkind = 'r') a) a;

13. Get table metadata

SELECT relname, relpages, reltuples, relallvisible
, relkind, relnatts, relhassubclass, reloptions
, pg_table_size(oid)
FROM pg_class WHERE relname='<table_name_here>';

14. Get table structure / DESCRIBE a table

SELECT column_name, data_type
, character_maximum_length
FROM INFORMATION_SCHEMA.COLUMNS where table_name = '<table_name_here>';

-- Does the table have anything unusual about it?
-- a. contains large objects
-- b. has a large proportion of NULLs in several columns
-- c. receives a large number of UPDATEs or DELETEs regularly
-- d. is growing rapidly
-- e. has many indexes on it
-- f. uses triggers that may be executing database functions, or is calling functions directly


15. Get Lock connection count


16. Get locks_relation_count

SELECT relation::regclass AS relname
, COUNT(DISTINCT pid) AS count

17. Get locks_statement_duration

SELECT a.query AS blocking_statement
, EXTRACT('epoch' FROM NOW() - a.query_start) AS blocking_duration
FROM pg_locks bl JOIN pg_stat_activity a
ON a.pid = bl.pid WHERE NOT bl.GRANTED;


18. Get missing indexes

SELECT relname
,seq_scan - idx_scan AS too_much_seq,
WHEN seq_scan - coalesce(idx_scan, 0) > 0
THEN 'Missing Index?'
,pg_relation_size(relname::regclass) AS rel_size, seq_scan, idx_scan
FROM pg_stat_all_tables
WHERE schemaname = 'public' AND pg_relation_size(relname::regclass) > 80000
ORDER BY too_much_seq DESC;

19. Get Unused Indexes

SELECT indexrelid::regclass as index
, relid::regclass as table
, 'DROP INDEX ' || indexrelid::regclass || ';' as drop_statement
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0 AND indisunique is false;

20. Get index usage stats

t.tablename AS "relation",
c.reltuples AS num_rows,
pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_tables t
LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch, indexrelname, indisunique FROM pg_index x
JOIN pg_class c ON c.oid = x.indrelid
JOIN pg_class ipg ON ipg.oid = x.indexrelid
JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
AS foo
ON t.tablename = foo.ctablename
WHERE t.schemaname='public'


21. Get TOP 10 costly queries

SELECT  r.rolname,
round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage_cpu ,
round(total_time::numeric, 2) AS total_time,
round(mean_time::numeric, 2) AS mean,
substring(query, 1, 800) AS short_query
FROM pg_stat_statements
JOIN pg_roles r ON r.oid = userid
ORDER BY total_time DESC


22. Get TOP cached tables

SELECT relname AS "relation"
, heap_blks_read AS heap_read
, heap_blks_hit AS heap_hit
, ((heap_blks_hit*100) / NULLIF((heap_blks_hit + heap_blks_read), 0)) AS ratio
FROM pg_statio_user_tables;


23. Last Autovaccum

SELECT relname as "relation"
, EXTRACT (EPOCH FROM current_timestamp-last_autovacuum) as since_last_av
, autovacuum_count as av_count
, n_tup_ins
, n_tup_upd
, n_tup_del
, n_live_tup
, n_dead_tup
FROM pg_stat_all_tables
WHERE schemaname = 'public'
ORDER BY relname;


24. List all table partitions (as Parent/Child relationship)

nmsp_parent.nspname AS parent_schema
, parent.relname AS parent
, child.relname AS child
, CASE child.relkind WHEN 'r' THEN 'table'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 'v' THEN 'view'
WHEN 'm' THEN 'materialized view'
ELSE 'other'
END AS type
, s.n_live_tup AS total_rows
FROM pg_inherits
JOIN pg_class parent
ON pg_inherits.inhparent = parent.oid
JOIN pg_class child
ON pg_inherits.inhrelid = child.oid
JOIN pg_namespace nmsp_parent
ON nmsp_parent.oid = parent.relnamespace
JOIN pg_namespace nmsp_child
ON nmsp_child.oid = child.relnamespace
JOIN pg_stat_user_tables s
ON s.relid = child.oid
WHERE child.relkind = 'r'
ORDER BY parent,child;