Composite Index trong PostgreSQL — Vì sao index chồng index vẫn chậm?

Composite Index — hiểu đúng mới dùng đúng

Ai đã từng nghe "thêm index vào là query chạy nhanh" rồi... vẫn còn chậm, thì blog này dành cho bạn. Index một cột ai cũng biết, nhưng tới khi truy vấn kéo theo 2-3 cột điều kiện, nhiều dev lóng ngóng tạo tới 3 cái index riêng lẻ rồi thắc mắc tại sao chẳng khá hơn. Vấn đề nằm ở composite index (index nhiều cột) và quy tắc leftmost prefix.

B-tree và việc index hoạt động ra sao

Mặc định PostgreSQL dùng B-tree. Một composite index (a, b, c) về bản chất là một cây B-tree sắp xếp theo thứ tự tuple (a, b, c) — so a trước, bằng thì so b, bằng thì so c. Vì vậy index này có thể phục vụ query đúng thứ tự cột từ trái qua, nhưng không thể dùng khi bạn nhảy cóc.

Quy tắc Leftmost Prefix

Index (city, created_at) tạo ra, vậy những query nào dùng được?

• WHERE city = ? ✅
• WHERE city = ? AND created_at > ? ✅ (điều kiện phạm vi ở cuối)
• WHERE city = ? ORDER BY created_at ✅ (đã sắp xếp sẵn, khỏi sort lại)
• WHERE created_at > ? ❌ (bỏ qua cột trái — index vô dụng)

Câu số 4 là cái bẫy kinh điển. Vì cây chỉ sắp theo city trước, nên tra created_at không có city thì phải quét toàn bộ. Đó là lý do "có index mà vẫn quét 100k dòng".

Code ví dụ thực tế

Giả sử bảng orders khá lớn:

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,
  status TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Query hay gặp: lấy đơn đang pending của 1 user
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42 AND status = 'pending'
ORDER BY created_at DESC;

Index tối ưu nhất ở đây:

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

status đặt trước created_at vì nó là cột bằng nhau (equality), còn created_at là cột phạm vi/sắp xếp (range/order). Nguyên tắc vàng: cột equality đặt trước, cột range đặt sau. Đặt ngược là tự vô hiệu hoá về sau.

Covering Index — lấy thẳng từ index, khỏi quay lại bảng

Nếu query chỉ cần vài cột, thêm chúng vào index để biến nó thành covering index — PostgreSQL trả dữ liệu thẳng từ index, không phải lội ngược bảng (index-only scan).

-- SELECT user_id, status, created_at ...
CREATE INDEX idx_orders_cover
ON orders (user_id, status, created_at)
INCLUDE (total);

Chữ INCLUDE chứa cột phụ mà không tham gia so sánh — giúp tránh Heap Fetches (TOCTOU giữa index và heap). Xem bằng EXPLAIN ANALYZE, mục tiêu là thấy dòng Index Only Scan thay vì Index Scan kèm Heap Fetches cao.

Kinh nghiệm thực tế của mình

1. Quan sát query thật bằng pg_stat_statements trước — đừng đoán. Index mà không query nào dùng chỉ phí ghi.
2. Ít mà chuẩn hơn nhiều mà thừa — mỗi index là chi phí cho mỗi INSERT/UPDATE. Đừng vì "khoẻ" mà phủ index.
3. Luôn EXPLAIN (ANALYZE, BUFFERS) — con số thật hơn lời ngon.
4. Drop index sắp hết hạn — tạo thử, đo lại, không tối ưu thì xoá ngay.

Nguyên tắc nhớ đời: equality trước, range sau, cột select nhét vào INCLUDE. Làm đúng cái này, query của bạn từ vài trăm ms tụt còn vài ms.