Statistics & cost model — planner ước tính row count như thế nào

TL;DR: Planner không nhìn dữ liệu thật khi lập plan; nó nhìn statistics, bản chụp phân bố từng cột mà ANALYZE lấy được từ một mẫu khoảng 30.000 row: số giá trị khác nhau (n_distinct), tỷ lệ NULL, danh sách giá trị phổ biến kèm tần suất (MCV) và histogram cho range. Từ đó nó tính selectivity của mỗi điều kiện WHERE, nhân với số row để ra rows ước tính, rồi nhân với các hệ số cost (seq_page_cost, random_page_cost…) để chọn plan rẻ nhất. Ước tính sai khi statistics cũ sau bulk load, khi MCV bỏ sót giá trị hiếm, hoặc khi planner nhân xác suất hai cột tương quan như thể chúng độc lập; ba nguyên nhân đó có ba cách sửa riêng.

Planner không đoán mù, nó tính cost từ statistics. Nhưng statistics chỉ là snapshot tại thời điểm ANALYZE chạy: data đổi nhiều mà statistics chưa cập nhật thì planner vẫn quyết định theo số liệu cũ. Phần lớn ca "tại sao planner chọn plan tệ" có gốc ở đây: planner không sai logic, nó đang dùng thông tin sai.

1. Analogy — bộ phận kế hoạch nhà hàng

Bếp trưởng lập kế hoạch mỗi ngày (bao nhiêu nguyên liệu, mấy đầu bếp, giờ nào tăng cường) dựa trên báo cáo doanh thu, không dựa trên khách đang ngồi trong quán.

2. ANALYZE — lấy mẫu, không quét hết

ANALYZE quét một mẫu ngẫu nhiên của bảng, tính statistics và lưu vào catalog. Sample size xấp xỉ 300 × default_statistics_target row; với target 100 mặc định là khoảng 30.000 row, đủ cho bảng 1 triệu row phân bố bình thường, có thể thiếu với 100 triệu row nhiều outlier.

ANALYZE tasks;             -- toan bo bang
ANALYZE tasks(status);     -- mot cot
ANALYZE VERBOSE tasks;     -- in so page scanned, so row sampled

Cần ANALYZE tay sau bulk insert/update lớn (autovacuum có độ trễ), sau khi đổi statistics_target, và khi EXPLAIN ANALYZE cho thấy rows ước tính lệch xa thực tế.

3. pg_stats — đọc statistics trực tiếp

pg_stats là view dễ đọc trên catalog pg_statistic, một dòng cho mỗi cột:

SELECT attname, n_distinct, null_frac, most_common_vals, most_common_freqs, histogram_bounds
FROM pg_stats WHERE tablename = 'tasks';

-- tasks.status (4 gia tri, phan bo lech):
-- n_distinct        | 4
-- most_common_vals  | {todo,doing,done,archived}
-- most_common_freqs | {0.70,0.20,0.08,0.02}
-- histogram_bounds  | NULL      <- MCV da cover 100% gia tri

-- tasks.due_at (timestamp, gan nhu unique):
-- n_distinct        | -1        <- am = ty le: -1 la moi gia tri unique
-- null_frac         | 0.05
-- most_common_vals  | NULL
-- histogram_bounds  | {2025-01-01,2025-03-15,...,2026-12-31}   <- 100 bucket cho range query

n_distinct dương là số tuyệt đối, âm là tỷ lệ so với số row (−1 = mọi giá trị unique). most_common_freqs là tần suất của từng giá trị trong MCV, tổng không quá 1. Cột text unique như title có cả MCV lẫn histogram NULL: planner phải rơi về heuristic.

4. Cost model — các hệ số sinh ra con số cost

Planner không tính bằng millisecond. Cost là đơn vị tương đối chuẩn hoá quanh chi phí đọc một page tuần tự 8 KB, và mỗi thao tác được gán một hệ số. GUC (Grand Unified Configuration) là tên PostgreSQL gọi các tham số cấu hình runtime như random_page_cost:

random_page_cost = 4 là lý do bài scan strategies đặt điểm hoà vốn Index Scan vs Seq Scan ở 5–15% selectivity. Giữ 4.0 trên SSD khiến planner tưởng random I/O đắt hơn thực tế, nên nghiêng quá tay về Seq Scan; hạ xuống 1.1 trong postgresql.conf sau khi đã thử bằng SET random_page_cost = 1.1 trong session và đo EXPLAIN ANALYZE trước/sau.

Thử ngẫmserver production vừa chuyển từ HDD sang NVMe nhưng random_page_cost vẫn giữ 4.0 mặc định. Planner đang định giá random I/O đắt hơn hay rẻ hơn thực tế phần cứng mới?

5. Planner tính selectivity như thế nào?

Selectivity là tỷ lệ row mà planner ước tính sẽ lọt qua một điều kiện. Với cột status ở trên:

WHERE status = 'todo'      -- co trong MCV: freq 0.70 -> 70% -> Seq Scan
WHERE status = 'archived'  -- co trong MCV: freq 0.02 -> 2%  -> Index Scan
WHERE status IN ('todo', 'doing')          -- cong freq: 0.90
-- gia tri KHONG co trong MCV:
--   (1 - tong_MCV_freq) / (n_distinct - so_gia_tri_trong_MCV)

WHERE due_at BETWEEN '2026-01-01' AND '2026-02-01'
-- dem so bucket histogram nam trong [X, Y] / tong so bucket

WHERE LOWER(title) = 'fix login bug'
-- planner KHONG biet phan bo cua LOWER(): hang so ~0.5%

Dòng cuối là bẫy hay gặp: bọc cột trong hàm thì mọi statistics của cột trở nên vô dụng và planner dùng một hằng số, bất kể data thật. Bài anti-pattern mổ pattern này và cách sửa.

6. Khi nào statistics tự cập nhật?

Auto-analyze là một worker của autovacuum daemon (cùng hạ tầng với VACUUM ở bài VACUUM & dead tuple). Nó chạy khi:

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × n_live_tup
                    = 50 + 0.1 × n_live_tup   (mac dinh)

Bảng 1 triệu row: ngưỡng là 100.050 row bị sửa. Bulk insert 200.000 row lúc 3 giờ sáng thì autovacuum sẽ tới, nhưng tuỳ tải có thể lag 10–30 phút, và trong khoảng đó planner dùng statistics của bảng cũ.

SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables WHERE relname = 'tasks';

ANALYZE tasks;   -- sau bulk load, khong cho autovacuum

Thử ngẫmmột bulk import 200.000 row chạy lúc nửa đêm, autovacuum chưa kịp trigger analyze. Dashboard query đầu giờ sáng dựa trên statistics của thời điểm nào?

7. statistics_target — mua độ chính xác bằng thời gian ANALYZE

default_statistics_target (100) quyết định sample size, số bucket histogram và độ dài MCV. Cột phân bố rất lệch (10% khách hàng chiếm 80% đơn) mà MCV chỉ giữ 100 giá trị thì khách ngoài top 100 rơi vào công thức "phần còn lại", ước tính rất thấp.

ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 1000;
ANALYZE orders;   -- bat buoc chay lai, doi target khong tu trigger ANALYZE

Chỉ tăng cho cột xuất hiện trong WHERE của query quan trọng, không tăng toàn cục.

8. Cross-column correlation — CREATE STATISTICS

Planner mặc định coi các cột độc lập khi tính selectivity cho điều kiện nhiều cột: nhân xác suất từng điều kiện với nhau. Đúng với nhiều trường hợp, sai khi hai cột tương quan.

-- Planner assume doc lap:
--   p(status='todo' AND due_at < tomorrow) = 0.70 x 0.10 = 0.07   <- uoc tinh 7%
-- Thuc te (tuong quan cao): ~0.50                                   <- 50%
-- Under-estimate 7x -> chon Index Scan cho 50% bang -> cham

CREATE STATISTICS stats_tasks_status_due (dependencies, ndistinct)
ON status, due_at FROM tasks;
ANALYZE tasks;   -- thu thap extended statistics

SELECT stxname, stxkind FROM pg_statistic_ext WHERE stxrelid = 'tasks'::regclass;

Thử ngẫmhai cột project_id và status trong bảng tasks của bạn có tương quan không? Bạn đo bằng query gì trước khi quyết định tạo CREATE STATISTICS cho chúng?

9. Deep Dive

10. Liên hệ các bài khác

• Cost-based optimization (khoá SQL & Database) — nguyên lý optimizer chọn plan theo cost; bài này là các hệ số và statistics cụ thể của PostgreSQL.
• Scan strategies — selectivity tính ở đây quyết định Seq Scan hay Index Scan ở đó.
• Join algorithms — ước tính rows sai ở outer là lý do planner chọn Nested Loop cho một triệu row.
• 5 anti-pattern — hàm bọc cột và cast ngầm là hai cách làm planner mất statistics ngay cả khi stats mới.
• Có index vẫn Seq Scan — ca thật: cột correlation trong pg_stats thấp khiến planner bỏ index.

11. Tóm tắt

• Checklist khi rows ước tính lệch xa thực tế: last_analyze cũ? → ANALYZE; vẫn sai với giá trị hiếm? → tăng statistics_target cho cột đó; vẫn sai với điều kiện nhiều cột? → CREATE STATISTICS.
• Ca status + due_at ở mục 8: ước tính 7% trong khi thực tế 50% thì CREATE STATISTICS … (dependencies) rồi ANALYZE, đừng thêm index.
• Hàm bọc cột (LOWER(email), DATE_TRUNC(...)) làm planner mù, dùng hằng số ~0,5% bất kể data.
• Đổi statistics_target hay tạo CREATE STATISTICS xong đều phải ANALYZE lại mới có hiệu lực.
• effective_cache_size chỉ là hint cho planner, không cấp RAM; tăng nó làm planner thích Index Scan hơn.

12. Tự kiểm tra

Bài tiếp theo: 5 anti-pattern khiến planner chọn plan tệ