Đánh index lên cột boolean rồi đợi truy vấn nhanh — nhưng PostgreSQL phớt lờ nó, và nó đúng

Tra mục lục cuối sách để tìm một từ xuất hiện đúng hai lần trong cả cuốn — tuyệt vời, lật thẳng tới hai trang đó. Nhưng tra mục lục để tìm một từ xuất hiện trên một nửa số trang? Vô nghĩa: cái mục lục sẽ liệt kê gần như mọi số trang, và bạn thà đọc thẳng cả cuốn từ đầu còn nhanh hơn nhảy tới nhảy lui theo danh sách dày cộp đó. Một cái mục lục chỉ đáng dùng khi thứ nó trỏ tới là hiếm. Chỉ mục cơ sở dữ liệu y hệt, và bài này là lần tôi đánh index lên một cột rồi chưng hửng vì cơ sở dữ liệu nhất quyết không thèm dùng.

Ba bài trước ca ngợi chỉ mục: nhanh 400 lần, cây rất nông. Nên dễ rút ra kết luận sai: "cứ đánh index lên cột hay lọc là truy vấn nhanh". Bài này đo mặt kia của tấm huy chương — khi nào một cột có index mà truy vấn vẫn không dùng nó, và vì sao đó là quyết định đúng của cơ sở dữ liệu. Tôi vấp đúng cái giả định "có index thì phải dùng index".

Index chỉ có lợi khi truy vấn chọn lọc

Chỉ mục nhanh nhờ nó cho phép bỏ qua phần lớn bảng: đi qua cây B-tree tới đúng vài hàng cần, rồi nhảy vào bảng lấy chúng. Cái lợi đó chỉ có nghĩa khi "vài hàng" thật sự là vài — khi truy vấn chọn lọc (selective), trả về một phần nhỏ số hàng, đúng như từ chỉ xuất hiện hai lần trong sách.

Khi truy vấn khớp nhiều hàng — chẳng hạn 50% bảng — bức tranh lật ngược. Dùng index để lấy nửa bảng nghĩa là nhảy vào gần như mọi trang của bảng, mỗi lần một cú nhảy ngẫu nhiên (đọc ngẫu nhiên đắt gấp 4 lần đọc tuần tự, như bài cost model đã đo). Trong khi đó, quét tuần tự đọc toàn bộ bảng đúng một lượt, theo thứ tự đĩa, không nhảy nhót. Với đủ nhiều hàng, quét thẳng cả bảng một lần rẻ hơn nhảy vào từng trang qua index. Planner tính cost cả hai và chọn cái rẻ hơn — và ở độ chọn lọc thấp, nó bỏ index, chọn seq scan.

Đo: index thắng khi 1%, bị bỏ khi 50%

Tôi tạo bảng một triệu hàng với cột v (1000 giá trị, đánh index) và cột kich_hoat kiểu boolean (chỉ true/false, cũng đánh index). Trước hết một truy vấn chọn lọc — WHERE v < 10 (khoảng 1% số hàng):

index (Bitmap Heap Scan) : ~2,0 ms
ép quét tuần tự          : ~9,3 ms

Index nhanh hơn khoảng 4,7 lần — đúng như kỳ vọng, vì chỉ cần lấy 1% hàng. Giờ một truy vấn không chọn lọc — WHERE kich_hoat = true, khớp khoảng 50% bảng. Dù cột kich_hoat có index, EXPLAIN cho:

Seq Scan on t  (cost=... rows=502133)

Planner chọn quét tuần tự, bỏ hoàn toàn index idx_kh. Nó tính ra rằng để lấy nửa bảng, quét thẳng một lượt rẻ hơn nhảy vào gần hết số trang qua index. Đây không phải lỗi — đó là lựa chọn đúng: với 50% hàng, index chẳng giúp được gì, chỉ tổ tốn thêm bước, đúng như cái mục lục liệt kê nửa số trang. Để kiểm chứng, tôi thử ép quét tuần tự trên chính truy vấn v < 10 chọn lọc: nó chậm hẳn (9,3 ms so với 2,0 ms) — đúng là ở đó index mới thật sự đáng giá. Cùng một cơ chế index, mà một truy vấn thì nó là vũ khí, truy vấn kia thì nó là gánh nặng; khác biệt nằm ở độ chọn lọc, không nằm ở bản thân index.

"Có index thì phải dùng index"

Cái bẫy tôi bước vào nằm ở cột boolean. Tôi đánh index idx_kh lên kich_hoat, và đinh ninh rằng từ nay WHERE kich_hoat = true sẽ chạy nhanh nhờ index — tôi đã tạo nó ra cơ mà. Trong đầu tôi có một quy tắc ngầm: "cột có index thì truy vấn trên cột đó dùng index".

Nhưng EXPLAIN phũ phàng cho Seq Scan — planner ngó lơ index của tôi hoàn toàn. Lúc đầu tôi ngờ mình cấu hình sai, hay planner "hỏng". Theo kỷ luật, tôi kiểm lại: đây không phải công cụ sai, mà là giả định của tôi sai. Planner chỉ dùng index khi truy vấn chọn lọc. kich_hoat là boolean — chỉ hai giá trị — nên truy vấn tốt nhất cũng chỉ lọc còn ~50% bảng, không bao giờ đủ chọn lọc để index có lợi. Index trên một cột hai giá trị gần như vô dụng cho việc đọc.

Tệ hơn, nó không chỉ vô dụng mà còn có hại: mỗi index phải được cập nhật ở mỗi lần INSERT/UPDATE/DELETE trên bảng, nên một index không bao giờ được dùng để đọc vẫn âm thầm làm chậm mọi thao tác ghi và tốn dung lượng đĩa. Cái tôi đo trật — "tạo index là truy vấn sẽ dùng" — dẫn thẳng tới thói quen đánh index bừa lên mọi cột, tạo ra một đống index chết chỉ làm chậm ghi mà chẳng tăng tốc đọc nào.

Điều rút ra: một chỉ mục không phải cái công tắc bật-là-nhanh; nó là một lựa chọn mà planner cân nhắc theo độ chọn lọc của từng truy vấn. Đừng đo "cột có index chưa" mà hãy đo "truy vấn này có thật sự dùng index không" — chạy EXPLAIN và nhìn planner chọn gì. Index không "sai"; nó chỉ không hợp với truy vấn không chọn lọc, và cơ sở dữ liệu đủ khôn để bỏ qua nó.

Về đến code hằng ngày

Điều đầu tiên: đừng đánh index lên cột có ít giá trị phân biệt (boolean, cột trạng thái với vài giá trị, cột giới tính). Không truy vấn nào trên chúng đủ chọn lọc để index có lợi, nên index chỉ tổ tốn chỗ và làm chậm ghi. Ngoại lệ đáng nói là khi phân bố lệch nhiều — ví dụ cột da_xoa mà 99,9% là false và bạn hầu như chỉ truy vấn 0,1% true; khi đó một partial index (WHERE da_xoa = true) mới đáng, nhưng đó là chuyện khác. Quy tắc chung: index đáng giá khi cột có nhiều giá trị phân biệt và truy vấn lọc còn ít hàng.

Điều thứ hai: một index tồn tại không đảm bảo nó được dùng — luôn kiểm bằng EXPLAIN. Rất nhiều người thêm index rồi yên tâm rằng truy vấn đã nhanh, mà không kiểm tra planner có thật sự chọn nó không. Có thể index không được dùng vì truy vấn không chọn lọc (như bài này), vì kiểu dữ liệu không khớp (so sánh text với số), vì có hàm bọc quanh cột (WHERE lower(email) = ... không dùng index trên email thường), hoặc vì thống kê cũ. EXPLAIN là cách duy nhất để biết chắc, thay vì tin rằng "đã có index thì xong".

Và cái bẫy sâu nhất ở đây — thêm một tối ưu rồi cho là nó đang hoạt động, mà không kiểm — vượt xa index. Lập trình viên thêm một cache mà không đo hit rate (có khi tỉ lệ trúng gần 0, chỉ tốn RAM); thêm một @Index trên ORM entity mà không xem query planner có dùng; bật một cờ tối ưu compiler mà không benchmark; parallel-hóa một vòng lặp mà không đo nó có thật sự nhanh hơn (đôi khi overhead nuốt hết, như bài song song đã đo). Mọi tối ưu đều là một giả thuyết, và giả thuyết chưa đo là niềm tin, không phải sự thật. Mẫu số chung đáng mang theo: mỗi tối ưu bạn thêm vào — index, cache, cờ, luồng — đều phải trả lời câu hỏi "nó có thật sự được kích hoạt và có thật sự giúp không", đo bằng công cụ (EXPLAIN, profiler, metric) chứ không bằng niềm tin; và một tối ưu không được dùng thường không trung tính mà mang chi phí ẩn (chậm ghi, tốn bộ nhớ, thêm phức tạp). Gói lại bằng số: index nhanh khi truy vấn chọn lọc (v < 10, 1% hàng: nhanh 4,7 lần), nhưng bị planner bỏ khi truy vấn khớp nhiều hàng (boolean 50%: seq scan) — vì lấy nửa bảng qua index đắt hơn quét thẳng một lượt.

Muốn tự thấy thì trong cơ sở dữ liệu của bạn, tìm một truy vấn lọc trên cột có ít giá trị (một cột trạng thái, một boolean) và chạy EXPLAIN nó — nhiều khả năng bạn thấy Seq Scan dù cột đó có index, vì nó không đủ chọn lọc. Rồi kiểm kho index của bạn: SELECT indexrelname, idx_scan FROM pg_stat_user_indexes ORDER BY idx_scan; — cột idx_scan là số lần mỗi index thật sự được dùng. Những index có idx_scan = 0 (hoặc rất thấp) sau một thời gian chạy là index chết: chúng làm chậm mọi lần ghi mà không tăng tốc đọc nào, và thường an toàn để xóa. Đó là cách đo index nào đang giúp thật, thay vì đếm xem bạn đã tạo bao nhiêu.