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;