PostgreSQL query chậm? 5 bước debug mình luôn làm

Mỗi lần app chậm, 80% nguyên nhân nằm ở database query. Đây là quy trình debug mình dùng:

Bước 1: Tìm query chậm

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

Nếu chưa enable pg_stat_statements:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Bước 2: EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders
WHERE user_id = 123
AND status = 'pending'
ORDER BY created_at DESC
LIMIT 10;

Chú ý:

• Seq Scan trên bảng lớn = thiếu index
• • Nested Loop với rows cao = query plan tệ
• • Buffers shared read cao = data không nằm trong cache

Bước 3: Kiểm tra index

-- Xem index nào đang được dùng
SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC;

-- Index không ai dùng (candidate để xóa)
SELECT indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public';

Bước 4: Tạo index đúng

Sai:

CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

Đúng — composite index theo thứ tự filter:

CREATE INDEX idx_orders_user_status ON orders(user_id, status, created_at DESC);

Thứ tự column trong composite index rất quan trọng:

1. Equality conditions trước (user_id = ?, status = ?)
2. 2. Range/sort conditions sau (created_at DESC)

Bước 5: Kiểm tra connection pool

SELECT count(*), state
FROM pg_stat_activity
GROUP BY state;

Nếu thấy nhiều idle connections = app giữ connection không release. Dùng PgBouncer hoặc giảm pool size.

Bonus: Slow query log

Thêm vào postgresql.conf:

log_min_duration_statement = 500  # Log query > 500ms

Reload: SELECT pg_reload_conf();

Đây là 5 bước cơ bản. Bạn có tip nào khác để optimize PostgreSQL không?