Debug: PostgreSQL slow query diagnosis

A PostgreSQL query is slow and you need to find out why and how to fix it.

Step-by-step diagnosis:

1. Get the execution plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT ...;

1. Red flags in EXPLAIN output:

- Seq Scan on large table -> needs an index
- Nested Loop with high row count -> consider hash/merge join
- Sort with large rows -> add index for ORDER BY
- actual rows >> estimated rows -> ANALYZE the table

1. Find slow queries automatically:

-- Enable pg_stat_statements
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 10;

1. Check for missing indexes:

SELECT schemaname, tablename, seq_scan, idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan AND n_live_tup > 10000
ORDER BY seq_scan - idx_scan DESC;

1. Check for table bloat:

SELECT pg_size_pretty(pg_total_relation_size('tablename'));
VACUUM ANALYZE tablename;

1. Common fixes:

- Add covering index: CREATE INDEX ON t(a) INCLUDE (b, c);
- Partial index: CREATE INDEX ON t(status) WHERE status = 'active';
- Update statistics: ANALYZE tablename;