Window function

Window function giải được lớp bài toán mà GROUP BY không giải nổi: xếp hạng trong từng nhóm, so với dòng liền trước, tính cộng dồn. Phần này đo chi phí thật của chúng, và so với hai lựa chọn thay thế — làm bằng SQL kiểu khác, hoặc kéo dữ liệu về ứng dụng mà tự tính.

Bảng đo

2.000.000 dòng bán hàng của 2.000 nhân viên, 129 MB, có chỉ mục (nv, doanh_thu desc).

Bài toán: lấy 3 đơn doanh thu cao nhất của mỗi nhân viên — 6.000 dòng kết quả.

Bốn cách, bốn con số

Kết quả bất ngờ nhất là LATERAL nhanh hơn window function gần hai lần:

select g.nv, x.doanh_thu
from (select nv from bh group by nv) g
cross join lateral (
  select doanh_thu from bh b
  where b.nv = g.nv
  order by doanh_thu desc limit 3
) x;

Lý do nằm ở số dòng phải đọc. LATERAL chạy 2.000 lần tra chỉ mục, mỗi lần lấy đúng 3 dòng đầu — tổng cộng 6.000 dòng. Window function thì phải đi qua cả 2.000.000 dòng để gán số thứ tự cho từng dòng, rồi mới lọc r <= 3.

Đây là quy luật chung: khi số nhóm nhỏ so với số dòng và có chỉ mục phù hợp, LATERAL thắng. Khi số nhóm lớn (mỗi nhóm chỉ vài dòng), 2.000 lần tra chỉ mục trở thành 500.000 lần và window function thắng lại.

DISTINCT ON là cách viết gọn nhất nhưng chỉ lấy được một dòng đầu mỗi nhóm. Nó không mở rộng ra top-3 được.

Tự tính ở ứng dụng đắt hơn nhiều lần

Cách thứ tư là cách mà mã ứng dụng hay làm: SELECT * FROM bh, rồi gom nhóm trong Java hoặc Python.

truyền dữ liệu : 25 MB trong 0,41 s
tính ở ứng dụng: 0,66 s
tổng            : 1,07 s

Chậm hơn window function 7,4 lần và chậm hơn LATERAL 13,6 lần. Và đó là khi ứng dụng chạy trên cùng một máy với cơ sở dữ liệu. Qua mạng thật, 25 MB là con số quyết định.

Ba chi phí mà cách này gánh và ba cách kia không có:

Truyền 25 MB thay vì 6.000 dòng. Kết quả cuối chỉ có 0,3% số dòng ban đầu, nhưng bạn kéo về 100%.

Bộ nhớ ở phía ứng dụng. Vòng lặp Python của tôi giữ tối đa 3 giá trị mỗi nhân viên nên chỉ tốn vài trăm kB — nhưng cách viết tự nhiên hơn là list(cursor) rồi mới nhóm, và cách đó cần 25 MB trong heap. Trên máy chủ ứng dụng chạy nhiều luồng, đó là chỗ hết bộ nhớ.

Không dùng được chỉ mục. Cơ sở dữ liệu biết (nv, doanh_thu desc) tồn tại; ứng dụng thì không.

Điều này không có nghĩa "mọi thứ nên làm trong SQL". Nhưng với phép tính mà SQL có sẵn cú pháp — xếp hạng, gộp nhóm, chọn top-N — thì làm trong SQL gần như luôn rẻ hơn.

Chỉ mục khớp với OVER là yếu tố lớn nhất

Window function cần dữ liệu đã sắp theo PARTITION BY rồi ORDER BY. Nếu có chỉ mục cho đúng thứ tự đó, không cần sắp xếp gì cả.

row_number() over (partition by nv order by doanh_thu desc)

Chậm 6,5 lần, và phải ghi 50 MB ra đĩa tạm.

Một chi tiết thú vị hơn: chỉ mục chỉ phủ cột PARTITION BY cũng đã giúp đáng kể. Với lag(doanh_thu) over (partition by nv order by luc) mà chỉ có chỉ mục trên (nv):

Incremental Sort
  Sort Key: bh.nv, bh.luc
  Presorted Key: bh.nv
  Full-sort Groups: 2000   Peak Memory: 28kB
  Pre-sorted Groups: 2000  Peak Memory: 71kB

Incremental Sort biết dữ liệu đã sắp theo nv, nên nó chỉ sắp xếp trong từng nhóm 1.000 dòng thay vì sắp cả 2 triệu dòng. Bộ nhớ đỉnh 71 kB thay vì 50 MB.

Với bảng lớn, đây là khác biệt giữa "sắp xếp trong bộ nhớ" và "tràn ra đĩa".

Nhiều cửa sổ khác thứ tự thì nhân lên

Từ một lên ba cửa sổ khác thứ tự: chậm 9 lần.

Mỗi mệnh đề OVER với thứ tự khác nhau bắt PostgreSQL sắp xếp lại toàn bộ tập dữ liệu. Ba thứ tự khác nhau là ba lần sắp xếp nối tiếp, và ở quy mô này chúng tràn ra đĩa.

Cách rẻ nhất khi cần nhiều cửa sổ là khai một lần rồi dùng lại:

select row_number() over w, rank() over w, dense_rank() over w
from bh
window w as (partition by nv order by doanh_thu desc);

Ba hàm dùng chung một WindowAgg và một lần sắp xếp. Đây là mệnh đề WINDOW — ít người dùng, và nó đúng là thứ giữ 582 ms thay vì 1.464 ms trong bảng trên.

Khi buộc phải có nhiều thứ tự khác nhau, hãy xếp chúng sao cho thứ tự nào có chỉ mục thì đặt trước — PostgreSQL xử lý các WindowAgg theo thứ tự giảm dần độ chi tiết của khoá sắp xếp, và cái đầu tiên có thể tận dụng chỉ mục.

Cái bẫy đã làm hỏng lần đo đầu

Lần đầu tôi đo bằng count(*):

select count(*) from (select sum(doanh_thu) over (partition by nv) s from bh) t;
--  52,99 ms

Con số 52,99 ms trông quá đẹp cho một phép gộp nhóm trên 2 triệu dòng. Kế hoạch giải thích:

Finalize Aggregate
  ->  Gather
        ->  Partial Aggregate
              ->  Parallel Seq Scan on bh

Không có nút WindowAgg nào. count(*) không đọc cột s, nên bộ tối ưu bỏ luôn phép tính cửa sổ. Tôi đang đo thời gian đếm dòng.

Sửa bằng cách bắt truy vấn ngoài dùng đúng cột đó:

select sum(s) from (select sum(doanh_thu) over (partition by nv) s from bh) t;
--  510,78 ms

Gần gấp 10 lần con số cũ.

Bài học áp cho mọi phép đo, không riêng window function: count(*) bao ngoài là cách đo tiện nhưng nó cho phép bộ tối ưu vứt bỏ chính thứ bạn đang đo. Luôn dùng một phép gộp chạm vào cột kết quả.

Một tối ưu mới nên biết

Kế hoạch của row_number() ... where r <= 3 trên PostgreSQL 16 có dòng này:

WindowAgg  (rows=6000)
  Run Condition: (row_number() OVER (?) <= 3)

Run Condition được thêm từ PostgreSQL 15: nó đẩy điều kiện r <= 3 vào trong WindowAgg, nên khi đã đủ 3 dòng của một nhân viên, nó bỏ qua phần còn lại của nhóm đó thay vì tính số thứ tự cho cả 1.000 dòng rồi lọc sau.

Điều này chỉ áp cho các hàm đơn điệu: row_number, rank, dense_rank. Nó không áp cho sum() over (...) hay count() over (...) vì giá trị của chúng không tăng đơn điệu theo thứ tự.

Nếu bạn đang chạy PostgreSQL 14 trở xuống, mẫu top-N-mỗi-nhóm bằng window function đắt hơn đáng kể so với những gì đo được ở đây, và LATERAL càng đáng dùng hơn.

Bảng chọn nhanh

Thử ba mươi giây

Lấy một truy vấn có OVER mà bạn thấy chậm:

explain (analyze, buffers) <truy vấn của bạn>;

Ba thứ cần nhìn:

1. Có nút Sort trước WindowAgg không. Nếu có, dựng chỉ mục khớp (cột PARTITION BY, cột ORDER BY) là cách nhanh nhất — 6,5 lần trong phép đo trên.
2. Sort Method có chữ external hoặc Disk không. Nếu có thì đang tràn ra đĩa; hoặc nâng work_mem, hoặc dựng chỉ mục.
3. Có bao nhiêu nút WindowAgg. Nhiều hơn một nghĩa là bạn có nhiều thứ tự khác nhau; kiểm xem gộp lại bằng WINDOW w AS (...) được không.

Phần sau đo GROUP BY, DISTINCT và HashAggregate: bao nhiêu bộ nhớ thật sự được dùng, và chuyện gì xảy ra khi không đủ.