Thống kê và ANALYZE

Phần 10 đã đo bộ lập lịch ước lượng sai như thế nào. Phần này đo nguyên nhân phổ biến nhất của chuyện đó — thống kê cũ — và tìm ra rằng PostgreSQL có một lớp bảo vệ mà tôi không biết là nó tồn tại.

Dựng ca hỏng kinh điển

Bảng đơn hàng 2 triệu dòng, dữ liệu trải từ 2025-01-01 tới 2025-04-26, đã ANALYZE sạch sẽ. Sau đó chèn 500.000 dòng mới của tháng 6 — nằm hoàn toàn ngoài dải mà histogram biết:

select (histogram_bounds::text::timestamptz[])[1],
       (histogram_bounds::text::timestamptz[])[array_length(histogram_bounds::text::timestamptz[],1)]
from pg_stats where tablename = 'dh4' and attname = 'luc';
--  2025-01-01   |   2025-04-26

Rồi truy vấn đúng vào khoảng dữ liệu mới:

select d.id, k.ten from dh4 d join kh k on k.id = d.kh_id
where d.luc >= '2025-06-01' and d.luc < '2025-07-01';

Dòng đầu là ca hỏng: ước lượng 1 dòng trong khi thực tế là nửa triệu — sai 500.000 lần. Bộ lập lịch thấy vế ngoài chỉ có một dòng nên chọn Nested Loop, và rồi phải chạy vòng lặp đó nửa triệu lần.

Điều bất ngờ: chỉ mục tự nó là lớp bảo vệ

Dòng thứ hai mới là chỗ đáng chú ý. Thống kê hoàn toàn giống dòng đầu — cùng histogram cũ, reltuples vẫn là 2.000.000 trong khi bảng đã có 2.500.000 dòng. Khác biệt duy nhất là có một chỉ mục B-tree trên cột luc.

Ước lượng nhảy từ 1 lên 11.089 và kế hoạch đổi sang Hash Join, nhanh gấp 3 lần.

Lý do là khi giá trị trong điều kiện nằm ngoài histogram, bộ lập lịch không chấp nhận kết luận "gần như không có dòng nào" mà đi đọc đầu mút của chỉ mục ngay lúc lập kế hoạch để biết giá trị lớn nhất thật sự là bao nhiêu. Nhờ vậy nó biết dữ liệu đã vượt ra ngoài phạm vi histogram và hạ mức tự tin xuống.

Điều này giải thích một chuyện tôi từng không hiểu: vì sao ca "thống kê cũ làm sập máy chủ" được kể nhiều đến vậy mà tôi hiếm khi tái hiện được. Trong đa số hệ thống thật, cột hay bị chèn thêm dữ liệu mới — cột ngày tháng, cột id tự tăng — chính là cột có chỉ mục, nên lớp bảo vệ này luôn bật. Muốn thấy ca hỏng phải cố tình bỏ chỉ mục đi.

Ước lượng 11.089 vẫn sai 45 lần, nhưng sai 45 lần thường không đổi kế hoạch, còn sai 500.000 lần thì đổi.

Lớp bảo vệ đó có giá

Đọc đầu mút chỉ mục ở mỗi lần lập kế hoạch không miễn phí, và giá tăng vọt khi đầu mút chỉ mục đầy dòng chết.

Bảng 2 triệu dòng, chỉ mục trên luc. Xoá 358.400 dòng ở đầu dải rồi chưa VACUUM:

Lập kế hoạch chậm 4,7 lần, trong khi thời gian chạy không đổi. Chú ý tỷ lệ: lập kế hoạch tốn gấp 50 lần thời gian chạy.

Với một truy vấn nặng chạy vài lần mỗi phút thì 1,68 ms chẳng là gì. Với một truy vấn tra cứu nhẹ chạy hàng nghìn lần mỗi giây — kiểu "lấy bản ghi mới nhất" — thì toàn bộ chi phí nằm ở khâu lập kế hoạch, và nó là chi phí vô hình: EXPLAIN ANALYZE mặc định vẫn báo "Execution Time" nhỏ xíu, phải nhìn riêng dòng "Planning Time" mới thấy.

Đây cũng là một lý do cụ thể để không để bảng chất đầy dòng chết, ngoài lý do tốn đĩa.

Thống kê một cột không biết các cột liên quan tới nhau

Đây là kiểu ước lượng sai mà ANALYZE không chữa được, dù chạy bao nhiêu lần.

Bảng 2 triệu dòng, cột xa có 2.000 giá trị, cột tinh có 63 giá trị, và mỗi xã thuộc đúng một tỉnh:

Thêm một điều kiện đúng làm ước lượng tệ đi 66 lần. Bộ lập lịch nhân hai xác suất với nhau như thể hai cột độc lập:

1/2000  ×  1/62,5  ×  2.000.000  =  16 dòng

Nhưng điều kiện tinh = 15 không loại thêm dòng nào cả — biết xã là biết tỉnh rồi. Con số thật vẫn là 1.000.

ANALYZE không sửa được vì nó thu thập thống kê cho từng cột riêng lẻ. Phải khai báo rằng hai cột có liên hệ:

create statistics st2 (dependencies, ndistinct) on xa, tinh from tp2;
analyze tp2;

Từ sai 66,7 lần xuống đúng. Đây là công cụ ít được dùng nhất trong bài này mà lại rẻ nhất: một câu lệnh, không tốn đĩa đáng kể, không ảnh hưởng tốc độ ghi.

Dấu hiệu nhận ra bạn đang cần nó: EXPLAIN ANALYZE cho thấy ước lượng đúng khi lọc theo một cột, nhưng lệch hẳn khi thêm cột thứ hai — và hai cột đó về nghiệp vụ rõ ràng liên quan (xã và tỉnh, mã sản phẩm và loại sản phẩm, thành phố và mã bưu chính, hãng xe và mẫu xe).

Khi nào ANALYZE tự chạy

select name, setting from pg_settings
where name like 'autovacuum_analyze%';
--  autovacuum_analyze_threshold     = 50
--  autovacuum_analyze_scale_factor  = 0.1

Công thức là 50 + 0,1 × số dòng. Với bảng 10 triệu dòng, phải có 1.000.050 dòng thay đổi thì autoanalyze mới chạy một lần.

Con số đó khiến hai loại bảng dễ bị bỏ quên:

Bảng rất lớn chỉ ghi thêm. Chèn 500.000 dòng mới vào bảng 10 triệu dòng là chưa tới một nửa ngưỡng, nên thống kê đứng yên. Nếu 500.000 dòng đó lại là dữ liệu của tháng mới — tức nằm ngoài histogram — thì bạn rơi đúng vào ca ở đầu bài.

Bảng vừa nạp xong dữ liệu lớn. Sau một lần COPY hoặc INSERT ... SELECT, thống kê là của trạng thái trước đó, và autovacuum phải đợi tới chu kỳ tiếp theo (mặc định autovacuum_naptime là 60 giây). Truy vấn chạy trong khoảng đó dùng số liệu cũ.

Hạ ngưỡng riêng cho từng bảng, không đổi tham số toàn cục:

alter table don_hang set (autovacuum_analyze_scale_factor = 0.02);

Và sau mỗi lần nạp dữ liệu lớn thì chạy tay, đừng đợi:

analyze don_hang;

ANALYZE không khoá bảng để ghi, chỉ lấy mẫu (mặc định 30.000 dòng, tức default_statistics_target × 300), nên nó rẻ hơn nhiều so với những gì cái tên gợi ra.

Sai lầm khi đo: autovacuum chạy sau lưng

Lần đầu dựng ca hỏng, tôi chèn 500.000 dòng rồi chạy truy vấn ngay và ước lượng đã là 11.065 chứ không phải 1. Tôi tưởng lớp bảo vệ đầu mút chỉ mục mạnh hơn thực tế.

Thật ra autovacuum đã tự chạy ANALYZE trong lúc tôi gõ lệnh tiếp theo. 500.000 dòng trên bảng 2 triệu là 25%, vượt ngưỡng 10%, và autovacuum_naptime là 60 giây — thừa thời gian.

Phải tắt hẳn cho riêng bảng đó mới đo được:

create table dh4(...) with (autovacuum_enabled = off);

Bài học chung: mọi phép đo về thống kê cũ đều phải tắt autovacuum cho bảng đang đo, nếu không bạn đang đo autovacuum chứ không đo cái mình định đo. Và điều này áp cả theo chiều ngược lại — nếu trên máy chủ thật bạn thấy thống kê cũ gây hại, hãy kiểm xem autovacuum có đang bị tắt hoặc bị quá tải không, trước khi đi tìm nguyên nhân phức tạp hơn.

Kiểm tra nhanh trên máy chủ của bạn

Bảng nào lâu chưa được ANALYZE:

select relname,
       n_live_tup as so_dong,
       n_mod_since_analyze as thay_doi_tu_lan_cuoi,
       last_analyze, last_autoanalyze
from pg_stat_user_tables
where n_mod_since_analyze > 10000
order by n_mod_since_analyze desc
limit 10;

n_mod_since_analyze lớn mà last_autoanalyze từ lâu lắm rồi nghĩa là bảng đó đang vượt xa ngưỡng và autovacuum không theo kịp.

Và kiểm reltuples mà bộ lập lịch đang tin có khớp thực tế không:

select relname,
       reltuples::bigint as bo_lap_lich_tin,
       (select count(*) from don_hang) as thuc_te
from pg_class where relname = 'don_hang';

Thử ba mươi giây

Lấy một truy vấn chậm bất kỳ và chạy:

explain (analyze, buffers) <truy vấn của bạn>;

Tìm nút có rows= ước lượng lệch xa nhất so với actual rows=. Nếu tỷ lệ lệch trên 100 lần, thử theo thứ tự:

1. analyze <bảng>; rồi chạy lại — nếu hết thì nguyên nhân là thống kê cũ.
2. Còn lệch, và điều kiện lọc có từ hai cột liên quan tới nhau — thử create statistics.
3. Vẫn lệch, và cột lọc chưa có chỉ mục — dựng chỉ mục cũng là cách cho bộ lập lịch thêm thông tin, không chỉ để tăng tốc.

Phần sau chuyển sang giao dịch: BEGIN, COMMIT và bốn mức cô lập.