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ả, và điều kiện thứ hai thì hầu như không ai nhắc tới.
Đ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;
| Chỉ mục | Kế hoạch | Thời gian | Lượt đọc trang |
|---|---|---|---|
(kh) |
Index Scan | 53,22 ms | 150.021 |
(kh, tong) |
Index Only Scan | 8,63 ms | 50.235 |
(kh) INCLUDE (tong) |
Index Only Scan | 8,03 ms | 10.407 |
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.
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:
| Trạng thái bảng | Trang khả kiến | Heap fetch | Thời gian |
|---|---|---|---|
Ngay sau VACUUM |
100,0% | 0 | 7,91 ms |
Sau UPDATE 300.000 dòng, chưa VACUUM |
81,8% | 29.976 | 16,13 ms |
Sau khi VACUUM lại |
100,0% | 0 | 6,95 ms |
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
| Chỉ mục | Thời gian | Lượt đọc trang |
|---|---|---|
(kh, tong) |
2,68 ms | 4.263 |
(kh) INCLUDE (tong) |
5,01 ms | 10.407 |
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 | Dung lượng |
|---|---|
(kh) |
22 MB |
(kh, tong) |
42 MB |
(kh) INCLUDE (tong) |
64 MB |
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.
Thử ba mươi giây
Xem truy vấn của bạn có đang chạm bảng dù dùng Index Only Scan 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.
