Cùng bài top-3 mỗi nhân viên: SQL mất 78 ms, kéo về ứng dụng tự tính mất 1.070 ms và 25 MB qua mạng

Muốn tra cứu ở thư viện, bạn không bảo thủ thư chở cả mười nghìn cuốn về nhà để rồi tự lục ra ba cuốn mình cần — bạn gửi câu hỏi, họ tìm đúng ba cuốn, rồi gửi ba cuốn ấy. Chở cả kệ về vừa tốn chuyến xe vừa chật phòng khách, mà lại mất luôn cái mục lục của thư viện. Tính toán dữ liệu cũng vấp đúng chỗ đó, và không nhận ra là lý do một phép top-3 tưởng nhẹ lại ngốn 25 MB qua mạng. 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

Mẫu số chung

Chuyện tự tính ở ứng dụng chậm 13,6 lần không phải vì Python chậm — nó là hệ quả của một nguyên tắc lớn: một phép tính làm nhỏ đầu vào (lọc, gộp, top-N) thì phải chạy sát dữ liệu, để chỉ cái kết quả nhỏ đi qua ranh giới, chứ không phải cả đầu vào lớn. Kéo 25 MB về để trả ra 6.000 dòng là lãng phí đường truyền, lãng phí bộ nhớ, và vứt luôn cái chỉ mục mà nơi ở của dữ liệu vốn có. Bản năng "cứ lấy về rồi xử lý trong mã của mình" trả một cái giá tỉ lệ với đầu vào — đúng thứ mà phép tính này lẽ ra làm nhỏ đi.

Đúng nguyên tắc "đưa hàm tới dữ liệu, đừng kéo dữ liệu tới hàm" này ở khắp nơi:

• Trong xử lý dữ liệu lớn, đây chính là khẩu hiệu của Hadoop/Spark: đưa tính toán tới chỗ dữ liệu, vì dữ liệu thì nặng còn hàm thì nhẹ; và predicate/aggregate pushdown trong Parquet hay các engine truy vấn đẩy bộ lọc xuống tận tầng lưu trữ để đọc ít nhất có thể.
• Trong Java/Go, mẫu chống là SELECT * rồi lọc trong stream/vòng lặp: ứng dụng không dùng được chỉ mục và phải nhận hết dữ liệu; viết WHERE/phép gộp vào truy vấn thì cơ sở dữ liệu làm phần thu nhỏ trước khi truyền.
• Ở tầng kiến trúc, cùng logic đẩy tính toán ra biên (CDN edge compute chạy sát người dùng) hay vào stored procedure sát dữ liệu — di chuyển cái nhẹ (mã) tới cái nặng (dữ liệu), không ngược lại.

Nên câu hỏi đúng khi định "lấy dữ liệu về rồi xử lý" là "phép xử lý này làm dữ liệu to ra hay nhỏ đi" — nếu nhỏ đi, hãy làm nó ngay tại nơi dữ liệu ở và chỉ chuyển kết quả; chỉ kéo dữ liệu thô về khi bạn thật sự cần từng byte của nó. Tự soát ngay một truy vấn OVER 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 đủ.