Composite index trong PostgreSQL: chọn thứ tự cột Cơ bản

Thời gian đọc ước tính: 9 phút

Cần đọc trước

B-tree index trong PostgreSQL hoạt động thế nào

Tóm tắt nhanh

Index (a, b, c) sắp key như sổ danh bạ: theo a, trong mỗi a theo b, trong mỗi (a, b) theo c. Index chỉ thu hẹp được vùng quét khi điều kiện nối liền từ cột trái sang phải.

Quy tắc	Ý nghĩa
Cột equality đứng trước	`a = ?`, `b = ?` tạo ra một dải liền nhau
Cột range hoặc `ORDER BY` đứng sau cùng	Cột đầu tiên có range là cột cuối còn thu hẹp được vùng quét
Thiếu cột đầu	PG17 trở xuống: gần như chắc chắn Seq Scan. PG18: có thể skip scan (xem bên dưới)
Thiếu cột giữa	Index vẫn dùng được, chỉ quét rộng hơn

Tài liệu PostgreSQL phát biểu chính xác: equality constraints trên các cột đầu, cộng inequality trên cột đầu tiên không có equality, luôn được dùng để giới hạn phần index phải quét. Điều kiện trên các cột bên phải được kiểm tra trong index (đỡ phải ghé heap) nhưng không nhất thiết thu hẹp phần phải quét.

Thí nghiệm công bằng: ba query, cùng hình dạng

Bảng activity_demo 200.000 dòng, 100 tenant, 2 status, một index:

CREATE INDEX idx_esr ON activity_demo (tenant_id, status, created_at DESC);

Cả ba query đều có ORDER BY created_at DESC LIMIT 10, đều chạy 3 lần trên bảng thật, số liệu là lần thứ ba (warm):

-- A: đủ cả ba cột
SELECT id, created_at FROM activity_demo
WHERE tenant_id = 42 AND status = 'open' AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

-- B: thiếu tenant_id (cột đầu)
SELECT id, created_at FROM activity_demo
WHERE status = 'open' AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

-- C: thiếu status (cột giữa)
SELECT id, created_at FROM activity_demo
WHERE tenant_id = 42 AND created_at >= '2026-01-02'
ORDER BY created_at DESC LIMIT 10;

Query	Plan	Buffers	Thời gian	Dòng thoả điều kiện
A: đủ 3 cột	Index Scan, không `Sort`	13	0.012 ms	10 (dừng ở `LIMIT`)
C: thiếu cột giữa	Bitmap Index + Heap Scan, `Sort` top-N	586	0.30 ms	1.136
B: thiếu cột đầu	Parallel Seq Scan, `Sort` top-N	1.927	5.8 ms	56.801

Buffers là thước đo ổn định hơn thời gian ở quy mô micro-giây. Lưu ý B không đọc ít hơn vì index vô dụng ở đây: query này có 56.801 dòng thoả điều kiện và không có dải nào để nhảy tới.

Cùng một index, ba cách chạm vào các dải key

tenant 41 · 42 · closed · 42 · open · tenant 43

Bước 1/4

Index sắp key theo thứ tự (tenant_id, status, created_at): các dải liền kề nhau như họ rồi mới tới tên trong sổ danh bạ.

Vì sao ra con số đó

A: tenant_id = 42, status = 'open' là equality, created_at >= ... là range đứng cuối. Cả ba tạo thành một dải liền. Vì created_at DESC nằm sẵn trong index, ORDER BY ... LIMIT 10 đọc 10 entry đầu rồi dừng. Không có Sort.

Limit (actual time=0.006..0.008 rows=10 loops=1)
  ->  Index Scan using idx_esr on activity_demo (actual rows=10 loops=1)
        Index Cond: ((tenant_id = 42) AND (status = 'open') AND (created_at >= '2026-01-02 ...'))
        Buffers: shared hit=13

C: status bị bỏ, nên dải của tenant 42 gồm cả closed lẫn open. Index quét toàn bộ entry của tenant 42 (2.000 entry), kiểm tra created_at ngay trong index, giữ lại 1.136 entry thoả điều kiện. Con số 1.136 là số dòng khớp, không phải số entry quét. Sau đó phải ghé heap rồi Sort.

Limit
  ->  Sort (Sort Key: created_at DESC, top-N heapsort)
        ->  Bitmap Heap Scan on activity_demo (actual rows=1136) Heap Blocks: exact=573
              ->  Bitmap Index Scan on idx_esr
                    Index Cond: ((tenant_id = 42) AND (created_at >= '2026-01-02 ...'))
                    Buffers: shared hit=13

B: không có tenant_id, các dòng open rải trong mọi tenant. Planner chọn Parallel Seq Scan: loại 143.199 dòng, rồi sort top-N.

Equality trước, range sau

Cột range đặt giữa làm các cột sau nó mất khả năng thu hẹp vùng quét. Thí nghiệm: 200.000 dòng, status = 'urgent' chỉ khoảng 1%, query lấy toàn bộ urgent của tenant 42 từ giữa trưa (không LIMIT):

WHERE tenant_id = 42 AND status = 'urgent' AND created_at >= '2026-01-01 12:00'

Index	Thứ tự	Buffers (Index Only Scan, warm)
`(tenant_id, status, created_at)`	equality, equality, range	4
`(tenant_id, created_at, status)`	equality, range, equality	11

Index thứ hai buộc phải đi qua mọi status trong khoảng thời gian rồi lọc status bên trong index. Bảng demo nhỏ nên chênh lệch chỉ 2.7 lần; với tenant có hàng triệu dòng, khoảng cách tăng theo kích thước dải.

Cũng cho thấy vì sao status (2-3 giá trị) vẫn đứng trước created_at. Khẩu quyết “cột nhiều giá trị khác nhau đứng đầu” không phải quy tắc của composite index: cardinality chỉ là heuristic yếu, thứ quyết định là equality hay range và hình dạng WHERE thật.

PostgreSQL 18: skip scan

PG18 thêm skip scan cho B-tree: có thể dùng index khi thiếu cột đầu, bằng cách lặp lại tìm kiếm cho từng giá trị của cột đó. Đây là điều đã xác minh trong release notes và tài liệu PG18 (chưa được chạy trong demo ở bài, vì demo dùng PG17).

Tài liệu nêu rõ điều kiện: chỉ hiệu quả khi cột bị bỏ có ít giá trị khác nhau, để planner bỏ qua phần lớn index.

Với tenant_id hàng nghìn giá trị, toàn bộ index vẫn phải quét và planner thường chọn seq scan. Kết luận không đổi: đặt cột bạn luôn lọc ở đầu, đừng trông chờ skip scan thay thế một index đúng.

Khi nào dùng / không dùng

Dùng composite index khi	Cân nhắc kỹ khi
Nhiều query cùng lọc equality trên cùng nhóm cột, kèm range/sort	Có hơn 3 cột: tài liệu nói hiếm khi có ích trừ khi workload cực kỳ cố định
Muốn `ORDER BY ... LIMIT` đọc thẳng từ index, không `Sort`	Chỉ một query hiếm dùng cột đầu khác: index riêng rẻ hơn kéo dài index lớn
Muốn Index Only Scan (thêm `INCLUDE`, xem covering index)	Bảng ghi nhiều: mỗi index là chi phí ghi và dung lượng

Index dư thừa: (tenant_id) là tiền tố của (tenant_id, status, created_at), nên thường có thể xoá, vì index dài phục vụ được cả query chỉ lọc tenant_id. Đo trước bằng pg_stat_user_indexes rồi mới xoá.

Ví dụ thực tế

Bảng activity có hai kiểu query, cùng phục vụ bởi index (tenant_id, status, created_at DESC):

Query	Hình dạng	Cách index phục vụ
“Hoạt động đang mở của tenant X, mới nhất trước”	Equality `tenant_id` + `status`, sort `created_at DESC`	Đọc đúng 10 entry
“Mọi hoạt động gần đây của tenant X”	Thiếu `status` (kiểu C ở trên)	Quét dải của tenant X nhưng không rơi về Seq Scan

Nếu xuất hiện báo cáo nội bộ lọc status xuyên mọi tenant, đừng sửa index này. Thêm index riêng, thường là partial index WHERE status = 'open'.

Trade-offs

Ưu điểm

Một index phục vụ nhiều query khi điều kiện đều khớp tiền tố bên trái
Bỏ cột giữa vẫn dùng được phần index phía trước nó
Khi các cột trước đã cố định bằng equality, ORDER BY theo cột kế tiếp đọc sẵn từ index, không Sort

Nhược điểm

Thiếu cột đầu: PG17 trở xuống gần như chắc chắn Seq Scan; PG18 chỉ cứu được khi cột đầu có ít giá trị
Mỗi thứ tự cột chỉ tối ưu cho một nhóm hình dạng query; hai nhóm khác hẳn cần hai index
Index dài tốn dung lượng và chi phí ghi; index nhiều cột thường là dấu hiệu cần xem lại thiết kế

Sai lầm thường gặp

Checklist khi thiết kế composite index

Liệt kê các hình dạng WHERE và ORDER BY thật của nhóm query cần phục vụ.
Cột đầu là cột mà query nào cũng lọc; đừng trông chờ skip scan của PG18 thay cho thứ tự đúng.
Cột equality đứng trước; cột range hoặc cột ORDER BY đứng cuối cùng. Không xếp theo cardinality.
Kiểm tra từng hình dạng bằng EXPLAIN (ANALYZE, BUFFERS): mong đợi Index Cond đủ cột và không có Sort khi có ORDER BY ... LIMIT.
Quá 3 cột hoặc có query hiếm dùng cột đầu khác: cân nhắc index riêng (hoặc partial index) thay vì kéo dài index.
Tìm index dư thừa là tiền tố của index dài hơn; xem pg_stat_user_indexes trước khi xoá.

Tóm tắt

Tự kiểm tra

Vì sao query A không có node Sort còn query C thì có?
Index (tenant_id, created_at, status) kém (tenant_id, status, created_at) ở điểm nào khi status = 'urgent'?
Skip scan của PG18 cứu được trường hợp nào, và không cứu được trường hợp nào?
Vì sao 1.136 trong plan C không phải số entry được quét?

Tham khảo

#postgresql #index #btree #query-planner