Thấy Seq Scan trong kế hoạch, phản xạ đầu tiên của nhiều người là thêm chỉ mục. Nhưng có một ngưỡng mà quét toàn bộ bảng thật sự nhanh hơn, và bài này đo nó.
Bảng thử: 2.000.000 dòng, 70 MB, chỉ mục B-tree 13 MB.
Ép từng kế hoạch rồi bấm giờ
Tôi tắt lần lượt enable_seqscan và enable_indexscan để buộc PostgreSQL chọn từng đường, rồi đo thời gian thật:
| Tỉ lệ dòng khớp | Ép chỉ mục | Ép quét tuần tự | Bộ tối ưu tự chọn |
|---|---|---|---|
| 1% | 2,1 ms | 21,9 ms | Bitmap Heap Scan |
| 20% | 16,7 ms | 34,6 ms | Bitmap Heap Scan |
| 35% | 27,0 ms | 30,2 ms | Seq Scan |
| 45% | 33,1 ms | 33,3 ms | Seq Scan |
| 50% | 39,1 ms | 34,6 ms | Seq Scan |
Ở mức 1%, chỉ mục nhanh hơn 10 lần. Ở mức 50%, nó chậm hơn 13%.
Điểm hoà vốn đo được nằm khoảng 45% — cao hơn nhiều so với con số 5–10% mà người ta hay nhắc.
Nhưng để ý cột cuối: bộ tối ưu chuyển sang Seq Scan từ 35%, trong khi ở mức đó chỉ mục vẫn còn nhanh hơn (27,0 so với 30,2 ms). Nó chuyển sớm. Lý do nằm ở mục sau.
Vì sao chỉ mục thua khi lấy nhiều dòng
Index Scan làm hai việc: tìm trong chỉ mục, rồi nhảy sang trang dữ liệu để lấy dòng. Mỗi dòng là một lần nhảy, và các dòng liên tiếp trong chỉ mục thường nằm ở những trang rải rác khắp bảng.
Số trang phải đụng tới, đo được:
| Tỉ lệ | Đường chỉ mục | Cả bảng |
|---|---|---|
| 20% | 2.644 trang | 6.300 trang |
| 50% | 6.300 trang | 6.300 trang |
Ở mức 50%, đường qua chỉ mục đụng đúng bằng số trang của cả bảng — nhưng đụng theo thứ tự ngẫu nhiên, cộng thêm chi phí đọc chính chỉ mục. Quét tuần tự đọc cùng số trang theo thứ tự liền mạch, nên rẻ hơn.
Phần 9 đã thấy trước hiện tượng này: ở mức 9,9%, Bitmap Heap Scan đụng 14.875 trang trong khi Seq Scan trên 90% chỉ đụng 14.706.
Hai thứ dịch chuyển ngưỡng
random_page_cost
Đây là tham số nói cho bộ tối ưu biết đọc ngẫu nhiên đắt hơn đọc tuần tự bao nhiêu lần:
random_page_cost |
Bộ tối ưu chuyển sang Seq Scan ở |
|---|---|
| 4,0 (mặc định) | 32,5% |
| 2,0 | 37,5% |
| 1,1 | 40,0% |
Giá trị mặc định 4,0 có từ thời đĩa quay, khi một lần tìm kiếm ngẫu nhiên tốn hàng mili giây. Trên SSD, chênh lệch giữa đọc ngẫu nhiên và tuần tự nhỏ hơn nhiều; trên dữ liệu đã nằm trong bộ nhớ đệm thì gần như không có.
Đó chính là lý do bộ tối ưu chuyển ở 35% trong khi phép đo nói 45%: nó đang tính theo giả định một loại phần cứng mà máy tôi không dùng.
Với máy chủ dùng SSD, random_page_cost = 1.1 là khuyến nghị phổ biến và phép đo ở trên ủng hộ điều đó — nó đẩy ngưỡng từ 32,5% lên 40%, gần với 45% thật hơn.
Thứ tự vật lý của dữ liệu
Tôi dựng bảng thứ hai với cùng 2 triệu dòng nhưng dữ liệu xếp đúng theo thứ tự cột được đánh chỉ mục:
he so tuong quan: bang rai rac = 0,004 bang xep thu tu = 1,000
| Bộ tối ưu chuyển sang Seq Scan ở | |
|---|---|
| Tương quan 0,004 | 34% |
| Tương quan 1,000 | 50% |
Và thời gian thật trên bảng xếp thứ tự:
| Tỉ lệ | Ép chỉ mục | Ép quét tuần tự |
|---|---|---|
| 30% | 20,4 ms | 29,1 ms |
| 40% | 24,5 ms | 34,8 ms |
| 50% | 35,4 ms | 35,2 ms |
Ở mức 40%, chỉ mục vẫn nhanh hơn 1,4 lần. Khi dữ liệu nằm đúng thứ tự, "nhảy ngẫu nhiên" thật ra là đọc tuần tự, nên chỉ mục giữ được lợi thế lâu hơn nhiều.
PostgreSQL theo dõi con số này trong pg_stats.correlation, và bạn có thể xếp lại bảng bằng CLUSTER ten_bang USING ten_chi_muc — nhưng lệnh đó khoá bảng và thứ tự sẽ trôi dần khi có UPDATE, nên nó hợp với bảng chủ yếu đọc.
Ngoại lệ: Index Only Scan
Nếu truy vấn chỉ cần những cột đã nằm trong chỉ mục, PostgreSQL không phải chạm vào bảng chút nào:
| Tỉ lệ | Index Only Scan | Ép quét tuần tự |
|---|---|---|
| 10% | 11,2 ms | 23,5 ms |
| 50% | 25,0 ms | 33,4 ms |
Nó thắng ở cả mức 50%, vì toàn bộ phần "nhảy sang trang dữ liệu" biến mất. Lần đo đầu tôi dùng count(*) và thấy chỉ mục thắng ở mọi mức — phải đổi sang sum(id) với id không nằm trong chỉ mục thì ngưỡng thật mới hiện ra.
Đây là lập luận cho chỉ mục có cột đi kèm:
CREATE INDEX ON don (trang_thai) INCLUDE (tong);
Phần 15 sẽ đo kỹ Index Only Scan.
Khi nào đừng thêm chỉ mục
- Bảng nhỏ. Dưới vài nghìn dòng, cả bảng nằm gọn trong vài trang và quét tuần tự luôn thắng.
- Truy vấn lấy phần lớn bảng. Báo cáo tổng hợp cả tháng không cần chỉ mục trên cột trạng thái.
- Cột có ít giá trị khác nhau. Cột
booleanchia bảng thành hai nửa; chỉ mục trên đó gần như không bao giờ được dùng, trừ khi phân bố lệch hẳn. - Bảng ghi nhiều hơn đọc. Mỗi chỉ mục là thêm công việc cho mọi
INSERTvàUPDATE.
Và nhớ rằng Seq Scan trong kế hoạch không phải lỗi cần sửa. Nó chỉ là lỗi khi số dòng trả về nhỏ so với số dòng đọc lên — chính là con số Rows Removed by Filter ở phần 8.
Thử ba mươi giây
Xem dữ liệu của bạn nằm rải rác tới đâu:
SELECT tablename, attname, correlation
FROM pg_stats
WHERE schemaname = 'public' AND abs(correlation) < 0.1
ORDER BY tablename;
Cột nào gần 0 là cột mà chỉ mục sẽ mất lợi thế sớm.
Và kiểm tra random_page_cost của bạn có hợp với phần cứng không:
SHOW random_page_cost;
Nếu máy chủ dùng SSD mà con số vẫn là 4, bộ tối ưu đang từ chối chỉ mục sớm hơn cần thiết. Đổi trong postgresql.conf, đo lại vài truy vấn quan trọng, rồi mới áp dụng rộng.
Phần sau đi vào chỉ mục B-tree: nó thắng bao nhiêu khi đọc, và tốn bao nhiêu khi ghi.
