PostgreSQL Bài 26: Query Planner & Statistics: Vai trò của ANALYZE, pg_statistic và Stored Statistics

Ở Bài 25, bạn đã thấy chỉ số rows ước lượng trong EXPLAIN đóng vai trò quyết định thuật toán thực thi: nếu ước lượng ra 2 dòng, Planner chọn Nested Loop Join; nếu ước lượng ra 200.000 dòng, nó lập tức đổi sang Hash Join.

Nhưng làm thế nào Planner biết được một câu lệnh WHERE age > 30 AND city = 'Hanoi' sẽ trả về bao nhiêu dòng mà không cần chạy thử câu query?

Câu trả lời nằm ở Hệ thống dữ liệu thống kê (Database Statistics). Planner không hề bói toán hay đọc toàn bộ bảng; nó dựa vào các mô hình xác suất toán học (MCV Lists, Histograms) được thu thập bởi lệnh ANALYZE và lưu trữ trong bảng hệ thống pg_statistic.

1. Cơ chế thu thập dữ liệu của lệnh ANALYZE

Nhiều người lầm tưởng lệnh ANALYZE sẽ quét qua 100% dữ liệu của bảng để đếm từng dòng. Nếu làm vậy trên bảng 500 triệu dòng, bản thân lệnh ANALYZE sẽ làm tê liệt hệ thống.

1.1. Thuật toán lấy mẫu ngẫu nhiên (Reservoir Sampling)

PostgreSQL sử dụng thuật toán lấy mẫu thống kê Vitter's Reservoir Sampling:

• Engine chỉ đọc một số lượng Data Pages ngẫu nhiên trên đĩa.

• Số lượng dòng được lấy mẫu tính theo công thức phụ thuộc vào thông số cấu hình default_statistics_target (mặc định là 100):

  Soˆˊ doˋng laˆˊy maˆ˜u≈300×default_statistics_target

• Với giá trị mặc định 100, PostgreSQL chỉ bốc ngẫu nhiên khoảng 30.000 dòng từ bảng để phân tích, bất kể bảng đó có 1 triệu hay 100 triệu dòng.

• Quá trình này diễn ra rất nhanh (chỉ vài giây) nhưng vẫn đảm bảo độ tin cậy thống kê toán học theo luật số lớn.

1.2. Phân biệt EXPLAIN ANALYZE vs ANALYZE table_name

• ANALYZE table_name; (DDL Maintenance): Quét lấy mẫu bảng, cập nhật metadata thống kê vào Catalog.

• EXPLAIN ANALYZE ... (Query Profiling): Thực thi câu truy vấn thật và in ra thời gian chạy chi tiết. Hai lệnh này phục vụ hai mục đích hoàn toàn khác nhau.

2. Giải mã kho dữ liệu thống kê: pg_statistic và View pg_stats

Dữ liệu thống kê thô được lưu trong bảng hệ thống pg_catalog.pg_statistic. Vì bảng này chứa các mảng nhị phân khó đọc và yêu cầu quyền Superuser, PostgreSQL cung cấp view thân thiện hơn là pg_stats.

Hãy kiểm tra dữ liệu thống kê của một cột trong bảng users:

SQL

SELECT 
    attname AS column_name,
    null_frac,
    n_distinct,
    most_common_vals,
    most_common_freqs,
    correlation
FROM pg_stats
WHERE tablename = 'users' AND attname = 'country';

KHO DỮ LIỆU THỐNG KÊ (pg_stats) CỦA CỘT 'country':
┌───────────────────────┬────────────────────────────────────────────────────────┐
│ Trường thống kê       │ Giá trị ví dụ                                          │
├───────────────────────┼────────────────────────────────────────────────────────┤
│ null_frac             │ 0.002 (0.2% số dòng mang giá trị NULL)                 │
│ n_distinct            │ 4.0 (Có đúng 4 quốc gia khác biệt)                     │
│ most_common_vals (MCV)│ {Vietnam, USA, Japan, Singapore}                       │
│ most_common_freqs     │ {0.60, 0.20, 0.15, 0.048}                              │
│ correlation           │ 0.92 (Sắp xếp vật lý trên đĩa trùng khớp với thứ tự)   │
└───────────────────────┴────────────────────────────────────────────────────────┘

2.1. Các chỉ số cốt lõi trong pg_stats:

1. null_frac (Tỷ lệ NULL):

   • Tỷ lệ phần trăm các dòng có giá trị NULL.

   • Nếu null_frac = 0.8, Planner biết ngay câu lệnh WHERE col IS NOT NULL chỉ trả về 20% bảng.

2. n_distinct (Số lượng giá trị phân biệt):

   • Nếu số dương (ví dụ 4.0): Có chính xác 4 giá trị khác biệt trong bảng.

   • Nếu số âm (ví dụ -0.8): Số lượng giá trị phân biệt tăng theo quy mô bảng (cụ thể là chiếm 80% tổng số dòng). Giá trị -1.0 đại diện cho cột Unique (mỗi dòng một giá trị).

3. most_common_vals (MCV) & most_common_freqs (MCF):

   • Danh sách các giá trị xuất hiện thường xuyên nhất và tần suất tương ứng của chúng.

   • Ở ví dụ trên: Giá trị 'Vietnam' chiếm tới 60% toàn bộ dữ liệu. Khi bạn query WHERE country = 'Vietnam', Planner biết trước kết quả sẽ trả về 60% bảng → Nó sẽ chọn Seq Scan thay vì Index Scan (vì dùng Index Scan trên 60% dữ liệu sẽ gây Random I/O cực chậm!).

4. histogram_bounds (Biểu đồ phân bố tần suất):

   • Dùng cho các giá trị liên tục không nằm trong nhóm MCV (như ngày tháng, số tiền).

   • PostgreSQL chia dải giá trị thành các thùng (buckets) có diện tích bằng nhau để tính xác suất cho các câu truy vấn khoảng (<, >, BETWEEN).

5. correlation (Độ tương quan vật lý):

   • Đo lường mức độ trùng khớp giữa thứ tự logic của dữ liệu và thứ tự vật lý lưu trên các Data Page của ổ đĩa.

   • Dao động từ -1.0 đến +1.0.

   • correlation càng gần 1.0: Chi phí Index Scan càng rẻ vì engine chỉ cần đọc các page liền kề nhau. Nếu correlation gần bằng 0: Dữ liệu phân tán lộn xộn, Index Scan sẽ tốn nhiều I/O ngẫu nhiên.

3. Điều chỉnh độ chi tiết thống kê (statistics target)

Nếu một cột có độ phân tán dữ liệu rất phức tạp (ví dụ hàng triệu SKU sản phẩm phân bổ không đều), con số mẫu mặc định 100 có thể khiến Planner đoán sai số dòng.

Bạn có thể tăng độ chi tiết lấy mẫu riêng cho một cột cụ thể mà không làm chậm quá trình analyze của toàn bộ database:

SQL

-- Tăng mức độ lấy mẫu cho cột sku lên 500 (Tối đa là 10000):
ALTER TABLE products ALTER COLUMN sku SET STATISTICS 500;

-- Chạy lại analyze để cập nhật:
ANALYZE products;

4. Điểm mù kinh điển: Sự độc lập giữa các cột (Attribute Independence Assumption)

Mặc định, Planner của PostgreSQL tính toán xác suất dựa trên tiền đề toán học: Các cột trong bảng hoàn toàn độc lập với nhau.

Xaˊc suaˆˊt(A VAˋ B)=Xaˊc suaˆˊt(A)×Xaˊc suaˆˊt(B)

4.1. Kịch bản lỗi ước lượng thực tế

Xét bảng danh mục xe ô tô:

• Cột make: Hãng xe (ví dụ: 'Audi').

• Cột model: Dòng xe (ví dụ: 'A4').

Trong thực tế: Chỉ có hãng Audi mới sản xuất xe A4. Hai cột này phụ thuộc 100% vào nhau!

Nhưng Planner không biết điều đó:

• Giả sử 'Audi' chiếm 1% bảng: P(make=’Audi’)=0.01.

• Giả sử model 'A4' chiếm 1% bảng: P(model=’A4’)=0.01.

Khi bạn chạy câu truy vấn:

SQL

SELECT * FROM cars WHERE make = 'Audi' AND model = 'A4';

Planner sẽ nhân mù quáng hai xác suất:

P=0.01×0.01=0.0001 (0.01%)

• Nếu bảng có 1.000.000 dòng, Planner tính toán: 1.000.000×0.0001=100 doˋng.

• Nhưng thực tế toàn bộ xe A4 đều do Audi sản xuất, số dòng thật là 10.000 dòng!

• Hậu quả: Sai lệch gấp 100 lần. Planner nghĩ chỉ có 100 dòng nên chọn kế hoạch chạy dở tệ, dẫn đến nghẽn CPU.

5. Giải pháp hiện đại: Extended Statistics (CREATE STATISTICS)

Từ PostgreSQL 10+, tính năng Extended Statistics cho phép bạn hướng dẫn Planner theo dõi sự tương quan đa chiều giữa các cột.

Cú pháp:

SQL

CREATE STATISTICS stats_name [ ( kind [, ...] ) ] 
ON column_1, column_2 
FROM table_name;

Ba loại thống kê mở rộng:

1. dependencies (Phụ thuộc hàm - Functional Dependencies):

   • Đo lường mức độ một cột quyết định giá trị của cột kia (như ví dụ make → model ở trên, hoặc Tỉnh/Thành → Quốc gia).

2. ndistinct (Số lượng giá trị duy nhất kết hợp):

   • Đo lường số giá trị duy nhất của cụm (col_a, col_b) phục vụ cho mệnh đề GROUP BY a, b.

3. mcv (Most Common Values đa chiều):

   • Lập danh sách các cặp giá trị phổ biến nhất kết hợp giữa nhiều cột.

5.1. Thực hành sửa lỗi ước lượng bằng CREATE STATISTICS

SQL

CREATE SCHEMA IF NOT EXISTS stats_lab;

CREATE TABLE stats_lab.cars (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    make TEXT NOT NULL,
    model TEXT NOT NULL
);

-- Nạp dữ liệu giả lập có sự ràng buộc phụ thuộc hoàn toàn:
INSERT INTO stats_lab.cars (make, model)
SELECT 
    'Audi', 
    'A' || (1 + (i % 5)) -- Audi A1 -> A5
FROM generate_series(1, 100000) AS i;

-- Thêm các hãng xe khác
INSERT INTO stats_lab.cars (make, model)
SELECT 
    'BMW', 
    'Series ' || (1 + (i % 5))
FROM generate_series(1, 100000) AS i;

ANALYZE stats_lab.cars;

Kiểm tra khi CHƯA có Extended Statistics:

SQL

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM stats_lab.cars WHERE make = 'Audi' AND model = 'A1';

Kế hoạch:

Plaintext

Seq Scan on cars  (cost=0.00..3850.00 rows=20000 width=13) (actual rows=20000 loops=1)
  Filter: ((make = 'Audi'::text) AND (model = 'A1'::text))

(Nếu dữ liệu phân tán phức tạp, Planner sẽ ước lượng lệch con số rows này xuống rất thấp).

Tạo Extended Statistics và kiểm tra lại:

SQL

-- Tạo thống kê phụ thuộc hàm và MCV đa cột:
CREATE STATISTICS stats_cars_make_model (dependencies, mcv) 
ON make, model 
FROM stats_lab.cars;

-- Bắt buộc chạy lại ANALYZE để tính toán thống kê mới:
ANALYZE stats_lab.cars;

Kiểm tra thông tin thống kê vừa tạo trong catalog:

SQL

SELECT 
    stxname, 
    stxkeys, 
    stxkind 
FROM pg_statistic_ext
WHERE stxname = 'stats_cars_make_model';

Kể từ thời điểm này, Planner sẽ tra cứu vào bảng tần suất kết hợp giữa make và model. Nó nhận biết được phân phối xác suất chính xác của cặp ('Audi', 'A1'), triệt tiêu hoàn toàn lỗi lệch pha ước lượng dòng.

6. Thống kê trên Biểu thức (Expression Statistics - PG 14+)

Từ PostgreSQL 14+, bạn thậm chí có thể tạo Extended Statistics trên các biểu thức tính toán mà không cần tạo Expression Index:

SQL

-- Thu thập thống kê cho biểu thức ngày tháng:
CREATE STATISTICS stats_orders_expr 
ON (DATE(created_at)), customer_id 
FROM orders;

ANALYZE orders;

7. Tóm tắt & Bài tiếp theo

• Planner dựa vào dữ liệu mẫu trong pg_statistic (xem qua view pg_stats) để ước tính chi phí mà không cần đọc toàn bộ bảng.

• ANALYZE sử dụng thuật toán lấy mẫu ngẫu nhiên (Reservoir Sampling), phụ thuộc vào thông số default_statistics_target.

• pg_stats cung cấp các số liệu: null_frac, n_distinct, most_common_vals (MCV), histogram_bounds và correlation.

• Mặc định Planner giả định các cột độc lập với nhau. Sử dụng CREATE STATISTICS (Extended Statistics) để giải quyết các trường hợp các cột có tương quan logic chặt chẽ (dependencies, mcv).

Bài 27 xem tiếp: Các lỗi phổ biến khiến Index bị vô hiệu hóa và cách khắc phục — chúng ta sẽ điểm mặt chỉ tên những thói quen viết code SQL của lập trình viên khiến PostgreSQL "ngó lơ" Index: ép kiểu ngầm định (Implicit Type Casting), sử dụng hàm trên cột có index, Collation không khớp, và bẫy chi phí của toán tử phủ định (!=).