LATERAL join trong PostgreSQL: top-N mỗi nhóm gọn gàng, nhanh hơn window function

Bài toán "lấy 3 đơn mới nhất của mỗi khách" nghe đơn giản nhưng SQL chuẩn xử lý khá vụng: bạn thường phải viết window function row_number() rồi lọc, hoặc subquery tương quan lặp lại. LATERAL join là công cụ được sinh ra cho đúng lớp bài toán này — nó cho phép một subquery ở bên phải JOIN tham chiếu cột của bảng bên trái, điều mà subquery thường trong FROM không làm được. Bài này đo thật vì sao LATERAL + LIMIT + index thường thắng cả window function cho top-N mỗi nhóm.

Vấn đề: subquery trong FROM không thấy cột bên trái

Trực giác đầu tiên khi cần "3 đơn mới nhất mỗi khách" là viết thế này — và nó báo lỗi:

SELECT k.id, d.* FROM khach k
JOIN (SELECT * FROM don
      WHERE khach_id = k.id          -- LỖI: subquery thường KHÔNG thấy k.id
      ORDER BY ngay DESC LIMIT 3) d ON true;

Một subquery trong mệnh đề FROM được tính độc lập, trước khi join xảy ra, nên nó không có khái niệm "dòng k hiện tại" để tham chiếu. LATERAL chính là từ khóa gỡ bỏ giới hạn đó.

Hình 1: Subquery thường trong FROM không thấy k.id; LATERAL cho phép tham chiếu đó và chạy subquery một lần cho mỗi dòng bên trái. Kết hợp LIMIT + index cho top-N mỗi nhóm.

LATERAL: mở khóa tham chiếu cột trái

Thêm LATERAL trước subquery là đủ:

SELECT k.id, k.ten, d.id, d.ngay, d.tien
FROM khach k
CROSS JOIN LATERAL (
  SELECT id, ngay, tien FROM don
  WHERE don.khach_id = k.id           -- OK: LATERAL cho phép đọc k.id
  ORDER BY ngay DESC LIMIT 3
) d;

Ngữ nghĩa: với mỗi dòng k ở bên trái, PostgreSQL chạy subquery bên phải một lần, truyền k.id vào. Đây chính xác là vòng lặp bạn sẽ viết ở tầng ứng dụng — nhưng gói gọn trong một truy vấn, và planner biến nó thành Nested Loop.

Điểm mấu chốt về hiệu năng: vì subquery có ORDER BY ngay DESC LIMIT 3 và có index (khach_id, ngay DESC), mỗi lần chạy là một Index Scan dừng ngay sau 3 dòng — không đọc hết đơn của khách đó.

Đo thật: LATERAL vs window function

Bảng khach 100.000 dòng, don 3.000.000 dòng, index (khach_id, ngay DESC). Cùng câu hỏi "3 đơn mới nhất mỗi khách", hai cách viết:

Hình 2: LATERAL + LIMIT 3 chạy ~158 ms, đọc ~600 nghìn trang đệm; window row_number() phải quét cả 3 triệu dòng rồi lọc rn ≤ 3, mất ~540 ms và đọc ~2,99 triệu trang — LATERAL nhanh hơn ~3,4 lần, đọc ~5 lần ít trang hơn.

• LATERAL + LIMIT 3: Nested Loop → Index Scan + Limit, loops=100.000. Mỗi khách chỉ chạm ~3 dòng nhờ index trả sẵn thứ tự và LIMIT dừng sớm — ~158 ms, đọc ~600.000 trang đệm.
• window row_number(): WindowAgg phải sinh số thứ tự cho cả 3.000.000 dòng (dù mỗi khách chỉ giữ 3), rồi mới lọc rn ≤ 3 — ~540 ms, đọc ~2,99 triệu trang đệm.

LATERAL nhanh hơn ~3,4 lần và đọc ~5 lần ít dữ liệu, vì nó không phải xử lý toàn bảng. Window function không tránh được việc gán rn cho mọi dòng trước khi lọc — đó là bản chất của nó. Với top-N mỗi nhóm mà N nhỏ và có index phù hợp, LATERAL là lựa chọn thắng.

LATERAL làm được điều JOIN thường không

Ngoài top-N, LATERAL mở ra những dạng truy vấn khác. Ví dụ, một subquery tổng hợp tham chiếu cột trái:

SELECT k.id, s.so_don, s.tong
FROM khach k
LEFT JOIN LATERAL (
  SELECT count(*) AS so_don, sum(tien) AS tong FROM don WHERE khach_id=k.id
) s ON true;

LEFT JOIN LATERAL ... ON true giữ cả khách không có đơn (giống LEFT JOIN bình thường). Điểm khác biệt là subquery có thể chứa LIMIT, ORDER BY, hàm sinh tập (generate_series), hay gọi hàm trả bảng với tham số lấy từ cột trái — những thứ một JOIN phẳng không diễn đạt được. LATERAL là cầu nối giữa "một truy vấn khai báo" và "một vòng lặp có tham số".

Đánh đổi cần cân nhắc

LATERAL là Nested Loop — cần index trên cột tương quan. Sức mạnh của nó đến từ việc mỗi lần lặp là một index lookup rẻ. Nếu don.khach_id không có index, mỗi lần lặp thành Seq Scan toàn bảng và bạn rơi vào đúng bẫy O(n×m) như subquery tương quan không index (bài pg-047 đã đo 41 giây). LATERAL nhanh nhờ index, không phải bất chấp nó.

Số dòng bên trái lớn thì loops lớn. Với 100.000 khách, loops=100.000 vẫn rẻ vì mỗi lần chỉ 3 dòng. Nhưng nếu bảng trái hàng chục triệu dòng, chi phí khởi động mỗi lần lặp cộng dồn lại. Khi đó, đo cả window function và DISTINCT ON để so — không có công cụ nào thắng tuyệt đối.

Window function vẫn thắng khi cần xử lý toàn bộ. Nếu bạn cần tính toán trên mọi dòng (running total, xếp hạng đầy đủ, so dòng với dòng kế), window function là đúng — nó quét toàn bảng vì bạn cần toàn bảng. LATERAL thắng riêng ở trường hợp top-N nhỏ mỗi nhóm, nơi phần lớn dữ liệu bị bỏ đi.

Ba ý mang về

1. LATERAL cho subquery bên phải tham chiếu cột bảng bên trái, chạy một lần cho mỗi dòng trái (Nested Loop) — điều subquery thường trong FROM không làm được; đây là cách khai báo một "vòng lặp có tham số" ngay trong SQL.
2. Top-N mỗi nhóm: LATERAL + LIMIT + index thường thắng window function: đo thật, 3 đơn mới nhất mỗi khách chạy 158 ms (đọc 600 nghìn trang) vì LIMIT dừng sớm, so với window row_number() phải quét cả 3 triệu dòng rồi lọc (540 ms, 3 triệu trang).
3. LATERAL nhanh nhờ index trên cột tương quan — không có index thì mỗi lần lặp thành seq scan và rơi vào O(n×m); và window function vẫn là công cụ đúng khi bạn thật sự cần xử lý mọi dòng.

Phần sau ta xét một cấu trúc điều kiện âm thầm vô hiệu hóa index: Phần sau đo vì sao OR giữa hai cột khác nhau khiến PostgreSQL bỏ index, và cách UNION hay chỉ mục biểu thức cứu vãn.