Khi một query lớn dần — nhiều tầng subquery lồng nhau, đọc từ trong ra ngoài như bóc hành — nó thành cơn ác mộng để đọc và sửa. CTE (Common Table Expression, cú pháp WITH) giải quyết điều đó: nó cho phép đặt tên cho từng bước tính toán, viết query theo trình tự từ trên xuống như đọc một đoạn văn. Và CTE còn mở ra một khả năng mà subquery thường không có: recursive CTE — duyệt cây và đồ thị (cây tổ chức, cây danh mục, đường đi trong graph) chỉ bằng một câu SQL.

Nhưng CTE có một chi tiết hiệu năng mà nếu không biết sẽ khiến bạn ngạc nhiên khó chịu: từ trường hợp "rào tối ưu" (optimization fence). Trước PostgreSQL 12, mọi CTE luôn bị materialize — tính xong toàn bộ rồi mới dùng, chặn planner tối ưu xuyên qua. Từ PG12, CTE mặc định được inline, nhưng từ khoá MATERIALIZED vẫn ép hành vi cũ. Bài này (phần 9 loạt SQL sâu) đo thật cả sức mạnh của CTE lẫn cái bẫy materialization đó.

Cơ chế: WITH, recursive, và materialization

Ảnh chụp đoạn mã nền tối minh hoạ CTE và recursive CTE, khối CTE thường cộng recursive duyệt cây CTE thường đặt tên cho một bước query dễ đọc theo tầng WITH buoc1 AS buoc2 AS SELECT FROM buoc2 recursive duyệt cây tổ chức id manager_id WITH RECURSIVE cay AS neo gốc manager_id IS NULL SELECT id name 1 AS level FROM employees WHERE manager_id IS NULL UNION ALL đệ quy nối con vào cha đã có trong cay SELECT e.id e.name c.level cộng 1 FROM employees e JOIN cay c ON e.manager_id bằng c.id SELECT sao FROM cay, khối bẫy MATERIALIZED PostgreSQL 12 cộng mặc định PG12 cộng CTE được INLINE planner đẩy WHERE vào trong WITH x AS SELECT sao FROM big SELECT sao FROM x WHERE id bằng 500000 MATERIALIZED ép tính CTE trước rào tối ưu không đẩy WHERE được WITH x AS MATERIALIZED SELECT sao FROM big

Hình 1: CTE thường đặt tên cho từng bước (WITH a AS (...), b AS (...)); recursive CTE dùng WITH RECURSIVE với phần neo (gốc) UNION ALL phần đệ quy (nối con vào cha) để duyệt cây. Bẫy: PG12+ mặc định inline CTE (planner đẩy được WHERE vào trong), nhưng MATERIALIZED ép tính toàn bộ CTE trước như một rào tối ưu.

Đo thật trong pg-lab

Mình chạy trong pg-lab (PostgreSQL 16): bảng employees (cây tổ chức) và bảng big (1 triệu dòng) để đo materialization.

Ảnh chụp bảng kết quả chạy thật trong pg-lab output thật postgresql 16 employees cộng bảng big 1.000.000 dòng, khối một recursive CTE cây tổ chức từ CEO xuống CEO level 1 VP-KyThuat level 2 VP-KinhDoanh level 2 Truong-Backend level 3 Truong-Frontend level 3 Dev-An level 4 Dev-Binh level 4 duyệt cả cây bằng 1 query, khối hai INLINE vs MATERIALIZED cùng query WHERE id bằng 500000 trên 1 triệu dòng cách viết CTE WITH x AS inline mặc định PG12 cộng Plan Index Scan thời gian 0.098 ms WITH x AS MATERIALIZED Plan Seq Scan cả 1 triệu dòng thời gian 99.756 ms INLINE planner đẩy được WHERE id bằng 500000 vào trong CTE Index Scan chỉ 1 dòng 0.098ms MATERIALIZED ép tính toàn bộ 1 triệu dòng trước rồi mới lọc chậm hơn khoảng 1000 lần CTE fence MATERIALIZED là con dao hai lưỡi

Hình 2: Kết quả thật — ① recursive CTE dựng cây tổ chức từ CEO (level 1) xuống Dev (level 4) chỉ bằng một query; ② cùng query WHERE id=500000 trên 1 triệu dòng: CTE inline (mặc định) dùng Index Scan, 0.098ms, còn MATERIALIZED buộc Seq Scan cả 1 triệu dòng, 99.756ms — chậm hơn ~1000 lần.

Hai bài học:

  • Recursive CTE duyệt cây trong một query. Bảng employees(id, name, manager_id) là một cây. Recursive CTE gồm hai phần nối bằng UNION ALL: neo chọn gốc (CEO, manager_id IS NULL), đệ quy lặp nối mỗi nhân viên vào cấp trên đã có trong kết quả, kèm level+1. Kết quả là cả cây tổ chức với đúng cấp bậc — CEO (1) → VP (2) → Trưởng nhóm (3) → Dev (4). Không recursive CTE, việc này cần vòng lặp trong code ứng dụng với N query (đúng kiểu N+1 ở phần 5). Đây là công cụ mạnh cho mọi dữ liệu phân cấp: cây danh mục, luồng bình luận, sơ đồ phụ thuộc.
  • MATERIALIZED có thể làm chậm 1000 lần. Đây là cái bẫy. Cùng query lấy WHERE id=500000 từ một CTE trên bảng 1 triệu dòng: CTE inline (mặc định PG12+) cho planner đẩy điều kiện WHERE vào trong CTE, dùng Index Scan tìm đúng một dòng — 0.098ms. Nhưng thêm MATERIALIZED, planner không thể đẩy WHERE vào; nó buộc phải tính toàn bộ CTE (Seq Scan cả 1 triệu dòng) rồi mới lọc ở ngoài — 99.756ms, chậm hơn ~1000 lần. Cùng một logic, chỉ khác một từ khoá.

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

Trước PG12, mọi CTE là rào tối ưu — cẩn thận khi đọc code cũ. Nếu bạn làm việc với PostgreSQL cũ (< 12) hoặc một CSDL khác, CTE có thể luôn materialize — nghĩa là cái query dùng CTE "cho dễ đọc" của bạn có thể chậm bất ngờ vì planner không tối ưu xuyên qua được. Đây là lý do trước đây nhiều người tránh CTE cho query hiệu năng-nhạy-cảm và dùng subquery thay thế. Trên PG12+, mối lo này giảm nhiều (inline mặc định), nhưng khi đọc code cũ hoặc chuyển CSDL, phải biết hành vi materialization của phiên bản đó.

MATERIALIZED không phải luôn xấu — đôi khi là công cụ đúng. Đừng vội kết luận "MATERIALIZED là chậm, tránh xa". Nó có ích trong hai tình huống: (1) CTE được dùng nhiều lần trong query — materialize tính một lần rồi tái dùng, thay vì inline chạy lại mỗi lần; (2) khi bạn muốn chặn planner đẩy một điều kiện vào chỗ sinh ra plan tệ (hiếm, nhưng có). MATERIALIZED là một công cụ điều khiển planner — dùng có chủ đích. Cái sai là dùng nó (hoặc gặp nó ở PG cũ) mà không biết nó chặn tối ưu.

Recursive CTE mạnh nhưng dễ vòng lặp vô hạn. Nếu dữ liệu có chu trình (A quản lý B, B quản lý A — dữ liệu bẩn) hoặc bạn quên điều kiện dừng, recursive CTE sẽ chạy mãi. UNION (thay vì UNION ALL) khử trùng lặp giúp dừng trong nhiều trường hợp, nhưng an toàn nhất là thêm cột level và giới hạn độ sâu (WHERE level < 100), hoặc dùng mảng lưu đường đi đã qua để phát hiện chu trình. PostgreSQL 14+ có cú pháp CYCLE để tự phát hiện. Với dữ liệu graph có chu trình, luôn phòng vòng lặp.

Ba ý mang về

  1. CTE làm query dễ đọc, recursive CTE duyệt cây trong một câu: WITH đặt tên cho từng bước (đọc từ trên xuống thay vì bóc subquery lồng); WITH RECURSIVE (neo UNION ALL đệ quy) dựng cả cây tổ chức từ CEO (level 1) xuống Dev (level 4) — thay cho vòng lặp N+1 trong code ứng dụng.
  2. MATERIALIZED là rào tối ưu, có thể chậm 1000 lần: đo thật, cùng query WHERE id=500000 trên 1 triệu dòng — CTE inline (mặc định PG12+) đẩy WHERE vào → Index Scan 0.098ms, còn MATERIALIZED buộc quét cả bảng → 99.756ms; một từ khoá đổi hoàn toàn hiệu năng.
  3. Dùng MATERIALIZED có chủ đích, phòng vòng lặp recursive: MATERIALIZED tốt khi CTE dùng nhiều lần hoặc cần chặn planner có chủ ý, xấu khi vô tình chặn đẩy điều kiện; recursive CTE cần điều kiện dừng (giới hạn level, UNION khử trùng, hoặc CYCLE ở PG14+) để tránh vòng lặp vô hạn với dữ liệu có chu trình.

Nguồn

Phần sau ta giải một bài toán tưởng đơn giản mà sai ở quy mô: phân trang. Vì sao OFFSET 1000000 LIMIT 20 chậm dần theo trang, và keyset pagination (con trỏ) giữ tốc độ ổn định — đo thật thời gian theo từng trang.