PostgreSQL EXPLAIN ANALYZE & Composite Index — Cặp bài trùm query chậm | Blog

PostgreSQL EXPLAIN ANALYZE & Composite Index — Cặp bài trùm query chậm

Anh em dev Backend chắc ai cũng từng gặp cảnh: app chạy ngon trên local, lên production query chậm như rùa. Căn bệnh "N+1 query" thì quen rồi, nhưng ngay cả một query đơn lẻ cũng có thể chậm nếu không có index phù hợp. Bài này mình chia sẻ cách dùng EXPLAIN ANALYZE và composite index — hai công cụ mình dùng hằng ngày để trị query chậm.

EXPLAIN ANALYZE — "Bảng điểm" của query

Trước khi tối ưu, phải đo. PostgreSQL cho bạn công cụ đo chính xác từng bước:

EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT * FROM orders 
WHERE user_id = 42 AND status = 'pending' 
ORDER BY created_at DESC 
LIMIT 20;

Kết quả trả về cho bạn thấy ngay:

Seq Scan — đang quét toàn bộ bảng. Dấu hiệu thiếu index rõ ràng.
Index Scan — có dùng index, nhưng có thể chưa tối ưu.
Bitmap Heap Scan — đang gộp nhiều index riêng lẻ.
Actual time — số đầu là time tới row đầu tiên, số sau là time tới row cuối. Nếu số đầu lớn, index bị chậm do random I/O.

Mẹo thực tế: nếu thấy "Seq Scan on orders (cost=0.00..12345.67 rows=50000)" trên production, bạn đang có vấn đề. Cost 12345 nghĩa là PostgreSQL ước tính query này đắt gấp 12345 lần một page read — tín hiệu đỏ.

Composite Index — Ít mà chất

Nhiều bạn cứ thấy cột nào xuất hiện trong WHERE là tạo index riêng. Sai lầm! PostgreSQL có thể Bitmap Scan để gộp nhiều index đơn, nhưng không hiệu quả bằng một composite index đúng thứ tự.

-- Cách làm của người mới: 2 index riêng
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);

-- Cách làm của người từng cháy production: 1 composite index
CREATE INDEX idx_orders_user_status_created 
ON orders(user_id, status, created_at DESC);

Quy tắc vàng khi thiết kế composite index: cột equals → cột range → cột sort.

Vị trí	Loại	Ví dụ
1	WHERE col = ?	user_id
2	WHERE col = ?	status
3	ORDER BY col	created_at DESC

Với index đúng thứ tự, PostgreSQL dùng Index Only Scan — không cần động vào bảng chính, dữ liệu đã có sẵn trong index. Đây là cấp độ tối ưu cao nhất.

Kinh nghiệm thực tế

Mình từng gặp một query báo cáo chạy 12 giây trên production. Bảng transactions có 5 triệu rows. Dùng EXPLAIN ANALYZE phát hiện Seq Scan trên cột merchant_id và created_at. Thêm composite index (merchant_id, created_at DESC), query còn 45ms — nhanh gấp 260 lần.

Nhưng cẩn thận: composite index không phải càng nhiều càng tốt:

Mỗi index thêm vào làm chậm INSERT/UPDATE khoảng 10-20%
Index nhiều cột quá (5+) ít khi được dùng trọn vẹn
Nguyên tắc: tối đa 3-4 cột, chỉ index những cột thực sự xuất hiện đồng thời trong WHERE + ORDER BY

Vài tips nữa

Partial Index cho dữ liệu có filter cố định:

CREATE INDEX idx_orders_pending ON orders(created_at DESC) 
WHERE status = 'pending';

Include columns (PostgreSQL 11+) nếu cần lấy thêm dữ liệu mà không động vào bảng:

CREATE INDEX idx_orders_user ON orders(user_id) 
INCLUDE (total_amount);

Kết luận

Muốn query nhanh không cần phức tạp:

Luôn dùng EXPLAIN (ANALYZE, BUFFERS) trước khi tối ưu
Composite index cho query có nhiều điều kiện WHERE
Thứ tự cột: equals → range → sort
Partial index cho dữ liệu có filter cố định
Chọn lọc, đừng tạo index tràn lan

Query chậm là chuyện thường ngày ở dev. Miễn là bạn biết đọc bảng điểm (EXPLAIN) và chọn đúng vũ khí (index), production nào cũng cứu được.