Một hướng dẫn viên giỏi tới đâu cũng vô dụng nếu bạn đưa cho họ một tấm bản đồ trắng. Họ không dốt — họ chỉ không có thông tin để mà chỉ đường, nên đành đoán "chắc đi thẳng khoảng một phần ba quãng đường thì tới". Đưa họ tấm bản đồ thật, cùng con người ấy chỉ đường chính xác tới từng ngã rẽ. Bộ tối ưu truy vấn (planner) của PostgreSQL cũng vậy: nó thông minh, nhưng chỉ tốt ngang tấm bản đồ nó cầm — và bài này là lần tôi mắng oan hướng dẫn viên trong khi lỗi là ở tấm bản đồ trắng.

Suốt các bài về kế hoạch truy vấn, ta liên tục thấy planner ước lượng số hàng để quyết định: chọn index hay quét bảng (phần 5), nested loop hay hash join, HashAggregate hay sort. Phần 3 còn đo ước lượng lệch xa thực tế. Nhưng planner lấy những ước lượng đó từ đâu? Câu trả lời là thống kê bảng, và bài này đo điều xảy ra khi thống kê vắng mặt — cùng một hiểu nhầm về "planner ngu" mà tôi mắc phải.

Planner nhìn dữ liệu qua thống kê

Planner không quét bảng để biết có bao nhiêu hàng khớp một điều kiện — làm vậy thì tính kế hoạch còn lâu hơn chạy truy vấn. Thay vào đó, nó dựa trên thống kê đã thu thập sẵn về bảng, lưu trong pg_statistic (xem đẹp qua pg_stats):

  • reltuples: tổng số hàng ước tính của bảng (trong pg_class).
  • n_distinct: số giá trị phân biệt của một cột.
  • most-common-values (MCV): những giá trị hay gặp nhất và tần suất của chúng.
  • histogram: phân bố giá trị chia thành các khoảng đều nhau.

Từ những con số này — chính là tấm bản đồ của hướng dẫn viên — planner ước tính "điều kiện WHERE v < 10 khớp bao nhiêu hàng" mà không cần đọc bảng: nếu histogram cho thấy v trải đều từ 0 tới 999, nó suy ra v < 10 chiếm khoảng 1% và ước lượng ~10.000 trên một triệu hàng. Lệnh ANALYZE là thứ thu thập thống kê: nó lấy mẫu một phần bảng, tính n_distinct, MCV, histogram, và ghi vào pg_statistic. Có thống kê tốt, planner ước lượng sát; thiếu chúng, nó phải đoán bằng các hằng số mặc định — và đoán thì hay sai.

Đo: 333350 hay 9872, tùy đã ANALYZE chưa

Tôi tạo một bảng, nạp một triệu hàng vào cột v (giá trị 0..999), tắt autovacuum để nó không tự ANALYZE, và không chạy ANALYZE. Rồi hỏi planner ước lượng WHERE v < 10 (thực tế khớp 10.000 hàng, tức 1%):

Trạng thái reltuples Ước lượng v < 10 So với thật (10000)
Chưa ANALYZE -1 (chưa biết) 333350 lệch ~33 lần
Sau ANALYZE 1000000 9872 khớp (~2%)

Khi bảng chưa được ANALYZE, pg_class.reltuples là -1 (dấu hiệu "chưa từng phân tích"), và planner ước lượng v < 10 sẽ khớp 333350 hàng — trong khi thực tế chỉ 10.000. Lệch hơn 33 lần. Vì sao? Không có histogram của cột v, planner không biết v phân bố ra sao, nên nó rơi về một hằng số mặc định: với một điều kiện khoảng (<), nó đoán bừa khoảng 1/3 số hàng khớp. Một phần ba của một triệu là ~333 nghìn — đúng con số vô lý kia, đúng câu "chắc đi thẳng một phần ba quãng đường".

Sau khi chạy ANALYZE, reltuples thành 1000000 (đúng), và ước lượng cho v < 10 là 9872 — sát thực tế 10.000 tới mức chỉ lệch chưa tới 2%. pg_stats giờ có n_distinct = 1000 (đúng), 8 most-common-values, và một histogram 100 khoảng — đủ để planner "nhìn thấy" rằng v < 10 chỉ chiếm 1% dữ liệu.

Suýt trách nhầm planner

Cái bẫy đến ngay ở con số 333350. Khi thấy planner ước lượng một điều kiện rõ ràng khớp 1% dữ liệu thành 33%, phản xạ của tôi là bực mình với chính planner: "sao nó ước lượng ngu thế, lệch cả mấy chục lần". Tôi định ghi vào ghi chú rằng planner của PostgreSQL đoán số hàng rất tệ.

Nhưng theo kỷ luật, một con số lệch xa như vậy đáng để dừng lại kiểm nguyên nhân, không đổ lỗi vội. Tôi nhìn pg_class.reltuples và thấy -1: bảng này chưa từng được ANALYZE. Planner không hề "ngu" — nó đang bị bịt mắt, cầm tấm bản đồ trắng. Không có thống kê, nó không có cách nào biết v phân bố ra sao, nên buộc phải dùng một con số đoán cứng (1/3 cho điều kiện khoảng). Con số 333350 không phải lỗi thuật toán ước lượng; nó là hệ quả tất yếu của việc thiếu dữ liệu để ước lượng. Chạy một lệnh ANALYZE, planner lập tức ước lượng 9872 — chính xác. Lỗi không nằm ở planner, mà ở thống kê vắng mặt.

Cái tôi trật là quên rằng dữ liệu vừa nạp thì chưa có thống kê, nên vội trách công cụ ước lượng thay vì hỏi "nó có dữ liệu để ước lượng chưa". Đây là mắt xích còn thiếu của phần 3 (ước lượng lệch actual) và phần 5 (planner bỏ index): rất nhiều "planner chọn kế hoạch tồi" thật ra gốc rễ là thống kê cũ hoặc thiếu. Khi ước lượng của planner lệch xa thực tế, đừng kết luận "planner dở" — kiểm reltuples và pg_stats trước, vì một planner mù thống kê buộc phải đoán, và cách chữa là cho nó "nhìn thấy" bằng ANALYZE.

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

Điều đầu tiên, rất thực tế: sau khi nạp hoặc thay đổi lớn dữ liệu, hãy chạy ANALYZE ngay. Nhập một lô lớn, TRUNCATE rồi nạp lại, hay đổi phân bố dữ liệu đáng kể — tất cả làm thống kê cũ đi, và cho tới khi ANALYZE chạy, planner ước lượng dựa trên bức tranh sai và có thể chọn kế hoạch tệ (nested loop khi nên hash, bỏ index, sort tràn đĩa không cần thiết). Đây là nguyên nhân kinh điển của "truy vấn nhanh trên môi trường test, chậm khủng khiếp ngay sau khi nạp dữ liệu production" — dữ liệu mới chưa được phân tích.

Điều thứ hai: autovacuum có chạy ANALYZE nền, nhưng có độ trễ. PostgreSQL tự động phân tích các bảng khi chúng thay đổi đủ nhiều, nên trong vận hành bình thường bạn hiếm khi phải ANALYZE tay. Nhưng autovacuum phản ứng sau khi dữ liệu đã đổi, và ngay sau một lần nạp lớn có thể có một cửa sổ mà thống kê còn cũ và các truy vấn chạy với kế hoạch tồi. Sau các thao tác nạp/di trú lớn, chạy ANALYZE tay ngay để không phải chờ autovacuum. Với cột cần ước lượng đặc biệt chính xác, có thể tăng độ chi tiết histogram bằng default_statistics_target hoặc đặt riêng cho cột.

Và cái nguyên tắc "một bộ tối ưu chỉ tốt ngang thống kê nó cầm" này chính là cách mọi optimizer dựa-trên-chi-phí (cost-based) hoạt động, ở mọi hệ. Mọi CSDL lớn — Oracle, SQL Server, MySQL — đều có lệnh thu thập thống kê tương tự (UPDATE STATISTICS, ANALYZE TABLE), và cùng cái bẫy "thống kê cũ → kế hoạch tồi". Rộng hơn nữa, bất kỳ hệ ra quyết định dựa-trên-dữ-liệu nào cũng thừa hưởng chất lượng của dữ liệu đầu vào: một mô hình học máy huấn luyện trên dữ liệu lệch sẽ dự đoán lệch, một cache dựa trên số liệu truy cập cũ sẽ giữ nhầm thứ, một autoscaler đọc metric trễ sẽ scale sai nhịp. Mẫu số chung đáng mang theo: trước khi trách một hệ "ra quyết định kém", hãy hỏi dữ liệu nó dựa vào có mới và đúng không — "rác vào, rác ra" áp dụng cho cả bộ tối ưu truy vấn lẫn mọi thứ quyết định bằng số liệu; và cách chữa thường không phải sửa thuật toán mà là làm tươi dữ liệu. Gói lại bằng số: planner ước lượng số hàng từ thống kê bảng (reltuples, n_distinct, MCV, histogram) do ANALYZE thu thập; thiếu thống kê, nó đoán bằng hằng số mặc định và lệch rất xa — bảng chưa ANALYZE ước lượng v<10 thành 333350 (lệch 33 lần), sau ANALYZE còn 9872 (khớp).

Muốn tự thấy thì với một bảng bất kỳ, xem lần ANALYZE gần nhất: SELECT relname, reltuples, last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname = 'ten_bang';. Nếu reltuples khác xa số hàng thật, hoặc bảng vừa nạp lớn mà chưa analyze, chạy EXPLAIN một truy vấn có WHERE và so rows= (ước lượng) với thực tế — bạn có thể thấy lệch lớn. Chạy ANALYZE ten_bang; rồi EXPLAIN lại: ước lượng sẽ sát hơn hẳn, đúng khác biệt bài này đo. Muốn nhìn planner "thấy" gì, xem SELECT n_distinct, most_common_vals FROM pg_stats WHERE tablename = 'ten_bang' AND attname = 'cot';.