Bảng đơn hàng của bạn có 5 triệu dòng, nhưng 97% đã hoàn thành và bạn gần như không bao giờ tra tới chúng nữa. Cái bạn truy vấn suốt ngày là 3% đơn đang chờ xử lý — dashboard vận hành, hàng đợi công việc. Vậy tại sao lại index cả 5 triệu dòng, phần lớn là "rác" không bao giờ được tra? PostgreSQL có câu trả lời gọn gàng: partial index — index chỉ trên những dòng thỏa một điều kiện.

Index có điều kiện WHERE

Partial index thêm mệnh đề WHERE vào CREATE INDEX: chỉ những dòng thỏa điều kiện mới được đưa vào index. Kết quả là một index nhỏ hơn rất nhiều.

Ảnh chụp mã SQL nền tối về partial index. Bảng 5 triệu đơn 97 phần trăm hoan_thanh chỉ 3 phần trăm cho_xu_ly nhưng bạn hầu như chỉ truy vấn đơn đang chờ dashboard xử lý. Index thường đánh cả 5 triệu dòng phần lớn là rác không bao giờ tra CREATE INDEX idx_full ON dh3 trang_thai 34 MB. Partial index chỉ đánh 3 phần trăm dòng thỏa điều kiện CREATE INDEX idx_partial ON dh3 khach_id WHERE trang_thai bằng cho_xu_ly 2,6 MB nhỏ 13 lần. Planner dùng partial index khi điều kiện truy vấn bao hàm điều kiện index. Dấu tích WHERE trang_thai bằng cho_xu_ly AND khach_id bằng 555 khớp dùng. Dấu chéo WHERE trang_thai bằng hoan_thanh AND khach_id bằng 555 không khớp bỏ. Lợi index nhỏ hơn nhiều nhẹ RAM nhanh và ghi cũng nhẹ hơn chỉ cập nhật index khi dòng thỏa điều kiện 97 phần trăm ghi khỏi đụng index

Hình 1: CREATE INDEX ... WHERE trang_thai = 'cho_xu_ly' chỉ index 3% số dòng. Planner dùng partial index khi điều kiện truy vấn bao hàm điều kiện của index — truy vấn lọc trang_thai='cho_xu_ly' thì dùng được, lọc 'hoan_thanh' thì không. Để ý một mẹo hay: cột index là khach_id, còn trang_thai nằm ở mệnh đề WHERE của index — nên index vừa nhỏ vừa phục vụ tra theo khách trong nhóm chờ xử lý.

Đo thật: 34 MB xuống 2,6 MB

Trên bảng 5 triệu đơn với 3% chờ xử lý:

Ảnh chụp kết quả đo thật nền tối partial index bảng 5 triệu đơn 3 phần trăm chờ xử lý. Kích thước index idx_full cả bảng 34 MB, idx_partial chỉ 3 phần trăm dòng 2,6 MB nhỏ hơn khoảng 13 lần. Một truy vấn khớp điều kiện chờ xử lý cộng khach_id, Index Scan using idx_partial cost 0.42 tới 8.44, Buffers shared read bằng 4 Execution Time 0.057 ms chỉ 4 trang. Hai truy vấn hoan_thanh không khớp điều kiện index, Seq Scan on dh3 partial index bị bỏ qua đúng vì nó chỉ chứa 3 phần trăm dòng. Chú thích partial index bằng đánh đúng phần dữ liệu nóng kinh điển đơn chưa xử lý tài khoản active bản ghi chưa xóa WHERE deleted_at IS NULL

Hình 2: Thật. Index đầy đủ trên trang_thai là 34 MB; partial index chỉ 2,6 MB — nhỏ hơn ~13 lần. Truy vấn khớp điều kiện (cho_xu_ly + khach_id) dùng idx_partial, chỉ chạm 4 trang, 0,057 ms. Còn truy vấn 'hoan_thanh' không khớp điều kiện → PostgreSQL bỏ qua partial index (đúng, vì nó không chứa những dòng đó).

Lợi ích kép: nhỏ hơn và ghi nhẹ hơn

Partial index thắng ở hai mặt, không chỉ dung lượng:

  • Nhỏ hơn → nhanh hơn và nhẹ RAM. Index 2,6 MB nằm gọn trong cache, cây nông hơn, tra nhanh hơn. Với bảng lớn, khác biệt còn rõ hơn.
  • Ghi nhẹ hơn. Đây là lợi ích ít người để ý: mỗi INSERT/UPDATE một đơn hoàn thành (97% số ghi) không đụng tới partial index, vì dòng đó không thỏa điều kiện. Chỉ 3% ghi (đơn chờ xử lý) mới cập nhật index. Nhớ bài trước: index làm chậm ghi — partial index giảm hẳn chi phí đó.
  • Còn cưỡng chế được ràng buộc bộ phận. CREATE UNIQUE INDEX ... WHERE is_active cho phép "duy nhất trong nhóm active" — ví dụ mỗi user chỉ một session active, nhưng nhiều session cũ đã đóng thì trùng thoải mái.

Những ca dùng kinh điển

Partial index tỏa sáng khi truy vấn của bạn luôn lọc theo một điều kiện cố định:

  • Bản ghi chưa xóa mềm: WHERE deleted_at IS NULL — mọi truy vấn thực tế đều lọc thế này, và bản đã xóa thường rất nhiều.
  • Trạng thái đang xử lý: WHERE status IN ('pending','processing') — hàng đợi công việc.
  • Cờ đặc biệt: WHERE is_featured, WHERE priority = 'high' — chỉ một phần nhỏ dòng.
  • Loại trừ giá trị phổ biến: WHERE country <> 'VN' nếu phần lớn dữ liệu là 'VN' và bạn hay tra phần còn lại.

Vài lưu ý

  • Điều kiện truy vấn phải bao hàm điều kiện index. PostgreSQL chỉ dùng partial index khi chắc chắn mọi dòng cần đều nằm trong đó. WHERE status='cho_xu_ly' khớp index WHERE status='cho_xu_ly'; nhưng WHERE status IN ('cho_xu_ly','huy') thì không dùng được index chỉ cho 'cho_xu_ly'.
  • Dùng hằng số trong điều kiện index. Điều kiện phải là biểu thức bất biến (status='x', col IS NULL), không phụ thuộc tham số runtime.
  • Đừng lạm dụng. Nếu truy vấn của bạn lọc theo nhiều giá trị khác nhau của cột, partial index cho một giá trị không giúp các giá trị khác — khi đó index thường (hoặc partial cho từng nhóm) mới hợp.

Ba ý mang về

  1. Partial index chỉ đánh những dòng thỏa WHERE — nhỏ hơn rất nhiều. Đo thật: 34 MB (full) xuống 2,6 MB (partial cho 3% dòng), nhỏ hơn ~13 lần.
  2. Lợi ích kép: index nhỏ hơn (nhanh, nhẹ RAM) và ghi nhẹ hơn (97% số ghi không đụng index vì không thỏa điều kiện) — cộng khả năng cưỡng chế ràng buộc bộ phận.
  3. Điều kiện truy vấn phải bao hàm điều kiện index để planner dùng nó. Hợp cho dữ liệu có phần "nóng" cố định: bản chưa xóa, đơn đang chờ, cờ đặc biệt.

Partial index chọn dòng nào để index. Bài sau chọn dạng dữ liệu nào để index. Phần sau nói về expression index — index trên một hàm hay biểu thức (như lower(email)), giải quyết vấn đề kinh điển: vì sao WHERE lower(email) = ... không dùng được index thường trên email, và cách sửa.