Troubleshooting PostgreSQL Performance

Troubleshooting PostgreSQL Performance

  1. Check for long-running queries:

SELECT pid, now() – query_start AS duration, query
FROM pg_stat_activity
WHERE now() – query_start > interval ‘5 minutes’
ORDER BY duration DESC;

This script uses the pg_stat_activity view to find all queries that have been running for more than 5 minutes. It calculates the duration of each query and orders the results by the longest duration first.

  1. Check for index usage:

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_all_indexes
ORDER BY idx_scan DESC;

This script uses the pg_stat_all_indexes view to show all indexes in use. It includes the number of times each index has been scanned (idx_scan), the number of tuples read from the index (idx_tup_read), and the number of tuples fetched from the heap (idx_tup_fetch).

  1. Check for locking issues:

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.query AS blocked_query, blocking_locks.pid AS blocking_pid, blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.GRANTED;

This script uses the pg_locks view to find all blocking locks and the queries that are causing them. It shows the PID and query text for both the blocked and blocking queries.

  1. Check for vacuum issues:

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_vacuum IS NULL OR last_autovacuum IS NULL OR last_analyze IS NULL OR last_autoanalyze IS NULL;

This script uses the pg_stat_user_tables view to find tables that have not been vacuumed or analyzed recently. It checks the last vacuum and analyze times, as well as the last autovacuum and autoanalyze times, and returns only tables where at least one of these values is NULL.