Một index đầy đủ đánh chỉ mục mọi hàng của bảng. Nhưng có những trường hợp bạn chỉ bao giờ tra một phần nhỏ dữ liệu — các đơn hàng đang active trong một bảng mà 99% đã done, các bản ghi có cột hiếm khi khác NULL. Đánh index cả những hàng chẳng ai tra tới là lãng phí chỗ và làm chậm việc ghi. Partial index (chỉ mục một phần) giải quyết đúng điều đó: chỉ đánh index phần hàng thỏa một điều kiện. Nó nhỏ hơn hàng trăm lần — nhưng có một cái bẫy khiến nó lặng lẽ không được dùng, và tôi đã vấp đúng cái bẫy đó.
Index có điều kiện
Một partial index chỉ khác index thường ở một mệnh đề WHERE lúc tạo:
CREATE INDEX idx_active ON job (created) WHERE status = 'active';
Index này chỉ chứa entry cho những hàng có status = 'active'. Mọi hàng done — dù chiếm 99% bảng — hoàn toàn vắng mặt khỏi index. Điều đó có nghĩa: index nhỏ hơn nhiều, và khi bạn INSERT hay UPDATE một hàng không thỏa điều kiện, PostgreSQL bỏ qua index này luôn, không phải cập nhật gì.
Đây là công cụ đúng cho cột lệch: một cột mà gần như mọi hàng mang cùng một giá trị mà không ai tra, chỉ một thiểu số mang giá trị bạn thật sự cần tìm. Trạng thái công việc là ví dụ kinh điển; một cột deleted_at (hầu hết NULL) hay error_code (hầu hết không lỗi) cũng vậy. Nhớ lại bài quét chỉ mục: một index chỉ đáng dùng khi nó chọn ra một tập nhỏ; đánh index cả 990.000 hàng 'done' vừa vô ích cho việc tra (không ai lọc 'done' rồi mong index giúp — nó quá phổ biến) vừa tốn chỗ và tốn công bảo trì. Partial index cắt bỏ đúng phần vô ích đó.
Đo: nhỏ hơn 128 lần, ghi rẻ hơn 2 lần
Tôi dựng bảng job một triệu hàng, trong đó chỉ 1% là active (10.000 hàng), còn 99% là done (990.000 hàng). Rồi so một index đầy đủ trên (status, created) với một partial index chỉ trên các hàng active:
index đầy đủ (status, created): 30 MB
index một phần (WHERE status='active'): 240 kB
Partial index nhỏ hơn khoảng 128 lần — vì nó chỉ chứa 10.000 entry thay vì một triệu, đúng tỉ lệ 1% hàng active. Và cái lợi không dừng ở dung lượng. Việc ghi cũng rẻ hơn: mỗi hàng mới thêm vào một index đầy đủ đều phải chèn một entry, còn với partial index thì chỉ hàng thỏa điều kiện mới đụng tới. Tôi đo thời gian chèn 200.000 hàng done (đúng loại hàng chiếm đa số trong thực tế):
chèn 200k hàng 'done':
bảng có index đầy đủ: 158 ms
bảng chỉ có partial: 72 ms (rẻ hơn ~2 lần)
Vì các hàng done không thỏa điều kiện WHERE status='active', chúng hoàn toàn không đụng tới partial index — PostgreSQL bỏ qua bước bảo trì index cho chúng. Trên một bảng ghi nhiều mà đa số bản ghi là loại "không cần index", khoản tiết kiệm này cộng dồn đáng kể — mỗi INSERT một hàng 'done' không phải đi tìm chỗ trong cây B-tree để chèn, không sinh WAL cho index đó, không góp phần làm index phình. Một index nhỏ cũng nằm gọn hơn trong bộ đệm, nên phần active hay tra được phục vụ từ RAM thường xuyên hơn. Và truy vấn cần tra thì vẫn nhanh: SELECT ... WHERE status='active' AND created BETWEEN ... dùng đúng partial index qua một Index Scan, chạm đúng nhúm hàng active.
Một lần tôi đo hớ: có index mà vẫn seq scan
Đây là chỗ tôi tự sập bẫy. Sau khi tạo partial index và thấy truy vấn với status='active' dùng nó ngon lành, tôi tưởng mọi truy vấn tìm hàng active sẽ được phục vụ. Nhưng ứng dụng thật hiếm khi viết literal 'active' thẳng trong câu — nó dùng tham số. Tôi thử đúng kiểu đó:
PREPARE p(text) AS SELECT id FROM job WHERE status=$1 AND created BETWEEN 1000 AND 500000;
EXECUTE p('active');
Kết quả (với generic plan) làm tôi ngớ người:
Seq Scan on job ... Rows Removed by Filter: 995009
-> đọc 5406 trang, KHÔNG dùng partial index
Dù giá trị tham số lúc chạy đúng là 'active', PostgreSQL quét tuần tự cả bảng và bỏ đi 995.009 hàng — hoàn toàn phớt lờ partial index tôi vừa tạo. Lý do rất căn bản: để dùng một partial index, planner phải chứng minh rằng mọi hàng truy vấn cần đều nằm trong điều kiện WHERE của index. Với literal status='active', nó chứng minh được ngay. Với status=$1 và một kế hoạch chung (không biết $1 là gì lúc lập kế hoạch), nó không thể chứng minh $1 sẽ luôn bằng 'active' — nên nó không dám dùng index chỉ phục vụ active, và quay về seq scan.
Cái sai của tôi là nghĩ "index tồn tại thì truy vấn đúng cột sẽ dùng nó". Với partial index, điều kiện truy vấn phải khớp một cách chứng minh được với điều kiện của index. Đây là lý do phổ biến của than phiền "tôi tạo index rồi mà nó vẫn seq scan": không phải index hỏng, mà là truy vấn không nói đủ rõ để planner biết nó nằm trong phần đã được đánh index. Cách chữa: viết điều kiện literal khớp đúng (WHERE status='active') trong truy vấn, hoặc để planner dùng custom plan biết giá trị cụ thể — như bài prepared statement đã đo về generic so với custom plan.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: dùng partial index cho cột lệch, thay vì index đầy đủ. Nếu truy vấn của bạn luôn tìm một tập con nhỏ (đơn active, hàng chưa xử lý, bản ghi lỗi), một partial index cho đúng tập đó nhỏ hơn hàng trăm lần, ghi rẻ hơn, và phục vụ đúng nhu cầu. Đừng đánh index một triệu hàng khi bạn chỉ bao giờ tra mười nghìn.
Hệ quả thứ hai: điều kiện truy vấn phải khớp điều kiện index. Khi dùng partial index, kiểm bằng EXPLAIN rằng truy vấn thật của bạn thực sự dùng nó — đặc biệt khi câu lệnh đi qua ORM hay dùng tham số. Con số mang theo: partial index (WHERE điều_kiện) chỉ đánh phần hàng thỏa điều kiện — trên cột lệch 1% active nó nhỏ còn 240 kB so với 30 MB của index đầy đủ (128 lần), và chèn hàng không thỏa điều kiện rẻ hơn 2 lần; nhưng planner chỉ dùng nó khi chứng minh được truy vấn khớp WHERE của index, nên WHERE status=$1 với generic plan quay lại seq scan dù giá trị lúc chạy là active. Partial index là con dao sắc; chỉ cần chắc lưỡi dao cắt đúng chỗ bạn nhắm.
Thử ba mươi giây
Trong psql, tạo bảng lệch: CREATE TABLE j (id int, st text); INSERT INTO j SELECT g, CASE WHEN g%100=0 THEN 'active' ELSE 'done' END FROM generate_series(1,500000) g;. Tạo hai index và so kích thước: CREATE INDEX f ON j(st); CREATE INDEX p ON j(id) WHERE st='active'; rồi SELECT pg_size_pretty(pg_relation_size('f')), pg_size_pretty(pg_relation_size('p')); — bạn sẽ thấy partial nhỏ hơn hàng trăm lần. Giờ chạy EXPLAIN SELECT id FROM j WHERE st='active'; (thấy Index Scan dùng p) rồi EXPLAIN SELECT id FROM j WHERE st='done'; (thấy Seq Scan — partial không phục vụ 'done'). Nửa phút đó cho bạn thấy cả sức mạnh lẫn ranh giới của partial index: nó chỉ tồn tại cho phần bạn khai.
