Tự tính cost của một truy vấn bằng tay, khớp EXPLAIN đến từng số lẻ — rồi đổi một dòng, kế hoạch lật hẳn

Một ứng dụng chỉ đường không đo đường thật rồi mới bảo bạn đi lối nào — nó ước lượng thời gian mỗi tuyến từ một mô hình: đường cao tốc đi được bao nhanh, đường phố bao nhiêu, kẹt xe cộng thêm bao nhiêu. Đổi một giả định trong mô hình — ví dụ "giờ này cao tốc đang kẹt" — và nó chọn tuyến khác hẳn, dù bản đồ y nguyên. Bộ tối ưu truy vấn của PostgreSQL chọn kế hoạch đúng theo kiểu app chỉ đường đó: con số cost là ước lượng từ mô hình, không phải thời gian đo thật. Bài này là lần tôi tự tính lại con số đó bằng tay cho khớp, rồi ngã ngửa khi đổi đúng một tham số làm cả kế hoạch lật.

Ở bài phần 1 tôi cảnh báo đừng đọc cost như thời gian, và bài phần 3 tách rows ước lượng khỏi actual rows. Nhưng cost thực ra là gì, tính từ đâu ra? Bài này mổ vào chính con số đó: tôi sẽ tự tính lại cost của một truy vấn bằng tay từ vài tham số cấu hình, xem nó có khớp con số PostgreSQL in ra không — rồi phát hiện một điều về bản chất của cost khiến tôi phải sửa lại cách nghĩ.

Cost là một mô hình, không phải thời gian

Con số cost mà planner gán cho mỗi kế hoạch không phải mili giây, cũng không phải đo được — nó là kết quả của một mô hình chi phí: một công thức cộng dồn từ vài tham số cấu hình. Bốn tham số chính:

• seq_page_cost = 1.0 — chi phí đọc một trang tuần tự (liền kề trên đĩa). Đây là đơn vị gốc: mọi cost khác đo tương đối so với "một lần đọc trang tuần tự".
• random_page_cost = 4.0 (mặc định) — đọc một trang ngẫu nhiên (nhảy vị trí). Đắt gấp 4 lần đọc tuần tự, vì mô hình giả định ổ đĩa quay phải quay đầu đọc tới đúng chỗ.
• cpu_tuple_cost = 0.01 — chi phí CPU xử lý một hàng.
• cpu_operator_cost = 0.0025 — chi phí một phép toán/so sánh.

Với một quét tuần tự toàn bảng, công thức rất gọn: cost = số_trang × seq_page_cost + số_hàng × cpu_tuple_cost. Planner tính cost như vậy cho mọi kế hoạch khả dĩ (seq scan, index scan, bitmap...) rồi chọn cái có cost thấp nhất — đúng như app chỉ đường tính giờ cho mọi tuyến rồi chọn tuyến nhanh nhất. Cost không phải để nói cho bạn biết truy vấn mất bao lâu; nó là thước đo tương đối để planner so sánh và chọn.

Đo: tự tính lại cost, khớp đến từng số lẻ

Tôi tạo một bảng một triệu hàng và đọc thống kê thật của nó từ pg_class: relpages = 5406 (bảng chiếm 5406 trang), reltuples = 1000000. Rồi EXPLAIN SELECT * FROM t (quét toàn bảng):

Seq Scan on t  (cost=0.00..15406.00 rows=1000000)

PostgreSQL nói cost = 15406.00. Giờ tôi tự tính lại bằng tay từ công thức:

đọc trang : 5406 × seq_page_cost(1.0)  = 5406.00
xử lý hàng: 1.000.000 × cpu_tuple(0.01) = 10000.00
tổng                                    = 15406.00

Khớp chính xác đến từng số lẻ. Cost không phải con số huyền bí; nó là số học đơn giản trên số trang và số hàng, với các hệ số bạn có thể đọc bằng SHOW seq_page_cost. Việc tự tính lại và khớp được xác nhận rằng ta hiểu đúng planner đang cân nhắc gì: nó đếm trang phải đọc và hàng phải xử lý, nhân với chi phí đơn vị, rồi cộng.

Cost không cố định theo dữ liệu

Tính khớp được 15406.00, tôi khoái chí, tưởng đã "giải mã" xong cost và kế hoạch. Trong đầu tôi khi đó, cost là một thuộc tính cố định của cặp (truy vấn, dữ liệu): cùng truy vấn trên cùng bảng thì phải ra cùng kế hoạch. Nghe hiển nhiên.

Để kiểm, tôi lấy một truy vấn lọc khoảng 25% số hàng và chạy EXPLAIN hai lần, chỉ đổi một tham số cấu hình giữa hai lần — random_page_cost từ 4.0 (mặc định) xuống 1.1:

random_page_cost = 4.0  ->  Bitmap Heap Scan
random_page_cost = 1.1  ->  Index Scan

Kế hoạch đổi hẳn — từ Bitmap Heap Scan sang Index Scan — dù tôi không sửa một hàng dữ liệu nào. Cùng bảng, cùng truy vấn, cùng thống kê, mà planner chọn khác. Theo kỷ luật đo lường, hai kết quả khác nhau trên cùng đầu vào nghĩa là có một biến ẩn — và biến ẩn ở đây chính là tham số cấu hình.

Đó là chỗ tôi đo trật: tôi tưởng cost (và kế hoạch) là hàm của dữ liệu, nhưng nó là hàm của dữ liệu cộng với mô hình chi phí. Cost là mô hình, và các tham số của mô hình — random_page_cost và bạn bè — là những giả định về phần cứng mà bạn có thể chỉnh, đúng như "giả định cao tốc đang kẹt" trong app chỉ đường. random_page_cost = 4.0 giả định một ổ đĩa quay, nơi đọc ngẫu nhiên (như index scan phải làm) đắt gấp 4 lần đọc tuần tự. Trên SSD, đọc ngẫu nhiên gần như rẻ ngang tuần tự, nên giá trị hợp lý là ~1.1 — và khi tôi đặt vậy, index scan (vốn đọc nhiều trang ngẫu nhiên) đột nhiên rẻ hơn trong mắt planner, nên nó chọn index. Chỉnh tham số là đổi thế giới quan của planner, và thế giới quan đổi thì lựa chọn đổi, dù dữ liệu đứng yên.

Điều rút ra: cost là đầu ra của một mô hình với các tham số cấu hình, không phải một đại lượng đo được của dữ liệu. Khi cùng một truy vấn cho hai kế hoạch khác nhau, đừng ngạc nhiên — hãy tìm biến ẩn: một SET nào đó, một tham số khác giữa hai môi trường. Rất nhiều chuyện "kế hoạch tốt trên máy dev, tệ trên máy chủ" gốc rễ là cost parameter khác nhau, không phải dữ liệu.

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

Điều đầu tiên, rất thực tế: random_page_cost mặc định 4.0 là dành cho ổ đĩa quay; nếu bạn chạy SSD, nên hạ xuống ~1.1. Với mặc định 4.0, planner định giá index scan (đọc ngẫu nhiên) đắt hơn thực tế trên SSD, nên nó ngại dùng index và hay chọn seq scan — dẫn tới truy vấn chậm mà EXPLAIN trông vẫn "hợp lý" theo cost. Đây là một trong những chỉnh cấu hình có tác động lớn nhất trên PostgreSQL hiện đại, và nó thuần túy là chuyện điều chỉnh mô hình cho khớp phần cứng thật.

Điều thứ hai: hiểu cost giúp đọc vì sao planner chọn kế hoạch này thay kế hoạch kia. Khi bạn thấy planner "cố chấp" quét tuần tự dù có index, EXPLAIN cho bạn hai con số cost để so; thường thủ phạm là ước lượng số hàng sai (bài phần 3) hoặc cost parameter không khớp phần cứng. Cost là ngôn ngữ planner dùng để ra quyết định — đọc được nó là hiểu được quyết định, thay vì chỉ bực vì "sao nó không dùng index của tôi".

Và cái nhận thức "một hệ ra quyết định bằng mô hình chi phí có tham số, không bằng đo trực tiếp" này là chìa khóa để hiểu — và chỉnh — rất nhiều thứ ngoài CSDL. JIT compiler quyết định inline hay không dựa trên một cost model (kích thước method, tần suất gọi) có ngưỡng chỉnh được (-XX:MaxInlineSize). Kubernetes scheduler xếp pod theo một mô hình điểm số có trọng số cấu hình được. Trình tối ưu của trình biên dịch cân cost/benefit của mỗi phép biến đổi. Điểm chung — và cái bẫy chung — là: mô hình chỉ tốt ngang các tham số của nó, và tham số mặc định được đặt cho một môi trường giả định có thể không phải môi trường của bạn. Mẫu số chung đáng mang theo: khi một hệ "tự động chọn giúp bạn" mà chọn có vẻ dở, đừng vội cho là nó ngu — hỏi nó đang tối ưu theo mô hình nào và tham số mô hình đó có khớp thực tế của bạn không; sửa tham số (như random_page_cost cho SSD) thường hiệu quả hơn nhiều so với ép tay từng quyết định. Gói lại bằng số: cost là đầu ra của một mô hình — cost seq scan = số_trang × 1.0 + số_hàng × 0.01, khớp EXPLAIN đến từng số lẻ (15406.00) — và các tham số của mô hình (như random_page_cost) là giả định về phần cứng có thể chỉnh, nên đổi tham số làm planner đổi kế hoạch dù dữ liệu y nguyên; cost là đơn vị tương đối (1.0 = một lần đọc trang tuần tự), không phải mili giây.

Muốn tự thấy thì trong psql, chạy SHOW random_page_cost; và SHOW seq_page_cost; để xem mô hình chi phí của cơ sở dữ liệu bạn đang giả định gì về phần cứng. Nếu random_page_cost là 4.0 mà máy chủ chạy SSD, đó là một cơ hội chỉnh: thử SET random_page_cost = 1.1; (chỉ trong phiên hiện tại) rồi chạy lại EXPLAIN một truy vấn hay bị quét tuần tự — bạn có thể thấy planner chuyển sang index scan. Và để tự kiểm tra công thức: đọc SELECT relpages, reltuples FROM pg_class WHERE relname = 'ten_bang';, nhân relpages × 1.0 + reltuples × 0.01, rồi so với cost của EXPLAIN SELECT * FROM ten_bang; — nó sẽ khớp, và bạn vừa tự tay xác nhận cost chỉ là số học đơn giản.