Index Only Scan tránh đọc heap bằng cách nào?

Index trên plate chỉ giữ biển số và địa chỉ của dòng. Giá bán, số km và mọi cột khác nằm trong heap.

Nên câu tra giá này luôn có hai bước:

SELECT price_vnd
FROM vehicles
WHERE plate = '47P-600.55';

Index tìm ra địa chỉ (474,21). PostgreSQL đọc heap page 474, lấy giá 205 triệu, rồi trả kết quả.

Một lần tra thì thế là ổn. Nhưng một dịch vụ tra giá nhận yêu cầu liên tục, và mỗi lần tìm thấy biển số lại phải đọc thêm một heap page. Trên bảng hàng triệu xe, các page đó nằm rải rác, nên chi phí cộng dồn qua từng lượt tra.

Có đáng lưu thêm giá vào index để bớt một lần đọc heap không?

Lưu thêm giá vào index

INCLUDE gắn thêm cột vào index mà không dùng cột đó để tìm:

CREATE INDEX vehicles_plate_price_idx
ON vehicles (plate) INCLUDE (price_vnd);

Cây vẫn tìm đường theo plate. price_vnd chỉ nằm kèm ở mỗi mục tầng lá. Tìm thấy 47P-600.55 là có luôn giá 205 triệu.

Index chứa đủ mọi cột câu lệnh cần gọi là index bao phủ (covering index). Với nó, PostgreSQL có thể chạy Index Only Scan: trả kết quả từ index, không đọc heap, miễn là phiên bản dòng đã được xác nhận là hợp lệ.

Cái giá đầu tiên là chỗ. Trên bộ dữ liệu thực hành, index tăng từ 150 MB lên 193 MB, gần 29%.

Mỗi page index giờ chứa ít mục hơn, và index chiếm thêm chỗ trong bộ nhớ đệm.

Cái giá thứ hai đến khi giá thay đổi. Trước đây, đổi giá một chiếc xe có thể không phải chạm vào index nào. Giờ giá nằm trong index, nên mỗi lần đổi giá, UPDATE phải ghi thêm mục index mới.

Vậy INCLUDE đáng dùng khi câu truy vấn chạy rất thường xuyên, chỉ cần vài cột, và các cột đó ít thay đổi. Trang nào cần cả chiếc xe bằng SELECT * thì index có plate và price_vnd cũng không giúp được.

Nhưng có đủ cột trong index rồi, PostgreSQL vẫn chưa chắc bỏ qua được heap.

Index đã có đủ cột, sao vẫn phải vào heap?

Index không biết phiên bản nào được đọc

Giả sử người bán đang sửa giá chiếc Honda từ 205 lên 210 triệu. Lệnh UPDATE đã chạy, nhưng transaction chưa COMMIT.

Lúc này heap giữ cả hai phiên bản của chiếc xe: bản cũ giá 205 triệu, bản mới giá 210 triệu. Index cũng có một mục trỏ tới mỗi bản.

Một người khác tra biển số đúng lúc đó phải thấy 205 triệu, vì thay đổi chưa được xác nhận. PostgreSQL phải chọn phiên bản nào được phép xuất hiện trong kết quả.

Thông tin để chọn nằm trong heap. Index chỉ có biển số, giá và địa chỉ dòng. Thấy giá trong index chưa có nghĩa là được dùng giá đó.

Nếu lần tra nào cũng phải đọc heap để kiểm tra, INCLUDE mất gần hết lợi ích. PostgreSQL tránh điều đó bằng cách ghi nhớ những heap page an toàn, không cần kiểm tra từng dòng nữa.

Chỗ ghi nhớ đó là bản đồ hiển thị (visibility map). Mỗi heap page có một bit all-visible trong bản đồ:

• Bằng 1: VACUUM đã xác nhận mọi dòng còn trên page đều được mọi transaction thấy. Trả dữ liệu từ index được ngay.
• Bằng 0: chưa có xác nhận đó, phải đọc heap để kiểm tra. Bit 0 không có nghĩa dòng sai hay đã bị xoá.

Với xe 47P-600.55, index đã cho giá 205 triệu và địa chỉ (474,21). PostgreSQL xem bit của page 474 trước khi quyết định có đọc heap hay không:

Bản đồ không lưu gì cho từng xe, chỉ hai bit cho mỗi heap page 8 KB. Bảng xe nặng 1,5 GB, còn bản đồ của nó chỉ khoảng 48 KB, nên xem bản đồ thường rẻ hơn nhiều so với đọc heap.

Khi bit của page 474 bằng 1, EXPLAIN ANALYZE cho thấy:

Đọc kết quả EXPLAIN

1. Index Only Scan using vehicles_plate_price_idx on vehicles Lấy dữ liệu từ index khi phiên bản dòng đã được xác nhận.
2. Index Cond: (plate = '47P-600.55'::text) Tìm mục index theo biển số.
3. Heap Fetches: 0 Không phải đọc heap để kiểm tra dòng nào trong lần chạy này.

Nhưng Heap Fetches: 0 không cố định. Chỉ cần dữ liệu trên page thay đổi, PostgreSQL lại phải kiểm tra trong heap.

Vì sao sửa xe khác mà Honda cũng phải đọc heap?

Page 474 chứa chiếc Honda 47P-600.55 và 25 xe khác. Page đang được đánh dấu all-visible, nên tra giá Honda không phải đọc heap.

Giờ một xe khác trên cùng page 474 được cập nhật số km:

UPDATE vehicles
SET mileage_km = mileage_km + 1
WHERE id = 12325;

Giá Honda vẫn là 205 triệu, mục index của Honda cũng không đổi. Nhưng page 474 vừa có dữ liệu bị sửa, nên PostgreSQL xoá bit all-visible của cả page. Bit thuộc về page, không thuộc về từng xe.

Lần tra Honda tiếp theo, PostgreSQL thấy bit của page 474 bằng 0 và phải vào heap kiểm tra, dù giá đã có sẵn trong index. Heap Fetches tăng từ 0 lên 1. Con số này đếm lượt vào heap để kiểm tra dòng, không phải số lần đọc đĩa; page đó có thể đã nằm sẵn trong bộ nhớ đệm.

Sao UPDATE xong không tự bật bit lại? Vì PostgreSQL còn phải biết chắc phiên bản cũ không còn transaction nào cần. Việc đó thuộc về VACUUM.

VACUUM là gì và giúp Index Only Scan thế nào?

UPDATE tạo phiên bản mới thay vì ghi đè lên bản cũ. Bản cũ phải được giữ lại một thời gian, vì transaction bắt đầu trước đó có thể vẫn cần đọc nó. DELETE cũng không xoá hẳn dòng khỏi heap ngay.

Khi không còn transaction nào cần, bản cũ thành dòng chết (dead tuple). Cứ để dòng chết tích tụ thì bảng ngày càng tốn chỗ.

VACUUM là việc dọn dẹp định kỳ của PostgreSQL. Nó tìm những dòng chết đã dọn được, trả chỗ trống cho các lần ghi sau dùng lại, và dọn các mục index trỏ tới chúng.

VACUUM còn làm một việc liên quan trực tiếp tới bài này: cập nhật bản đồ hiển thị. Nếu mọi dòng còn trên page 474 đều được mọi transaction thấy, VACUUM đặt bit all-visible của page về 1.

Một điểm dễ nhầm: VACUUM thường không làm file bảng nhỏ đi, nó chỉ giải phóng chỗ bên trong bảng để dùng lại. VACUUM FULL là thao tác khác, viết lại cả bảng và khoá bảng chặt hơn; ví dụ này không cần nó.

Bình thường, autovacuum tự chạy việc dọn dẹp trong nền, không cần gọi VACUUM bằng tay sau mỗi UPDATE. Bài thực hành chạy tay:

VACUUM (INDEX_CLEANUP ON) vehicles;

INDEX_CLEANUP ON buộc VACUUM dọn index, kể cả khi số dòng chết ít đến mức nó định bỏ qua bước này. Một lệnh VACUUM vehicles; thường cũng bật lại được bit. Nhưng nếu bước dọn index bị bỏ qua, page còn sót con trỏ chết sẽ chưa được đánh dấu all-visible; trong lab, page 474 từng rơi đúng vào trường hợp đó.

Khi UPDATE đã COMMIT, không còn transaction nào cần bản cũ và VACUUM chạy xong, page 474 có thể được đánh dấu all-visible trở lại. Tra giá Honda lại không phải đọc heap.

Bấm cập nhật xe khác rồi chạy VACUUM, để ý bit của page 474:

Page 474 all-visible: 1

• Honda205 triệu
• Xe khác50.000 km

Không cần đọc heap · Heap Fetches: 0

Honda vẫn là 205 triệu ở cả ba trạng thái. Chỉ có việc PostgreSQL phải vào heap kiểm tra hay không là thay đổi.

Bảng bị sửa liên tục thì khác. Nhiều page mất bit trước khi VACUUM kịp chạy, Index Only Scan phải vào heap thường xuyên, và 43 MB index thêm vào có thể chẳng mang lại bao nhiêu.

INCLUDE đổi dung lượng index và chi phí ghi lấy những lần đọc heap được bỏ qua. Nó hợp nhất với câu truy vấn chạy nhiều trên dữ liệu ít thay đổi.

Tới giờ, mọi câu đều tìm theo một cột. Trang danh sách xe thì lọc theo tỉnh và ngày đăng cùng lúc, nên cần index có cả hai cột.

Sao đổi thứ tự hai cột lại đổi tốc độ?