demo/postgresql-performance-first-aid

0 0 v1

demov1created via API4 hours ago 1

Find the slowest queries

Requires the pg_stat_statements extension.

$SELECT query, calls, mean_exec_time FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

Look for sequential scans on big tables

$SELECT relname, seq_scan, idx_scan FROM pg_stat_user_tables ORDER BY seq_scan DESC LIMIT 10;

EXPLAIN ANALYZE the worst offender

$EXPLAIN (ANALYZE, BUFFERS) <your query>;

Add the missing index

Index the columns used in WHERE/JOIN/ORDER BY.

$CREATE INDEX CONCURRENTLY ON orders (customer_id, created_at);

Check for table bloat / autovacuum lag

$SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Verify connection pool size vs max_connections

PostgreSQL performance first aid