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 (trongpg_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, 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 — ví dụ 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.
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.
Một lần tôi đo hớ: 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. 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 đo hớ 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. Bài học đo lường: 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ì sao điều này quan trọng khi lập trình
Hệ quả đầ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.
Hệ quả 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.
Hệ quả thứ ba là bài học đo lường, khép lại chuỗi bài về ước lượng. Con số mang theo: 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). Một ước lượng lệch không có nghĩa "planner dở"; nó thường có nghĩa "thống kê thiếu hoặc cũ". Đo bằng cách so estimated với actual trong EXPLAIN, rồi kiểm pg_stats — và ANALYZE là cách cho planner nhìn thấy dữ liệu thật.
Thử ba mươi giây
Với một bảng bất kỳ, xem thống kê của nó: SELECT reltuples, last_analyze, last_autoanalyze FROM pg_stat_user_tables JOIN pg_class USING ... — hoặc đơn giản SELECT relname, reltuples, last_vacuum, last_analyze FROM pg_stat_user_tables WHERE relname = 'ten_bang'; cho biết bảng được ANALYZE lần cuối khi nào. 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';.
