Statistics, ANALYZE và cách PostgreSQL ước lượng query Trung cấp

Vấn đề

Bảng tenant_demo có 100.000 dòng. Cột tenant_id (1-100) và region (west/east) không độc lập — dữ liệu được tạo sao cho tenant 1-50 luôn là west, tenant 51-100 luôn là east. Query:

SELECT * FROM tenant_demo WHERE tenant_id = 42 AND region = 'west';

Thực tế câu này khớp đúng 1.000 dòng (toàn bộ dữ liệu của tenant 42). Nhưng planner ước lượng:

Seq Scan on tenant_demo  (cost=0.00..2236.00 rows=489 width=26) (actual time=0.008..2.167 rows=1000 loops=1)
  Filter: ((tenant_id = 42) AND (region = 'west'::text))
Execution Time: 2.206 ms

rows=489 nhưng actual rows=1000 — thấp hơn phân nửa.

Thực tế                 1000  ████████████████████████████████████████
Mặc định (độc lập)       489  ███████████████████▌
Sau CREATE STATISTICS   1060  ██████████████████████████████████████████▍

Với query đơn giản này không sao (vẫn Seq Scan, không đổi plan). Nhưng nếu tenant_demo là một bên của một join lớn, sai lệch 2 lần ở đây có thể khiến planner chọn sai thuật toán join phía trên (xem Nested loop, hash join và merge join).

Khái niệm cốt lõi

Planner không đếm bảng cho mỗi query. Nó tra thống kê thu thập sẵn từ lần ANALYZE gần nhất rồi đoán số dòng khớp.

Thống kê (mỗi cột)	Dùng để
most_common_vals + tần suất (MCV)	Ước lượng col = x cho giá trị phổ biến
histogram_bounds	Ước lượng khoảng <, >, BETWEEN
n_distinct	Ước lượng col = x cho giá trị hiếm, GROUP BY
correlation	Chọn Index Scan hay Bitmap (thứ tự vật lý khớp thứ tự logic?)

Mặc định thống kê lưu theo từng cột riêng lẻ. Với A AND B, planner giả định độc lập rồi nhân hai selectivity. Giả định đúng phần lớn thời gian, sai khi hai cột tương quan như tenant_id và region.

Con số mặc định cần nhớ

Tham số	Mặc định	Ý nghĩa
default_statistics_target	100	Tối đa 100 MCV và 100 bucket histogram mỗi cột
Số dòng lấy mẫu	khoảng 300 × target (tức 30.000 dòng)	Cột có target lớn nhất quyết định cỡ mẫu
autovacuum_analyze_threshold	50	Số dòng thay đổi tối thiểu
autovacuum_analyze_scale_factor	0,1	Cộng thêm 10% kích thước bảng
autovacuum_naptime	1 phút	Chu kỳ autovacuum kiểm tra mỗi database

Bảng 100.000 dòng: auto-analyze chạy khi có khoảng 50 + 0,1 × 100.000 = 10.050 dòng thay đổi kể từ lần analyze trước. Bảng 100 triệu dòng cần 10 triệu dòng đổi, lúc đó thống kê có thể cũ lâu. Bảng lớn nên hạ autovacuum_analyze_scale_factor theo từng bảng.

Bảng TEMP không bao giờ được auto-analyze: autovacuum không truy cập được bảng tạm. Mọi demo trong loạt bài này đều gọi ANALYZE thủ công vì lý do đó. Sau bulk load, hãy ANALYZE ngay. PostgreSQL 18 giữ lại thống kê của bảng khi pg_upgrade, nhưng extended statistics thì không; PG17 trở xuống cần ANALYZE sau upgrade.

Cách hoạt động

Vì sao 489, không phải 1000?

Điều kiện	Selectivity ước lượng
tenant_id = 42	Khớp 1/100 giá trị → ~1%
region = 'west'	Khớp khoảng 50% dữ liệu
Cả hai (nhân lại)	1% × 50% × 100.000 ≈ 500 (planner tính ra 489, gần đúng công thức này)

Đây chính là phép tính “giả định độc lập” — coi như một tenant bất kỳ có 50% cơ hội rơi vào mỗi region.

Thực tế, tenant 42 luôn luôn là west — không có sự “may rủi” 50-50 nào cả. Toàn bộ 1.000 dòng của tenant 42 đều khớp cả hai điều kiện, không phải một nửa. Chênh lệch 489 so với 1.000 chính là cái giá của giả định độc lập sai.

Nhân selectivity từng cột cho ra 489; extended statistics đưa ước lượng lên 1060

Phóng to sơ đồ

tenant_demo → tenant_id = 421%

◀ Trước▶ PhátTiếp ▶↺ Từ đầuBước 1/4

Thống kê mặc định lưu theo từng cột. tenant_id = 42 khớp 1/100 giá trị, nên planner ước lượng khoảng 1% của 100.000 dòng.

Sửa bằng CREATE STATISTICS

CREATE STATISTICS tenant_demo_tenant_region_stats (dependencies, mcv)
ON tenant_id, region FROM tenant_demo;
ANALYZE tenant_demo;

CREATE STATISTICS không tự thu thập dữ liệu — nó chỉ khai báo “hãy để ý tương quan giữa hai cột này”. Bước ANALYZE sau đó mới thực sự tính. Chạy lại đúng câu query cũ:

Seq Scan on tenant_demo  (cost=0.00..2236.00 rows=1060 width=26) (actual time=0.012..2.030 rows=1000 loops=1)
  Filter: ((tenant_id = 42) AND (region = 'west'::text))
Execution Time: 2.056 ms

489 → 1060, so với thực tế 1000. Từ ước lượng chỉ bằng 49% thực tế, giờ thành 106% thực tế — sai lệch theo chiều ngược lại nhưng nhỏ hơn nhiều.

(dependencies, mcv) yêu cầu PostgreSQL ghi nhận cả mức độ phụ thuộc giữa hai cột (dependencies) lẫn tổ hợp giá trị phổ biến (mcv), đủ để nhận ra “biết tenant_id, gần như đoán được region”.

Xác nhận thống kê đã tồn tại và loại nào được lưu:

         statistics_name         | kinds
---------------------------------+-------
 tenant_demo_tenant_region_stats | {f,m}

f là functional dependencies, m là MCV — khớp đúng hai loại đã khai báo.

Xem thống kê planner đang dùng

-- Thống kê từng cột
SELECT attname, n_distinct, most_common_vals, most_common_freqs, correlation
FROM pg_stats
WHERE tablename = 'tenant_demo';

-- Extended statistics đã khai báo (pg_stats_ext hiển thị nội dung đã thu thập)
SELECT stxname, stxkeys, stxkind FROM pg_statistic_ext;

-- Lần analyze gần nhất
SELECT relname, last_analyze, last_autoanalyze, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'tenant_demo';

n_mod_since_analyze so với ngưỡng 50 + 0,1 × reltuples cho biết auto-analyze sắp chạy hay đã trễ.

Các loại extended statistics

Loại	Trị vấn đề	Ví dụ
dependencies	Cột A xác định cột B	zip → city; chỉ áp dụng cho điều kiện = với hằng số và IN
ndistinct	GROUP BY a, b đoán sai số nhóm	GROUP BY city, state
mcv	Tổ hợp giá trị phổ biến, hỗ trợ cả điều kiện khoảng	Cặp (country, currency)
expressions (PG14 trở lên)	Điều kiện trên biểu thức	WHERE date_trunc('day', ts) = ...

Extended statistics chỉ cải thiện ước lượng trên một bảng. Sai lệch ở điều kiện join giữa hai bảng không được sửa bằng CREATE STATISTICS.

Quy trình chẩn đoán khi nghi ngờ estimate sai

1. Chạy EXPLAIN (ANALYZE, BUFFERS), so rows= (ước lượng) với actual rows= ở node thấp nhất có sai lệch lớn — sai lệch ở node dưới sẽ lan lên các node cha phía trên.
2. Nếu chỉ một điều kiện đơn cột sai, thử ANALYZE lại trước (có thể do bulk load chưa kịp auto-analyze) hoặc tăng ALTER TABLE ... ALTER COLUMN ... SET STATISTICS cho riêng cột đó.
3. Nếu sai lệch xuất hiện đúng lúc kết hợp nhiều điều kiện như trường hợp trên, đó là dấu hiệu tương quan giữa các cột — dùng CREATE STATISTICS.
4. Đo lại bằng EXPLAIN ANALYZE sau mỗi thay đổi, đổi một yếu tố một lần — đừng vừa tăng target vừa thêm extended statistics cùng lúc rồi không biết cái nào có tác dụng.

Checklist production

1. Theo dõi n_mod_since_analyze và last_autoanalyze của các bảng lớn; bảng chậm auto-analyze thì hạ autovacuum_analyze_scale_factor riêng cho bảng đó.
2. Sau bulk load, import, hoặc pg_upgrade (PG17 trở xuống): chạy ANALYZE thủ công ngay.
3. Điều tra estimate sai từ node thấp nhất có rows lệch actual rows.
4. Một cột đơn sai: ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500 rồi ANALYZE, chỉ cho cột đó.
5. Nhiều điều kiện cùng lệch: CREATE STATISTICS cho đúng cặp cột tương quan, rồi ANALYZE.
6. Đổi một yếu tố mỗi lần và đo lại bằng EXPLAIN (ANALYZE, BUFFERS).
7. Bảng TEMP: luôn ANALYZE thủ công.

Ví dụ thực tế

Ví dụ thực tế

Một bảng events có cột country và currency — gần như mọi dòng country = 'VN' đều có currency = 'VND'.

Một dashboard lọc theo cả hai cột cho một báo cáo khu vực có thể khiến planner đánh giá sai số dòng cần xử lý, dẫn tới chọn sai thuật toán join hoặc sai kích thước hash table phía trên.

Trước khi nghi ngờ index hay phần cứng, kiểm tra rows so với actual rows ở node lọc hai cột đó. Nếu lệch nhiều lần, CREATE STATISTICS (dependencies, mcv) ON country, currency FROM events thường là cách sửa đúng chỗ, rẻ hơn nhiều so với việc thêm index hay viết lại query.

Trade-offs

Ưu điểm

• Cho phép planner ước lượng mà không cần đếm chính xác toàn bảng mỗi lần lập plan
• CREATE STATISTICS sửa đúng nguyên nhân gốc (tương quan bị bỏ sót) thay vì ép plan bằng tay
• Có thể áp dụng chọn lọc cho đúng cặp cột nghi ngờ, không cần bật cho toàn bảng hay toàn database

Nhược điểm

• Thống kê luôn là ước lượng từ mẫu (sample), không phải đếm chính xác — kể cả sau khi cải thiện, 1060 vẫn không phải 1000 tuyệt đối
• CREATE STATISTICS không tự động thu thập; quên ANALYZE sau khi tạo thì object tồn tại nhưng vô dụng
• Tăng default_statistics_target cho toàn cột/toàn bảng làm ANALYZE chậm hơn và tốn thêm dung lượng catalog, trong khi có thể chỉ cần extended statistics đúng một cặp cột

Sai lầm thường gặp

Sai lầm thường gặp

Tăng default_statistics_target toàn database ngay khi thấy một query estimate sai. Trong ví dụ trên, vấn đề không phải “mẫu thống kê quá thô” — nó là thiếu thông tin về tương quan giữa hai cột cụ thể, điều mà tăng target cho từng cột riêng lẻ không giải quyết được (mỗi cột vẫn được thống kê độc lập).

Xác định đúng loại nguyên nhân trước — hai bệnh khác nhau, hai cách chữa khác nhau:

Nguyên nhân	Cách chữa
Thiếu độ chi tiết	Tăng target cho một cột
Thiếu tương quan	Extended statistics

Tóm tắt

Tóm tắt

• Planner ước lượng số dòng bằng thống kê đã lưu (MCV, histogram, ndistinct) từ lần ANALYZE gần nhất, không đếm chính xác mỗi query
• Mặc định, nhiều điều kiện được coi là độc lập và nhân xác suất với nhau — sai khi các cột thực sự tương quan
• Trong demo: giả định độc lập cho ước lượng 489, thực tế 1000 (chỉ bằng 49%); sau CREATE STATISTICS (dependencies, mcv) ước lượng thành 1060 (106% — gần đúng hơn nhiều)
• Chẩn đoán bằng cách so rows với actual rows ở node thấp nhất lệch nhiều, không phải đoán chung chung “thống kê cũ”
• Mặc định: target 100, mẫu khoảng 30.000 dòng, auto-analyze khi thay đổi vượt 50 + 10% kích thước bảng; bảng TEMP không bao giờ được auto-analyze
• ANALYZE table cập nhật thống kê cho planner; EXPLAIN ANALYZE query chạy query thật và báo số đo runtime — hai lệnh trùng tên nhưng mục đích khác hẳn nhau

Tham khảo

• PostgreSQL 17 — Statistics Used by the Planner
• PostgreSQL 17 — ANALYZE
• PostgreSQL 17 — CREATE STATISTICS