Bài sắp xếp cho thấy work_mem quyết định một thao tác chạy trong RAM hay tràn đĩa. GROUP BY (và các hàm tổng hợp COUNT, SUM, AVG, MAX) cũng chịu cùng ranh giới đó, nhưng với một cách tính chi phí bất ngờ mà tôi hiểu sai lúc đầu. Bài này đo hai cách PostgreSQL thực hiện gom nhóm, và vấp đúng câu hỏi "cái gì mới thật sự tốn bộ nhớ".
Hai cách gom nhóm
GROUP BY cot gom các hàng có cùng giá trị cot lại thành nhóm, rồi tính một giá trị tổng hợp cho mỗi nhóm. PostgreSQL có hai cách làm việc này:
- HashAggregate: dựng một bảng băm với khóa là giá trị nhóm. Khi quét qua từng hàng, nó băm khóa nhóm, tìm ô tương ứng trong bảng băm, và cộng dồn (ví dụ tăng
COUNTlên 1). Không cần sắp xếp gì — chỉ một lượt quét. Thường là cách nhanh nhất. - GroupAggregate: cần đầu vào đã sắp theo khóa nhóm (qua một node
Sortphía trước, hoặc đọc theo index). Khi dữ liệu đã sắp, mọi hàng cùng nhóm nằm liền kề nhau, nên nó chỉ cần đi tuần tự và "chốt" một nhóm khi khóa đổi. Tốn ít RAM (chỉ giữ một nhóm đang xử lý), nhưng đổi lại phải trả giá cho bước sắp xếp.
Điểm mấu chốt của HashAggregate là bộ nhớ của nó tỉ lệ với số nhóm (mỗi nhóm một ô trong bảng băm), không phải số hàng. Đây là chỗ tôi đo hớ.
Đo: 24 kB hay 62 MB, tùy số nhóm
Tôi tạo bảng hai triệu hàng, với cột nhom chỉ có 100 giá trị (100 nhóm) và cột id có hai triệu giá trị phân biệt (hai triệu nhóm). Rồi đếm theo nhóm, đọc EXPLAIN ANALYZE:
| Truy vấn | Kế hoạch | Bộ nhớ / Thời gian |
|---|---|---|
GROUP BY nhom (100 nhóm) |
HashAggregate | 24 kB / 202 ms |
| Ép GroupAggregate (cùng truy vấn) | Sort đĩa + Group | 23 MB đĩa / 289 ms |
GROUP BY id (2 triệu nhóm) |
HashAggregate tràn | 62 MB đĩa / 539 ms |
Dòng đầu: GROUP BY nhom trên hai triệu hàng dùng HashAggregate với Memory Usage: 24kB — chỉ hai mươi tư kilobyte! Vì bảng băm chỉ có 100 ô (100 nhóm), bất kể quét bao nhiêu hàng. Ép PostgreSQL dùng GroupAggregate cho cùng truy vấn (dòng hai) thì nó phải thêm một node Sort sắp cả hai triệu hàng — và Sort đó tràn 23 MB ra đĩa, mất 289 ms, chậm hơn hash. Với ít nhóm, HashAggregate thắng rõ: nó chỉ quét một lượt và giữ 100 ô, trong khi GroupAggregate phải sắp hai triệu hàng chỉ để rồi gom lại thành 100 nhóm — bỏ công sắp cho một kết quả bé tí. Đây đúng là lý do PostgreSQL mặc định chọn hash cho trường hợp ít nhóm.
Dòng ba là chỗ lật.
Một lần tôi đo hớ: bộ nhớ theo số nhóm, không phải số hàng
Thấy GROUP BY nhom chỉ tốn 24 kB trên hai triệu hàng, tôi định chốt một quy tắc gọn: "HashAggregate nhẹ RAM, GROUP BY chạy tốt bất kể bảng lớn cỡ nào". Con số 24 kB cho hai triệu hàng nghe như bằng chứng chắc chắn.
Nhưng rồi tôi chạy GROUP BY id — cùng bảng hai triệu hàng, chỉ khác cột gom nhóm. Lần này HashAggregate hiện Planned Partitions: 32 Batches: 33 Memory Usage: 8209kB Disk Usage: 62920kB — nó tràn 62 MB ra đĩa và mất 539 ms. Cùng số hàng, mà một truy vấn tốn 24 kB, truy vấn kia tốn 62 MB đĩa. Theo kỷ luật, hai kết quả lệch xa trên "cùng bảng" nghĩa là tôi bỏ sót một biến — và biến đó là số nhóm.
Sự thật: bộ nhớ của HashAggregate tỉ lệ với số nhóm phân biệt (distinct), không phải số hàng. GROUP BY nhom có 100 nhóm nên bảng băm 100 ô = 24 kB, tí xíu. GROUP BY id có hai triệu nhóm nên bảng băm cần hai triệu ô — vượt xa work_mem, nên PostgreSQL phải chia bảng băm thành 33 phần và tràn ra đĩa (62 MB), giống hệt cách hash join tràn batch ở bài phần 18. Cái tôi đo hớ là lẫn "số hàng" với "chi phí gom nhóm" — tôi tưởng bảng lớn (nhiều hàng) là thứ làm HashAggregate tốn RAM, trong khi thủ phạm thật là số nhóm phân biệt. Một bảng tỉ hàng gom thành 10 nhóm vẫn nhẹ tênh; một bảng triệu hàng gom thành triệu nhóm mới là gánh nặng.
Bài học đo lường: khi ước lượng chi phí một thao tác, phải hỏi đúng đại lượng nó phụ thuộc. Với HashAggregate, đó là số nhóm, không phải số hàng; đọc Memory Usage và Disk Usage/Batches trong EXPLAIN cho biết bảng băm to cỡ nào và có tràn không. Và khi số nhóm quá lớn để bảng băm vừa RAM, GroupAggregate (qua Sort hoặc index) là một lối đi khác — nó chỉ giữ một nhóm tại một thời điểm, nên không phình theo số nhóm.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: GROUP BY trên cột ít giá trị phân biệt rất rẻ; trên cột nhiều giá trị phân biệt mới đáng lo. Gom theo trạng thái (vài giá trị), theo ngày (vài trăm), theo hạng mục (vài nghìn) — HashAggregate xử lý gọn trong RAM. Nhưng gom theo user_id, session_id, hay bất cứ khóa gần-như-duy-nhất nào trên bảng lớn có thể làm bảng băm tràn đĩa và chậm hẳn. Nếu một truy vấn báo cáo gom nhóm chạy chậm, kiểm EXPLAIN (ANALYZE) xem HashAggregate có Batches > 1/Disk Usage không, và cân nhắc tăng work_mem hoặc giảm số nhóm (lọc bớt trước khi gom).
Hệ quả thứ hai: COUNT(DISTINCT ...) là một cái bẫy hiệu năng liên quan. Đếm số giá trị phân biệt cũng phải theo dõi từng giá trị đã thấy, nên nó tốn bộ nhớ theo số giá trị distinct — với cột nhiều giá trị, COUNT(DISTINCT) có thể rất đắt. Khi cần đếm distinct trên tập lớn mà chấp nhận sai số nhỏ, các hàm ước lượng (như HyperLogLog qua extension) rẻ hơn nhiều. Còn COUNT(*) thường (không distinct) thì chỉ tăng một bộ đếm, không cần nhớ gì — nên nhanh và tốn bộ nhớ hằng số bất kể bao nhiêu hàng.
Hệ quả thứ ba là bài học đo lường. Con số mang theo: HashAggregate gom nhóm bằng bảng băm, bộ nhớ tỉ lệ với SỐ NHÓM phân biệt chứ không phải số hàng — 100 nhóm trên 2 triệu hàng chỉ 24kB, nhưng 2 triệu nhóm distinct làm nó tràn 62MB ra đĩa; GroupAggregate (cần Sort/index) là lối thay thế khi nhóm quá nhiều. Đừng đoán chi phí gom nhóm từ kích thước bảng; số nhóm phân biệt mới là đại lượng quyết định, và EXPLAIN cho biết nó có tràn đĩa hay không.
Thử ba mươi giây
Chạy EXPLAIN (ANALYZE) SELECT cot, count(*) FROM bang GROUP BY cot với hai cột khác nhau: một cột ít giá trị phân biệt (trạng thái, hạng mục) và một cột nhiều giá trị (id, email). Nhìn node HashAggregate: với cột ít nhóm, Memory Usage sẽ nhỏ và Batches: 1; với cột nhiều nhóm, bạn có thể thấy Batches > 1 và Disk Usage: ... — bảng băm tràn đĩa vì quá nhiều nhóm. Đó chính là "bộ nhớ theo số nhóm" mà bài này đo. Nếu thấy tràn, thử SET work_mem = '256MB'; rồi chạy lại: nếu Batches về 1, bạn vừa xác nhận số nhóm (không phải số hàng) là thứ cần bộ nhớ.
