CTE trong PostgreSQL có phải là "optimization fence" không? `MATERIALIZED` / `NOT MATERIALIZED` để làm gì?

Trước PostgreSQL 12, mọi CTE đều bị vật chất hoá bắt buộc: tính xong toàn bộ rồi mới dùng.

Điều kiện ở truy vấn ngoài không đẩy được vào trong CTE — đó là nghĩa của "optimization fence".

-- PostgreSQL 11: quet toan bo bang roi moi loc
WITH big AS (SELECT * FROM orders)
SELECT * FROM big WHERE id = 42;

Từ 12 trở đi, CTE được inline (gộp vào truy vấn ngoài như subquery) nếu thoả cả ba: không đệ quy, chỉ được tham chiếu một lần, và không có tác dụng phụ (INSERT/UPDATE/DELETE, hàm VOLATILE). Khi đó filter đẩy được vào trong và dùng index bình thường.

Hai từ khoá để ép rõ ý:

-- ep tinh mot lan, dung lai nhieu lan (tranh chay lai bieu thuc dat tien)
WITH heavy AS MATERIALIZED (SELECT ... FROM huge_table GROUP BY ...)
SELECT * FROM heavy a JOIN heavy b ON ...;

-- ep inline du CTE duoc tham chieu nhieu lan
WITH lookup AS NOT MATERIALIZED (SELECT id, name FROM categories)
SELECT ... FROM lookup ...;

Khi nào dùng:
- MATERIALIZED khi CTE đắt và được dùng lại, hoặc khi muốn cố định kết quả trong một transaction.
- NOT MATERIALIZED khi CTE chỉ là lớp đặt tên cho dễ đọc và bạn muốn optimizer đẩy được điều kiện xuống.
- CTE có INSERT/UPDATE/DELETE ... RETURNING (data-modifying CTE) luôn bị vật chất hoá và chỉ chạy đúng một lần.

MySQL 8.0 không có hai từ khoá này; CTE ở đó có thể được merge hoặc materialize tuỳ optimizer.