GROUP BY, DISTINCT và HashAggregate

GROUP BY là một trong những câu lệnh dễ viết nhất và khó đoán chi phí nhất. Phần này đo lượng bộ nhớ nó thật sự dùng, chuyện gì xảy ra khi không đủ, và tại sao cho thêm bộ nhớ lại gần như không giúp gì.

Bảng đo

5.000.000 dòng, 403 MB, hai cột gộp nhóm với độ phân tán rất khác nhau:

create table sk(id bigserial primary key, nhom int, nhom_to int, gt numeric(12,2), t text);
-- nhom    : 1.000.000 giá trị phân biệt
-- nhom_to :       100 giá trị phân biệt

work_mem để mặc định 4 MB.

Số nhóm quyết định tất cả

Cùng một bảng, cùng số dòng đọc vào, chênh nhau gần 7 lần.

HashAggregate giữ một dòng cho mỗi nhóm trong bảng băm. Với 100 nhóm thì bảng băm chiếm 80 kB và mọi thứ nằm gọn trong bộ nhớ. Với một triệu nhóm, bảng băm không vừa 4 MB, nên PostgreSQL chia dữ liệu thành 65 lô và ghi phần chưa tới lượt ra đĩa tạm.

Điểm quan trọng: chi phí bộ nhớ của GROUP BY không phụ thuộc số dòng đầu vào, nó phụ thuộc số nhóm đầu ra. Gộp 100 triệu dòng thành 50 nhóm rẻ hơn gộp 5 triệu dòng thành một triệu nhóm.

Cho thêm bộ nhớ gần như không giúp gì

Đây là kết quả tôi không chờ đợi.

Gấp 64 lần work_mem, đổi lại nhanh hơn 3%. Và bảng băm chiếm 434 MB RAM thật cho một truy vấn.

Lý do là "ghi ra đĩa" ở đây gần như miễn phí: dữ liệu tạm vừa được ghi xong thì vẫn nằm trong bộ nhớ đệm của hệ điều hành, nên lần đọc lại không chạm đĩa vật lý. Cột Disk Usage trong EXPLAIN đo lượng dữ liệu đi qua tệp tạm, không đo số lần thật sự đọc từ đĩa quay.

Điều này lặp lại kết luận đã gặp ở phần 29 với Hash Join: nâng work_mem để tránh chia lô là lời khuyên phổ biến nhưng nó chỉ đáng khi máy chủ đang thật sự thiếu bộ nhớ đệm. Nâng mù quáng vừa tốn RAM vừa có rủi ro — work_mem là cho mỗi nút của mỗi truy vấn, nên 256 MB nhân với vài chục kết nối là đủ làm hết bộ nhớ máy chủ.

Sắp xếp thắng băm

PostgreSQL có hai chiến lược gộp nhóm: HashAggregate dựng bảng băm, GroupAggregate sắp xếp trước rồi gộp các dòng liền kề. Bộ tối ưu chọn HashAggregate ở đây. Ép chạy cả hai, lặp ba lần:

GroupAggregate nhanh hơn ở cả hai mức — 10% ở 4 MB và 20% ở 256 MB. Ba lần đo có độ lệch rất nhỏ nên đây không phải nhiễu.

Và HashAggregate chậm đi khi có thêm bộ nhớ: 1.550 ms ở 4 MB thành 1.686 ms ở 256 MB. Cấp phát và ghi vào một bảng băm 434 MB tốn hơn phần tiết kiệm được từ việc bỏ 65 lô.

Đây là lần thứ hai trong sê-ri này mô hình chi phí chọn không phải phương án nhanh nhất — lần đầu là Merge Join ở phần 29. Cả hai lần đều theo cùng một hướng: mô hình đánh giá thao tác tuần tự (đọc chỉ mục theo thứ tự, sắp xếp) đắt hơn thực tế trên phần cứng hiện đại.

Cách xử lý thực dụng: nếu bạn có một truy vấn báo cáo chạy hàng ngày và EXPLAIN ANALYZE cho thấy HashAggregate với nhiều lô, hãy thử set enable_hashagg = off và đo. Nếu nhanh hơn thì bạn vừa tìm được 20% miễn phí.

DISTINCT và GROUP BY là cùng một thứ

Bộ nhớ và số lô trùng khít tới từng kilobyte. Hai cách viết, một kế hoạch. Chọn cái nào là chuyện đọc cho dễ hiểu, không phải chuyện hiệu năng.

Nhưng count(distinct) thì khác hẳn:

Có một lời khuyên rất phổ biến: "count(distinct) chậm, hãy viết lại thành count(*) trên một truy vấn con GROUP BY". Trên phép đo này, làm theo lời khuyên đó chậm thêm 46%.

Lý do: count(distinct) không dùng được HashAggregate, nó sắp xếp bằng trộn ngoài. Và như phần trên đã đo, sắp xếp ở đây nhanh hơn băm. Lời khuyên kia sinh ra từ thời HashAggregate chưa biết chia lô — trước PostgreSQL 13, một GROUP BY vượt work_mem sẽ vẫn cố nhét vào bộ nhớ và làm máy chủ hết RAM. Bây giờ nó chia lô đàng hoàng, và tương quan đã đảo ngược.

Đây là dạng lời khuyên đáng ngờ nhất: đúng ở thời điểm nó được viết ra, sai bây giờ, và không có gì trong nó nói cho bạn biết nó phụ thuộc phiên bản.

Đòn bẩy mạnh nhất: chỉ mục

Nhanh hơn hai lần, và quan trọng hơn: bộ nhớ về 0, ghi đĩa về 0.

Chỉ mục B-tree đã cho sẵn thứ tự, nên PostgreSQL chuyển sang GroupAggregate mà không cần bước sắp xếp nào. Nó chỉ việc đi dọc chỉ mục và gộp các khoá giống nhau liền kề.

So với việc nâng work_mem lên 256 MB để được 3%, một chỉ mục cho 2× tốc độ và bỏ hẳn nhu cầu bộ nhớ. Với truy vấn gộp nhóm chạy thường xuyên trên cùng một cột, chỉ mục gần như luôn là lựa chọn đúng.

Đổi lại là chi phí ghi — phần 19 đã đo: sáu chỉ mục thừa làm việc chèn chậm 4,6 lần. Nên chỉ dựng khi truy vấn gộp nhóm đó thật sự chạy thường xuyên.

DISTINCT ON đắt hơn vẻ ngoài

select distinct on (nhom_to) nhom_to, gt from sk order by nhom_to, gt desc;
--  3.434,96 ms,  sắp xếp trộn ngoài 102.696 kB

Chậm hơn group by nhom_to (234 ms) gần 15 lần, dù chỉ trả về 100 dòng.

DISTINCT ON buộc phải sắp xếp toàn bộ 5 triệu dòng theo (nhom_to, gt desc) để biết dòng nào đứng đầu mỗi nhóm. Nó không dùng được HashAggregate vì nó cần thứ tự.

Phần 31 đã đo cùng bài toán này bằng LATERAL và ra 78 ms. Quy tắc vẫn thế: khi số nhóm nhỏ và có chỉ mục phù hợp, LATERAL là lựa chọn nhanh nhất cho "lấy dòng đầu mỗi nhóm".

Bảng chọn nhanh

Thử ba mươi giây

explain (analyze, buffers) select <cột>, count(*) from <bảng> group by <cột>;

Ba thứ cần nhìn trong nút HashAggregate:

1. Batches — lớn hơn 1 nghĩa là đang chia lô. Không phải vấn đề, nhưng đáng biết.
2. Disk Usage — so với Peak Memory Usage. Nếu ghi đĩa gấp nhiều lần bộ nhớ, số nhóm đang rất lớn.
3. Planned Partitions so với Batches thật — chênh nhiều nghĩa là ước lượng số nhóm sai, và CREATE STATISTICS (phần 20) có thể giúp.

Và luôn thử chỉ mục trước khi nghĩ tới work_mem: 2× tốc độ với 0 bộ nhớ so với 3% tốc độ với 434 MB.

Phần sau đo EXISTS, IN, NOT IN và ANTI JOIN — bốn cách viết cùng một câu hỏi, và một cái bẫy NULL làm câu trả lời sai chứ không chỉ chậm.