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

Planner không đoán mù. Nó tính cost dựa trên statistics — bộ số liệu mô tả phân bố data trong mỗi column. Nhưng statistics chỉ là snapshot tại thời điểm ANALYZE chạy. Nếu data thay đổi nhiều mà statistics không được cập nhật, planner vẫn ra quyết định dựa trên số liệu cũ.

Đây là root cause của hầu hết "tại sao planner chọn plan tệ". Anti-pattern trong bài 5 của module này thường ngầm có gốc rễ từ statistics issue — planner không sai logic, nó chỉ đang dùng thông tin sai.

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

Bếp trưởng nhà hàng cần lập kế hoạch mỗi ngày: chuẩn bị bao nhiêu nguyên liệu, phân công bao nhiêu đầu bếp, giờ nào cần tăng cường. Bộ phận kế hoạch dựa vào báo cáo doanh thu để ra quyết định.

Nếu bếp trưởng lập kế hoạch dựa trên báo cáo 6 tháng trước trong khi hôm nay có sự kiện đặc biệt — khách đông gấp 3, món yêu cầu hoàn toàn khác — kế hoạch sẽ sai hoàn toàn. PostgreSQL planner gặp đúng vấn đề này khi statistics stale.

2. ANALYZE — thu thập statistics dựa trên sample

ANALYZE quét một sample ngẫu nhiên của table, tính statistics, và lưu vào catalog. Không quét toàn bộ table — dùng statistical sampling để nhanh ngay cả với table lớn.

ANALYZE tasks;             -- Update stats cho toàn bộ table
ANALYZE tasks(status);     -- Chỉ 1 column cụ thể
ANALYZE VERBOSE tasks;     -- In progress: số page scanned, số row sampled

Sample size: xấp xỉ 300 × default_statistics_target row. Với default_statistics_target = 100 (mặc định): khoảng 30.000 sample row. Đủ cho table 1 triệu row với distribution bình thường; có thể không đủ cho 100 triệu row với outlier nặng.

Khi nào cần ANALYZE thủ công:

• Sau bulk insert hoặc bulk update lớn — autovacuum có delay, stats chưa cập nhật ngay.
• Sau khi thay đổi statistics_target — cần regenerate để áp dụng target mới.
• Khi EXPLAIN ANALYZE cho thấy estimated rows lệch xa actual rows.

-- Sau bulk insert 1 trieu row: chay ANALYZE ngay, khong cho autovacuum
INSERT INTO tasks SELECT ... FROM generate_series(1, 1000000);
ANALYZE tasks;

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

pg_stats là view trên pg_statistic (internal catalog) — hiển thị statistics per column dưới dạng dễ đọc.

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

Output mẫu cho các column của bảng tasks:

-- tasks.status (enum 4 value, phan phoi skewed):
-- attname          | status
-- n_distinct       | 4
-- null_frac        | 0
-- most_common_vals | {todo,doing,done,archived}
-- most_common_freqs| {0.70,0.20,0.08,0.02}
-- histogram_bounds | NULL
-- (histogram NULL vi MCV cover 100% value, khong con row nao o ngoai)

-- tasks.due_at (timestamp, phan phoi range):
-- attname          | due_at
-- n_distinct       | -1
-- null_frac        | 0.05
-- most_common_vals | NULL
-- histogram_bounds | {2025-01-01,2025-03-15,2025-06-01,...,2026-12-31}
-- (n_distinct=-1: moi value unique; histogram 100 bucket cho range query)

-- tasks.title (text unique):
-- attname          | title
-- n_distinct       | -1
-- most_common_vals | NULL
-- histogram_bounds | NULL
-- (planner fallback heuristic, khong co real distribution)

Ý nghĩa các field chính:

4. Cost model — đơn vị abstract, không phải millisecond

Planner không tính cost bằng millisecond — nó dùng đơn vị abstract chuẩn hóa quanh chi phí đọc 1 sequential page (8 KB).

effective_cache_size không cấp phát RAM — chỉ là hint để planner ước tính bao nhiêu data đang được cache. Giá trị cao hơn làm planner ưu tiên Index Scan hơn.

Điều chỉnh cho SSD/NVMe là bắt buộc trên production modern:

-- Trong postgresql.conf hoac ALTER SYSTEM:
-- random_page_cost = 1.1   -- SSD
-- random_page_cost = 1.0   -- NVMe

-- Test trong session:
SET random_page_cost = 1.1;
SHOW random_page_cost;

Với random_page_cost = 4.0 mặc định trên SSD, planner over-prefer Seq Scan — bài 5 của module này giải thích vấn đề này cụ thể hơn.

5. Selectivity — planner tính xác suất như thế nào

Selectivity là tỷ lệ row mà planner ước tính sẽ pass qua một filter condition. Planner dùng MCV và histogram để tính.

Equality — WHERE col = X:

-- X co trong MCV: dung truc tiep frequency
WHERE status = 'todo'    -- MCV freq 0.70 -> 70% -> Seq Scan
WHERE status = 'archived' -- MCV freq 0.02 -> 2% -> Index Scan

-- X khong co trong MCV:
-- (1 - sum_MCV_freqs) / (n_distinct - count_MCV_values)

Range — WHERE col BETWEEN X AND Y:

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

IN list và pattern:

WHERE status IN ('todo', 'doing')  -- Sum freqs: 0.70 + 0.20 = 0.90 -> Seq Scan

WHERE title LIKE 'Fix%'
-- Heuristic prefix match tren histogram, kem chinh xac hon equality

WHERE LOWER(title) = 'fix login bug'
-- Planner KHONG biet phan phoi cua LOWER() -> fallback heuristic ~0.5%
-- -> Bat Index Scan du selectivity thuc te co the cao hon
-- Bai 5 cua module nay giai thich cach fix

6. Auto-analyze daemon — khi nào statistics tự cập nhật

Auto-analyze chạy như một worker trong autovacuum daemon — cùng infrastructure với autovacuum (bài 5 Module 6 của khoá này).

Trigger condition:

n_mod_since_analyze > autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × n_live_tup

Mặc định: 50 + 0.1 × n_live_tup. Với table 1 triệu row: trigger sau 100.050 modification. Bulk insert 200.000 row xong lúc 3 giờ sáng — autovacuum trigger sau vài phút tùy tải; stats có thể lag 10–30 phút.

-- Kiem tra lan cuoi ANALYZE chay:
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'tasks';

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

7. default_statistics_target — điều chỉnh độ chính xác

default_statistics_target kiểm soát độ chi tiết statistics mà ANALYZE thu thập: sample size (~300 × target row), histogram bucket count, MCV list size.

SHOW default_statistics_target;  -- 100

-- Tang cho column distribution skewed hoac cardinality cao:
ALTER TABLE tasks ALTER COLUMN status SET STATISTICS 1000;
ANALYZE tasks;  -- Bat buoc chay lai sau khi doi target

-- Toan cuc trong postgresql.conf:
-- default_statistics_target = 500

Tradeoff khi tăng target:

Chỉ tăng target cho column quan trọng trong WHERE clause của query critical.

8. Cross-column correlation — CREATE STATISTICS extended

Planner mặc định assume các column độc lập nhau khi tính selectivity cho multi-column predicate. Điều này đúng với nhiều trường hợp — nhưng sai khi 2 column tương quan cao.

-- TaskFlow: status='todo' tuong quan cao voi due_at gan tuong lai
-- Planner assume independent:
--   p(status='todo' AND due_at < tomorrow)
--   = p(status='todo') × p(due_at < tomorrow)
--   = 0.70 × 0.10 = 0.07   <- estimate: 7%
-- Thuc te (correlation cao): p = 0.50   <- thuc te: 50%
-- Planner under-estimate 7x -> chon Index Scan -> thuc te 50% row -> cham

CREATE STATISTICS stats_tasks_status_due (dependencies, ndistinct)
ON status, due_at FROM tasks;

ANALYZE tasks;  -- Collect extended statistics

-- Bay gio planner biet correlation -> estimate chinh xac -> chon plan dung

Ba chế độ của CREATE STATISTICS:

-- E-commerce: 10% top customer chiem 80% order
-- Default target=100: MCV chi catch 100 customer
-- Query cho customer ngoai MCV top 100 -> planner dung remainder formula
-- -> estimate rat thap -> co the chon sai plan
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 5000;
ANALYZE orders;

-- WHERE status='todo' AND due_at < now() + interval '7 days'
-- Independent assumption: 0.70 × 0.15 = 0.105
-- Thuc te (todo task hay due soon): 0.45
-- Fix:
CREATE STATISTICS stats_tasks_status_due (dependencies)
ON status, due_at FROM tasks;
ANALYZE tasks;

9. Applied — TaskFlow statistics setup

-- 1. tasks.status: MCV cover het 4 value, default target=100 du
SELECT attname, n_distinct, most_common_vals, most_common_freqs
FROM pg_stats
WHERE tablename = 'tasks' AND attname = 'status';
-- most_common_vals = {todo,doing,done,archived}
-- most_common_freqs = {0.70,0.20,0.08,0.02}

-- 2. tasks.title: TEXT unique -> MCV NULL, planner fallback heuristic
SELECT n_distinct, most_common_vals
FROM pg_stats
WHERE tablename = 'tasks' AND attname = 'title';
-- n_distinct = -1, most_common_vals = NULL (expected, khong optimize duoc)

-- 3. Cross-column status x due_at: correlation cao -> extended stats
CREATE STATISTICS stats_tasks_status_due (dependencies, ndistinct)
ON status, due_at FROM tasks;

ANALYZE tasks;

-- 4. Verify va test sau khi co extended stats
SELECT stxname, stxkind
FROM pg_statistic_ext
WHERE stxrelid = 'tasks'::regclass;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM tasks
WHERE status = 'todo' AND due_at < now() + interval '7 days';
-- Expected: rows estimate chinh xac hon, plan hop ly hon

10. Deep Dive

11. Tóm tắt

• Statistics là nền tảng của query planning: planner dùng n_distinct, null_frac, MCV, histogram để ước tính selectivity và chọn plan.
• ANALYZE thu thập statistics qua statistical sampling (~300 × target row). Stats là snapshot tại thời điểm chạy — data thay đổi sau không được reflect cho đến ANALYZE tiếp theo.
• pg_stats view cho phép đọc trực tiếp statistics per column: MCV + frequencies cho equality, histogram bounds cho range query.
• Cost model dùng đơn vị abstract: seq_page_cost=1.0 làm baseline, random_page_cost=4.0 mặc định cho HDD. Trên SSD/NVMe: bắt buộc set random_page_cost=1.1.
• Planner tính selectivity: equality dùng MCV frequency; range dùng histogram bucket; function wrapper (LOWER(), DATE_TRUNC()) → fallback heuristic, estimate kém chính xác.
• Auto-analyze trigger khi n_mod_since_analyze > threshold + scale_factor × n_live_tup — sau bulk load nên chạy ANALYZE thủ công.
• default_statistics_target (default 100, max 10000): tăng cho column có distribution skewed hoặc cardinality cao — cần ANALYZE lại sau khi thay đổi.
• Cross-column correlation → planner assume independent → estimate sai. Fix bằng CREATE STATISTICS (dependencies, ndistinct, mcv) ON col1, col2 FROM table (PG 10+).

12. Tự kiểm tra

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