Truy vấn `ORDER BY created_at DESC LIMIT 20` vẫn chậm dù bảng có index. Đánh index thế nào cho đúng?

Vấn đề nằm ở chỗ index có khớp được cả điều kiện lọc lẫn thứ tự sắp xếp hay không.

Nếu chỉ khớp phần lọc, database phải gom hết dòng khớp rồi sort toàn bộ mới lấy được 20 dòng đầu — LIMIT không giúp gì.

-- feed for one user
SELECT * FROM posts
WHERE user_id = $1
ORDER BY created_at DESC
LIMIT 20;

-- index on (user_id) alone: index scan then Sort node over all the user's posts
CREATE INDEX idx_posts_user_created ON posts (user_id, created_at DESC);

Với composite index (user_id, created_at DESC), các dòng của một user_id đã nằm liền nhau và đã đúng thứ tự — database chỉ đọc 20 entry đầu rồi dừng. Trong EXPLAIN bạn sẽ thấy node Sort biến mất, thay bằng Index Scan ... Limit.

Vài lưu ý:
- Cột lọc bằng = đứng trước, cột sắp xếp đứng sau. Đảo lại là mất tác dụng.
- Hướng sắp xếp: B-tree đọc được cả hai chiều nên (user_id, created_at) vẫn phục vụ được DESC. Chiều khai báo chỉ quan trọng khi sắp xếp nhiều cột ngược chiều nhau, ví dụ ORDER BY score DESC, created_at ASC cần đúng (score DESC, created_at ASC).
- NULLS FIRST/LAST cũng phải khớp; PostgreSQL mặc định NULLS LAST cho ASC và NULLS FIRST cho DESC.
- Nếu phân trang sâu, đổi từ OFFSET sang keyset pagination để cùng index này vẫn hiệu quả ở trang thứ 500.