PostgreSQL Performance Analysis2021-03-08
This document collects several queries to analyze a running PostreSQL server’s performance.
Index performance
Cache and Index hit ratio
Shows how many times (percentage) an index / a table can deliver content from Memory instead of disk. Should be high (> 90%), otherwise it is an indicator that the server may need more memory:
SELECT
'index hit rate' AS name,
(sum(idx_blks_hit)) / nullif(sum(idx_blks_hit + idx_blks_read),0) AS ratio
FROM pg_statio_user_indexes
UNION ALL
SELECT
'table hit rate' AS name,
sum(heap_blks_hit) / nullif(sum(heap_blks_hit) + sum(heap_blks_read),0) AS ratio
FROM pg_statio_user_tables;
Index usage percentage
Shows how many times an index is used when querying a table. As a rule of thumb: Tables with > 1’000 rows should have a high index usage (> 90%), otherwise queries are slow.
SELECT relname,
CASE idx_scan
WHEN 0 THEN 'Insufficient data'
ELSE (100 * idx_scan / (seq_scan + idx_scan))::text
END percent_of_times_index_used,
n_live_tup rows_in_table
FROM
pg_stat_user_tables
ORDER BY
n_live_tup DESC;
Unused indexes
The following query shows which indexes have a low index scan ratio. If it is low, it is either of a table that is seldomly read, or the index ins not used.
SELECT
schemaname || '.' || relname AS table,
indexrelname AS index,
pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
idx_scan as index_scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique AND idx_scan < 50 AND pg_relation_size(relid) > 5 * 8192
ORDER BY pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
pg_relation_size(i.indexrelid) DESC;