Kế hoạch ghi 'Index Only Scan' mà vẫn chạm bảng 29.976 lần — con số thật nằm ở chỗ khác

Hình dung một thủ thư trả lời câu hỏi thẳng từ tấm thẻ mục lục mà không cần ra kệ lấy sách. Nếu tấm thẻ đã ghi đủ thứ bạn hỏi — tác giả, năm, số trang — thì khỏi phải đi đâu cả, cực nhanh. Nhưng có một điều kiện ngầm: tấm thẻ có thể đã cũ. Nếu cuốn sách vừa bị sửa mà chưa ai xác nhận lại tấm thẻ, thủ thư buộc phải ra tận kệ kiểm cho chắc. Index Only Scan của PostgreSQL hành xử đúng như vậy, và cái điều kiện ngầm "tấm thẻ còn mới" chính là phần mà hầu như không ai nhắc tới. Index Scan bình thường làm hai việc: tìm trong chỉ mục, rồi nhảy sang bảng lấy dòng. Index Only Scan bỏ hẳn việc thứ hai — nhưng chỉ khi hai điều kiện cùng thoả.

Điều kiện thứ nhất: chỉ mục phải chứa mọi cột truy vấn cần

Ba bảng giống hệt nhau, 3.000.000 dòng, mỗi bảng một chỉ mục:

SELECT sum(tong) FROM ios WHERE kh BETWEEN 5 AND 5000;

Nhanh 6,6 lần, chỉ vì thêm một cột vào chỉ mục.

Điểm hay bị bỏ sót: cột cần có mặt không chỉ là cột trong WHERE mà mọi cột truy vấn chạm tới, kể cả trong SELECT, ORDER BY và GROUP BY. Truy vấn trên cần tong chỉ để cộng, nhưng thiếu nó là mất Index Only Scan.

Đây cũng là lý do SELECT * gần như không bao giờ được Index Only Scan — muốn thế thì chỉ mục phải chứa cả bảng.

Điều kiện thứ hai: trang phải được đánh dấu khả kiến

Đây là phần quan trọng nhất của bài.

Chỉ mục PostgreSQL không lưu thông tin phiên bản của dòng. Nó không biết dòng bạn vừa tìm được có còn sống với giao dịch hiện tại hay không. Để tránh phải hỏi bảng, PostgreSQL dùng bản đồ trang khả kiến — một bitmap đánh dấu những trang mà mọi dòng đều nhìn thấy được bởi mọi giao dịch. Đó chính là dấu "tấm thẻ mục lục còn mới".

VACUUM là thứ cập nhật bản đồ đó. Nên trạng thái của nó quyết định Index Only Scan có thật sự "only" hay không:

Chậm gấp đôi, và kế hoạch vẫn ghi Index Only Scan trong cả ba trường hợp.

Đây là điều cần nhớ khi đọc EXPLAIN: tên nút không nói lên chi phí. Con số cần nhìn là Heap Fetches, và nó chỉ hiện ra khi bạn chạy EXPLAIN (ANALYZE).

Hệ quả thực tế: bảng bị cập nhật liên tục sẽ mất dần lợi thế của chỉ mục phủ, và nó tự hồi phục sau mỗi lần autovacuum chạy. Nếu bạn thấy một truy vấn lúc nhanh lúc chậm không rõ lý do, hãy kiểm Heap Fetches — nó có thể đang dao động theo nhịp autovacuum.

INCLUDE khác cột khoá ở đâu

(kh, tong) và (kh) INCLUDE (tong) cùng cho Index Only Scan, nhưng chúng không thay thế nhau.

Cột INCLUDE không dùng để lọc:

WHERE kh BETWEEN 5 AND 5000 AND tong > 9000

Với cột khoá, điều kiện tong > 9000 thu hẹp luôn phạm vi quét. Với INCLUDE, nó chỉ là bộ lọc áp sau khi đã quét hết khoảng kh.

Cột INCLUDE không dùng để sắp xếp:

WHERE kh = 500 ORDER BY tong LIMIT 20

(kh, tong) mất 0,12 ms; (kh) INCLUDE (tong) mất 0,25 ms.

Nhưng INCLUDE làm được một việc mà cột khoá không làm được: giữ ràng buộc duy nhất chỉ trên phần khoá.

CREATE UNIQUE INDEX ON uq (ma) INCLUDE (ten);

chen ('A','mot') va ('B','hai')  -> OK
chen ('A','ba')                  -> ERROR: duplicate key value

Chỉ ma phải duy nhất, còn ten đi kèm để phục vụ Index Only Scan. Với UNIQUE (ma, ten) thì ('A','ba') sẽ lọt qua — một ràng buộc hoàn toàn khác.

Quy tắc chọn: cột có xuất hiện trong WHERE hoặc ORDER BY thì đưa vào phần khoá; cột chỉ để đọc ra thì đưa vào INCLUDE.

Cái giá của chỉ mục phủ

Chỉ mục phủ to hơn — ở đây là gấp hai tới ba lần. Và như phần 11 đã đo, chỉ mục to hơn nghĩa là mọi lần ghi tốn thêm.

Nên chỉ mục phủ đáng dùng cho truy vấn chạy rất nhiều lần và trả về nhiều dòng — đúng hai điều kiện đó thì 6,6 lần là khoản đáng đổi. Với truy vấn tìm một dòng theo khoá chính, việc nhảy sang bảng một lần chẳng tốn gì.

Một chi tiết về cách tôi đo

Ba lần đầu tôi đặt cả ba chỉ mục lên cùng một bảng và bộ tối ưu luôn chọn (kh) — chỉ mục nhỏ nhất — kể cả khi truy vấn cần tong. Kết quả là cả ba phép đo ra giống hệt nhau và không nói lên điều gì.

Phải tách thành ba bảng riêng, mỗi bảng đúng một chỉ mục, thì mới so được. Đây là cùng cách làm ở phần 12 — khi muốn so hai lựa chọn thiết kế, đừng để bộ tối ưu chọn giúp.

Mẫu số chung

Điều kiện thứ hai — "tấm thẻ phải còn mới" — chỉ ra một khuôn lặp khắp nơi: phục vụ câu trả lời từ một bản tóm tắt gọn thay vì đọc nguồn đầy đủ chỉ là một cái thắng chừng nào bản tóm tắt còn được tin là hiện thời. Vì thế mọi hệ thống kiểu này đều mang theo hai thứ: một dấu hợp lệ để biết bản tóm tắt còn tin được, và một đường quay về nguồn khi dấu đó vắng. Và hiệu năng thật của nó là tỉ lệ trúng trên cái dấu ấy — một con số mà tên của thao tác không bao giờ cho bạn thấy.

Cùng khuôn ấy, đổi ngăn xếp:

• HTTP cache phục vụ phản hồi mà không chạm máy chủ gốc — nhưng chỉ khi còn "tươi" (max-age, ETag); hết hạn thì phải revalidate với gốc. Index Only Scan so với Heap Fetch chính là cache hit so với một lần revalidate.
• Cache CPU và TLB phục vụ từ dòng cache nếu valid bit còn bật; miss hoặc bị vô hiệu hoá thì đi xuống bộ nhớ. Bản đồ trang khả kiến của PostgreSQL đúng là một mảng valid bit ở cấp bảng.
• Một materialized view hay replica đọc cho bạn đọc từ bản sao, nhưng độ trễ đồng bộ nghĩa là đôi khi vẫn phải về nguồn chính để chắc.

Nên câu hỏi đúng khi thấy một "đường tắt" kiểu này không nhanh như quảng cáo không phải "kế hoạch có ghi đúng tên tối ưu không" mà là "tỉ lệ trúng trên cái dấu hợp lệ của tôi là bao nhiêu". Và bài học đọc số đi kèm: đừng tin tên nút, hãy đọc bộ đếm miss — ở đây là Heap Fetches. Xem truy vấn của bạn có đang lặng lẽ chạm bảng không:

EXPLAIN (ANALYZE, BUFFERS) <truy van cua ban>;

Tìm dòng Heap Fetches:. Bằng 0 là tốt. Lớn hơn 0 nghĩa là bản đồ khả kiến chưa cập nhật — chạy VACUUM ten_bang rồi đo lại. Và kiểm tỉ lệ trang khả kiến của các bảng lớn:

SELECT relname,
       relpages,
       relallvisible,
       round(100.0 * relallvisible / nullif(relpages,0), 1) AS phan_tram_kha_kien
FROM pg_class
WHERE relkind = 'r' AND relpages > 1000
ORDER BY relpages DESC
LIMIT 10;

Bảng dưới 90% và bị truy vấn nhiều là ứng viên cho việc chỉnh autovacuum chạy thường xuyên hơn — phần 23 sẽ đo.

Phần sau đo GIN và tìm kiếm toàn văn trên dữ liệu tiếng Việt.