CTE — mệnh đề WITH — làm truy vấn dài trở nên dễ đọc, chia thành các bước có tên. Nhiều lập trình viên dùng nó vô tư như một cách "làm sạch code". Nhưng cách PostgreSQL thực thi CTE đã thay đổi hoàn toàn giữa PG11 và PG12, và không biết điều đó có thể khiến truy vấn chậm hàng nghìn lần. Bài này đo thật sự khác biệt, và làm rõ khi nào bạn muốn materialize một cách chủ ý.
Thay đổi lớn ở PostgreSQL 12
Điểm mấu chốt về CTE:
- PostgreSQL ≤ 11: mọi CTE luôn được materialize — tính toàn bộ kết quả, lưu vào một bảng tạm, rồi truy vấn ngoài mới dùng. Đây là một rào chắn tối ưu (optimization fence): filter và điều kiện ở truy vấn ngoài không đẩy được vào CTE.
- PostgreSQL ≥ 12: CTE đơn giản (chỉ đọc, tham chiếu một lần, không hàm volatile) được inline mặc định — planner gộp CTE vào truy vấn chính, đẩy filter xuống, dùng được index. Thường nhanh hơn rất nhiều.
WITH t AS (SELECT * FROM sp) -- PG12+: inline
SELECT * FROM t WHERE ma='SP12345'; -- filter đẩy vào → Index Scan
Hình 1: PG12 đổi CTE từ luôn-materialize (rào chắn) sang inline mặc định. MATERIALIZED/NOT MATERIALIZED để ép rõ ràng; khi nào cần fence chủ ý.
Đo thật: chậm hơn 4.700 lần
Truy vấn WITH t AS (SELECT * FROM sp) SELECT * FROM t WHERE ma='SP12345' trên bảng 3 triệu dòng, so CTE inline (mặc định) với ép MATERIALIZED:
Hình 2: CTE inline: Index Scan, 0,068 ms (filter đẩy vào dùng index). CTE MATERIALIZED: dựng bảng tạm 3 triệu dòng rồi mới lọc, 324 ms — chậm hơn ~4.700 lần.
- CTE inline (mặc định PG12+):
Index Scan using idx_ma— điều kiệnma='SP12345'được đẩy vào CTE nên dùng index, 0,068 ms. - CTE MATERIALIZED:
CTE Scantrên mộtSeq Scantoàn bộ 3 triệu dòng — nó dựng cả bảng tạm 3 triệu dòng trước, rồi mới lọc, 324 ms.
Chậm hơn ~4.700 lần. Đây chính là cái bẫy của các phiên bản ≤ PG11: mọi CTE là rào chắn, nên viết WITH cho dễ đọc vô tình phá hết khả năng đẩy filter xuống index. PG12 sửa điều này bằng cách inline mặc định.
Kể cả khi CTE được dùng lại nhiều lần với điều kiện lọc được, inline vẫn thường thắng: đo WITH t AS (... gia<500 ...) SELECT ... t a JOIN t b — inline 83 ms so với MATERIALIZED 2.579 ms, vì inline đẩy gia<500 xuống mỗi tham chiếu thay vì tính md5 cho cả 3 triệu dòng.
Khi nào cần MATERIALIZED chủ ý
Inline là mặc định tốt, nhưng có ba trường hợp bạn cần ép MATERIALIZED:
Hàm volatile dùng nhiều lần, cần nhất quán. Nếu CTE chứa random(), nextval(), now() và được tham chiếu nhiều lần, inline sẽ tính lại mỗi lần — hai tham chiếu thấy giá trị khác nhau. Đo thật: WITH t AS (SELECT id, random()... ) SELECT (count FROM t), (count FROM t) — inline chạy random() hai lần, MATERIALIZED chạy một lần nên hai tham chiếu thấy cùng kết quả. Khi tính đúng đắn phụ thuộc điều này, MATERIALIZED là bắt buộc.
CTE data-modifying luôn materialize. WITH x AS (INSERT ... RETURNING ...) SELECT ... — CTE chứa INSERT/UPDATE/DELETE luôn được thực thi một lần và materialize, không bao giờ inline. Đây là hành vi cố định, không đổi.
Ép rào chắn khi bạn đã đo. Đôi khi inline khiến planner chọn kế hoạch tệ (ước lượng sai lan truyền qua CTE). Nếu bạn đo thấy MATERIALIZED cho kế hoạch tốt hơn, ép nó là hợp lý — nhưng chỉ sau khi đo, không phải theo thói quen.
Đánh đổi cần cân nhắc
Nâng cấp từ PG11 lên PG12+ có thể đổi kế hoạch. Một truy vấn dựa (vô tình hoặc cố ý) vào hành vi materialize cũ có thể đổi hành vi sau nâng cấp. Nếu một truy vấn chậm đi sau khi lên PG12+, kiểm xem CTE có bị inline gây ước lượng sai không — thêm MATERIALIZED để khôi phục hành vi cũ.
Đừng dùng CTE chỉ để "ép thứ tự thực thi". Trước PG12, nhiều người dùng CTE như một fence để kiểm soát planner. Mẹo đó không còn đáng tin từ PG12 (CTE inline). Muốn fence thì khai MATERIALIZED rõ ràng.
CTE vẫn tốt cho tính dễ đọc và đệ quy. Inline nghĩa là CTE giờ gần như "miễn phí" về hiệu năng cho trường hợp đơn giản — cứ dùng để code sạch. Và WITH RECURSIVE là công cụ không thể thay thế cho dữ liệu phân cấp (cây, đồ thị).
Ba ý mang về
- PG12 đổi CTE từ luôn-materialize (rào chắn) sang inline mặc định: đo thật, CTE inline dùng được index (0,068 ms) còn MATERIALIZED dựng cả bảng tạm 3 triệu dòng rồi mới lọc (324 ms) — chậm hơn ~4.700 lần vì filter không đẩy xuống được.
- Inline thường thắng vì đẩy filter xuống, kể cả khi CTE dùng lại nhiều lần (83 ms so với 2.579 ms) — nên với truy vấn thường, cứ dùng
WITHcho dễ đọc mà không lo hiệu năng. - Ép
MATERIALIZEDchủ ý khi cần: hàm volatile dùng nhiều lần (cần nhất quán), CTE data-modifying (luôn materialize), hoặc khi đo thấy inline cho kế hoạch tệ — và nhớ hành vi đổi khi nâng cấp từ PG11.
Phần sau ta xét một dạng truy vấn con dễ viết mà tốn kém âm thầm: Phần sau đo subquery tương quan — vì sao nó chạy lại một lần cho mỗi dòng ngoài, và cách viết lại thành JOIN hay hàm cửa sổ.
