Phần 8 chỉ ra cách đọc từng con số trong kế hoạch. Bài này tập trung vào một con số duy nhất: chênh lệch giữa rows dự đoán và rows thật, vì gần như mọi truy vấn chậm bất thường đều bắt đầu từ đó.
Bảng thử là 2.500.000 đơn hàng.
Vì sao ước lượng quan trọng: nó quyết định kế hoạch
Cùng một câu truy vấn, chỉ đổi giá trị lọc:
| Tỉ lệ dòng khớp | Kế hoạch được chọn | Thời gian | Số trang đụng tới |
|---|---|---|---|
| 0,10% | Index Only Scan | 4,5 ms | 2.005 |
| 9,90% | Bitmap Heap Scan | 20,4 ms | 14.875 |
| 90,00% | Seq Scan | 58,3 ms | 14.706 |
Ba kế hoạch hoàn toàn khác nhau, và bộ tối ưu chọn đúng cả ba lần — vì nó biết mỗi giá trị chiếm bao nhiêu phần trăm bảng.
Để ý dòng giữa: ở mức 9,9%, đường đi qua chỉ mục phải đụng 14.875 trang, còn quét toàn bộ bảng ở mức 90% chỉ đụng 14.706 trang. Dùng chỉ mục đọc nhiều trang hơn quét thẳng — vì mỗi dòng tìm được lại phải nhảy sang một trang dữ liệu khác. Đây là nền cho phần sau về ngưỡng của Seq Scan.
Bây giờ hãy tưởng tượng bộ tối ưu tin rằng 90% kia chỉ là 0,1%. Nó sẽ chọn Index Scan và nhảy ngẫu nhiên 1,8 triệu lần.
Nguồn sai thứ nhất: thống kê cột đã cũ
Tôi chèn 500.000 dòng mang giá trị 'TinhMoi' vào một cột đã có thống kê, rồi không chạy ANALYZE:
| Dự đoán | Thực tế | Lệch | |
|---|---|---|---|
Chưa ANALYZE |
3 dòng | 500.001 | 166.667 lần |
Sau ANALYZE |
630.105 | 500.001 | 26% |
Ba dòng. Bộ tối ưu tin rằng giá trị đó gần như không tồn tại, vì trong ảnh chụp thống kê cũ nó thật sự không tồn tại.
Đây là kịch bản quen thuộc sau một lần nạp dữ liệu lớn, một migration, hoặc một chiến dịch làm lượng đơn tăng vọt trong một ngày. autovacuum sẽ tự chạy ANALYZE, nhưng nó chạy theo ngưỡng — mặc định khi số dòng thay đổi vượt 10% bảng — nên có một khoảng trống giữa lúc dữ liệu đổi và lúc thống kê bắt kịp.
Sau khi nạp dữ liệu lớn, hãy ANALYZE ngay thay vì chờ.
Nguồn sai thứ hai: hàm bọc quanh cột
WHERE extract(year from ngay) = 2026
| Dự đoán | Thực tế | Lệch | |
|---|---|---|---|
| Có hàm bọc | 15.624 | 1.157.598 | 74 lần |
| Viết thành khoảng | 633.228 | 502.740 | 26% |
Thống kê của PostgreSQL gắn với cột, không gắn với biểu thức. Khi bạn bọc cột trong một hàm, mọi thứ nó biết về phân bố giá trị trở nên vô dụng và nó rơi về một hằng số ước lượng.
Cách viết lại:
WHERE ngay >= '2026-01-01' AND ngay < '2027-01-01'
Đo được: 136,0 ms xuống 93,7 ms, nhanh hơn 1,45 lần.
Nhưng phải nói cho đúng: trong phép đo của tôi, cả hai cách đều chọn Hash Join — kế hoạch không đổi. Khoản 42 mili giây tiết kiệm được đến từ việc không phải gọi extract() cho từng dòng trong số 2,5 triệu dòng, chứ không phải từ việc chọn được kế hoạch tốt hơn. Ở quy mô lớn hơn hoặc với join phức tạp hơn, ước lượng lệch 74 lần có thể làm đổi kế hoạch, nhưng tôi không tái hiện được điều đó ở đây.
Nếu buộc phải giữ biểu thức, có thể tạo chỉ mục biểu thức — khi đó PostgreSQL thu thập thống kê cho chính biểu thức đó. Phần 14 sẽ đo.
Nguồn sai thứ ba: LIKE — nhẹ hơn tưởng
| Điều kiện | Dự đoán | Thực tế | Lệch |
|---|---|---|---|
tinh = 'Tinh7' |
36.876 | 31.746 | 16% |
tinh LIKE 'Tinh7%' |
36.876 | 31.746 | 16% |
tinh LIKE '%7' |
237.501 | 190.476 | 25% |
LIKE với ký tự đại diện ở đầu không phá ước lượng — nó chỉ lệch 25%, tốt hơn nhiều so với danh tiếng của nó. PostgreSQL dùng biểu đồ phân bố để đoán, và đoán khá tốt.
Cái '%7' thật sự làm hỏng là khả năng dùng chỉ mục B-tree, không phải ước lượng. Đó là hai vấn đề khác nhau và người ta hay gộp làm một.
Điều bộ tối ưu vẫn làm đúng dù thống kê rỗng
Tôi tạo một bảng mới, ANALYZE lúc nó còn rỗng, rồi chèn 800.000 dòng và không ANALYZE lại:
pg_class: relpages = 0 reltuples = 0
tep tren dia: 28 MB
bo toi uu doan: 800.040 dong (that: 800.000)
Sai số 0,005%. Với relpages = 0, PostgreSQL không tin thống kê — nó đọc kích thước tệp thật rồi chia cho bề rộng dòng trung bình.
Tôi thử ép nó chọn nhầm thuật toán join bằng thống kê cũ và không làm được: cả trước lẫn sau ANALYZE đều là Hash Join (58,0 so với 47,6 ms). Đây là điều đáng chỉnh lại trong hiểu biết phổ biến — "quên ANALYZE là bộ tối ưu mù tịt" không đúng.
Cái ANALYZE thật sự cứu là thống kê từng cột: phân bố giá trị, số giá trị khác nhau, giá trị phổ biến nhất. Đó là lý do ca 'TinhMoi' lệch 166.667 lần trong khi ca đếm cả bảng chỉ lệch 0,005%.
Quy trình khi gặp truy vấn chậm
- So
rowsdự đoán với thực tế ở từng nút. Nút đầu tiên lệch trên mười lần là nguyên nhân; mọi thứ bên trên nó chỉ là hậu quả. - Nếu lệch ở một nút quét bảng → chạy
ANALYZEbảng đó rồi đo lại. - Nếu điều kiện có hàm bọc quanh cột → viết lại thành khoảng, hoặc tạo chỉ mục biểu thức.
- Nếu hai điều kiện trên hai cột liên quan nhau →
CREATE STATISTICSnhư phần 8 đã đo. - Nếu ước lượng đã đúng mà vẫn chậm → vấn đề nằm ở chỉ mục hoặc ở lượng dữ liệu, không nằm ở bộ tối ưu.
Thử ba mươi giây
Tìm bảng có thống kê cũ nhất trong hệ thống của bạn:
SELECT relname,
n_live_tup,
n_mod_since_analyze,
round(100.0 * n_mod_since_analyze / nullif(n_live_tup,0), 1) AS phan_tram_doi,
last_autoanalyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY n_mod_since_analyze DESC
LIMIT 10;
Cột phan_tram_doi trên 10% nghĩa là bảng đó đã đổi nhiều hơn ngưỡng autovacuum và đang chờ tới lượt. Nếu bạn vừa nạp dữ liệu và sắp chạy báo cáo, ANALYZE ten_bang; mất vài giây và tránh được một kế hoạch sai.
Phần sau đo ngưỡng cụ thể: bao nhiêu phần trăm bảng thì Seq Scan thắng Index Scan.
