PostgreSQL Extended Statistics: Sửa ước lượng sai giữa các cột liên quan

Query có đủ index nhưng vẫn chậm đôi khi vì PostgreSQL đoán sai số hàng. Khi hai cột có liên hệ, việc ước lượng từng cột riêng rồi kết hợp như các điều kiện độc lập có thể khiến planner chọn kế hoạch không phù hợp. Extended statistics giúp mô tả mối liên hệ đó.

Bài hướng dẫn dùng PostgreSQL 18 làm mốc tài liệu. Ví dụ dành cho database thử nghiệm; các con số minh họa là kết quả tính từ phân bố dữ liệu, không phải benchmark đã đo trên hệ thống của bạn.

1. Phân biệt thiếu index và thiếu thông tin

Index cung cấp đường truy cập dữ liệu. Statistics giúp planner ước lượng mỗi đường cần xử lý bao nhiêu hàng. Thêm index không tự giải quyết ước lượng sai; ngược lại, statistics tốt cũng không tạo thêm đường truy cập.

Trong EXPLAIN (ANALYZE, BUFFERS), so sánh rows ước lượng với actual rows ở cùng node. Với node chạy nhiều lần, actual rows là trung bình mỗi lần; xem thêm loops để hiểu tổng lượng công việc. Tìm nơi sai lệch bắt đầu từ phía đọc bảng, trước khi kết luận node join cuối cùng là nguyên nhân.

2. Một phòng lab có thể tự chạy

Tạo bảng riêng trong database thử nghiệm. Mỗi mã vùng có đúng một mã nhóm; 100 mã vùng được chia vào 10 nhóm.

CREATE TABLE public.stats_lab (
    zone_id integer NOT NULL,
    region_id integer NOT NULL
);

INSERT INTO public.stats_lab (zone_id, region_id)
SELECT g % 100, (g % 100) / 10
FROM generate_series(1, 100000) AS s(g);

ANALYZE public.stats_lab;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM public.stats_lab
WHERE zone_id = 23 AND region_id = 2;

Trong dữ liệu này, zone_id = 23 chọn 1.000 hàng và tất cả đều có region_id = 2. Nếu coi hai điều kiện độc lập, phép tính là 100.000 × 1% × 10% = 100 hàng. Thực tế là 1.000 hàng. Ước lượng cụ thể có thể dao động do lấy mẫu và phiên bản planner.

Bảng lab không có index nên việc vẫn thấy sequential scan là bình thường. Mục tiêu đầu tiên là sửa cardinality, không phải đổi tên node hay hứa hẹn giảm thời gian chạy.

3. Dependencies: tránh tính hai lần một điều kiện liên quan

CREATE STATISTICS public.stats_lab_dependency (dependencies)
ON zone_id, region_id FROM public.stats_lab;

ANALYZE public.stats_lab;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM public.stats_lab
WHERE zone_id = 23 AND region_id = 2;

CREATE STATISTICS khai báo đối tượng cần thu thập; phải chạy ANALYZE hoặc chờ auto-analyze để có dữ liệu thống kê. Sau đó so sánh lại ước lượng ở node scan. Trong ví dụ này, dependencies giúp planner nhận ra thông tin mã nhóm đã được quyết định bởi mã vùng.

Giới hạn cần nhớ: dependencies hỗ trợ các dạng điều kiện bằng với hằng số và IN với tập hằng số; không phải giải pháp tổng quát cho range, LIKE hoặc so sánh hai cột. Nó cũng không thực thi ràng buộc dữ liệu. Nếu cần bảo vệ quan hệ nghiệp vụ, dùng constraint phù hợp.

4. MCV: học những tổ hợp xuất hiện thường xuyên

Trong hệ thống đơn hàng, một cặp như kênh bán và trạng thái có thể xuất hiện dày đặc mà không có quan hệ quyết định hoàn toàn. Multivariate MCV lưu thông tin về các tổ hợp phổ biến. Để thử riêng cơ chế này, thay đối tượng lab vừa tạo:

DROP STATISTICS public.stats_lab_dependency;

CREATE STATISTICS public.stats_lab_mcv (mcv)
ON zone_id, region_id FROM public.stats_lab;
ANALYZE public.stats_lab;

EXPLAIN SELECT * FROM public.stats_lab
WHERE zone_id = 23 AND region_id = 2;

EXPLAIN SELECT * FROM public.stats_lab
WHERE zone_id = 23 AND region_id = 7;

Cặp thứ hai không tồn tại trong dữ liệu lab. Dependencies đơn thuần không biết danh sách những cặp hợp lệ; MCV cung cấp thông tin cụ thể hơn về tần suất tổ hợp. Tuy vậy MCV có kích thước hữu hạn, nên không được coi việc một cặp vắng khỏi danh sách là bằng chứng nghiệp vụ rằng nó bất khả thi. Kế hoạch cũng không nhất thiết hiển thị ước lượng bằng 0.

5. Ndistinct: ước lượng số nhóm

Query tổng hợp có bài toán khác: cần biết số tổ hợp phân biệt để chọn chiến lược aggregate. Với bảng lab, chỉ có 100 cặp vùng–nhóm thực tế.

EXPLAIN SELECT zone_id, region_id, count(*)
FROM public.stats_lab GROUP BY zone_id, region_id;

CREATE STATISTICS public.stats_lab_groups (ndistinct)
ON zone_id, region_id FROM public.stats_lab;
ANALYZE public.stats_lab;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT zone_id, region_id, count(*)
FROM public.stats_lab GROUP BY zone_id, region_id;

So sánh số hàng ước lượng ở node aggregate với 100 nhóm thực tế. Đây là bài toán số nhóm, khác với selectivity của bộ lọc. Chọn loại statistics theo chỗ estimate sai sẽ giúp kiểm chứng tác dụng rõ hơn.

6. Kiểm tra dữ liệu và điều chỉnh target

SELECT statistics_name, attnames, kinds,
       n_distinct, dependencies
FROM pg_stats_ext
WHERE schemaname = 'public'
  AND tablename = 'stats_lab';

ALTER STATISTICS public.stats_lab_mcv SET STATISTICS 300;
ANALYZE public.stats_lab;

Giá trị 300 là điểm thử nghiệm, không phải cấu hình chuẩn cho mọi database. Tăng target có thể cải thiện độ chi tiết nhưng tốn thêm tài nguyên thu thập và lưu trữ. Đo thời gian ANALYZE và planning time cùng với execution time. Quyền nhìn thấy statistics phụ thuộc quyền sở hữu và chính sách truy cập; kết quả view rỗng cần được kiểm tra cả quyền lẫn việc đã chạy ANALYZE.

7. Những giới hạn dễ bị bỏ qua

Theo tài liệu PostgreSQL 18, extended statistics chưa được dùng để ước lượng selectivity trực tiếp cho phép join giữa các bảng. Ước lượng bộ lọc một bảng tốt hơn vẫn có thể tác động gián tiếp đến quyết định join, nhưng không đồng nghĩa đã mô hình hóa tương quan xuyên bảng.

Kiểm tra đúng query ứng dụng gửi: literal trong SQL console và prepared statement dùng generic plan có thể cho kết quả khác. Nếu tham số quyết định mạnh phân bố dữ liệu, thử nhiều giá trị phổ biến, hiếm và không tồn tại bằng chính đường gọi của ứng dụng.

Statistics là thông tin xấp xỉ. Sau đợt import hoặc thay đổi phân bố lớn, cần thu thập lại. Đừng tạo statistics cho mọi tổ hợp cột: chọn các nhóm xuất hiện trong query quan trọng và có bằng chứng estimate sai.

8. Quy trình áp dụng trên production

1. Lưu query, tham số, version PostgreSQL và kế hoạch hiện tại. Với truy vấn tốn tài nguyên, bắt đầu bằng EXPLAIN không ANALYZE.
2. Thử trên staging có phân bố gần production; xác định sai lệch thuộc dependencies, MCV hay số nhóm.
3. Đưa CREATE STATISTICS vào migration dưới quyền phù hợp; đặt tên phản ánh bảng và nhóm cột.
4. Lên lịch ANALYZE theo ngân sách I/O và CPU. Đây vẫn là công việc đọc/lấy mẫu có chi phí.
5. So sánh estimate, execution time, buffers và hành vi của những query khác dùng cùng bảng.
6. Theo dõi qua nhiều chu kỳ tải và auto-analyze; không kết luận chỉ từ một lần chạy nhanh.
7. Nếu hồi quy, gỡ đúng statistics object đã thêm và kiểm tra lại kế hoạch; không xóa bảng hoặc index để rollback thay đổi này.

Ghi rõ đối tượng, query mục tiêu, người phụ trách và tiêu chí giữ/gỡ trong migration hoặc tài liệu vận hành. Một estimate chính xác hơn nhưng latency không cải thiện là kết quả cần phân tích tiếp, không phải bằng chứng phải ép planner dùng index.

Tài liệu tham khảo

• PostgreSQL 18: Statistics Used by the Planner
• CREATE STATISTICS và giới hạn
• Ví dụ multivariate statistics
• ALTER STATISTICS và pg_stats_ext