CTE (Common Table Expression — mệnh đề WITH ... AS) là cách chia một truy vấn phức tạp thành các bước có tên, đọc từ trên xuống như một đoạn văn. Nó làm SQL dễ đọc hơn hẳn, nhất là với những truy vấn nhiều tầng mà viết lồng subquery sẽ rối như tơ vò. Nhưng quanh CTE có một câu chuyện hiệu năng lâu đời — "CTE là hàng rào tối ưu, luôn chậm hơn viết subquery" — mà tôi mang theo vào bài này, và khi đo trên PostgreSQL đời mới, nó hóa ra đã hết đúng. Đây là một bài học về việc kiểm lại niềm tin cũ theo đúng phiên bản mình đang chạy.
Hàng rào tối ưu: một luật đã đổi
Trước PostgreSQL 12, CTE là một optimization fence (hàng rào tối ưu): PostgreSQL luôn vật chất hóa (materialize) nó — chạy CTE một cách độc lập, dựng cả kết quả trung gian, rồi truy vấn ngoài mới đọc từ kết quả đó. Planner không đẩy điều kiện WHERE của truy vấn ngoài vào trong CTE. Hệ quả: nếu CTE của bạn là "toàn bộ bảng" và truy vấn ngoài lọc mạnh, CSDL vẫn dựng cả bảng rồi mới lọc — quét thừa khủng khiếp.
Từ PostgreSQL 12, luật đổi: một CTE không được tham chiếu nhiều lần và không có side-effect được inline — gộp thẳng vào truy vấn ngoài như thể nó là một subquery, và planner tối ưu xuyên qua nó. Bạn có thể ép tay bằng hai từ khóa: AS MATERIALIZED buộc hàng rào (như hành vi cũ), AS NOT MATERIALIZED buộc inline. Bài này đo cả hai trên PostgreSQL 16 để thấy khác biệt.
Một lần tôi đo hớ: CTE thường hóa ra nhanh y subquery
Tôi dựng bảng 2 triệu hàng với index trên cột k, rồi viết một truy vấn CTE lọc mạnh:
WITH c AS (SELECT * FROM t) SELECT * FROM c WHERE k = 12345;
Mang niềm tin "CTE là hàng rào", tôi đợi nó dựng cả 2 triệu hàng rồi mới lọc — chậm. Nhưng EXPLAIN ANALYZE cho thấy điều ngược lại:
A) CTE mặc định: Index Scan using t_k_idx, 4 trang, 0.134 ms
Nhanh như một truy vấn tra index bình thường. Planner đã đẩy WHERE k = 12345 vào trong CTE, và dùng index trên k — chạm đúng một hàng, đọc 4 trang. Không hề có CTE Scan nào trong kế hoạch; PostgreSQL đã hòa tan CTE vào truy vấn ngoài đến mức nó biến mất khỏi kế hoạch, chỉ còn lại chính cái Index Scan mà một subquery hay một câu SELECT phẳng cũng cho ra. Cái CTE hoàn toàn "trong suốt" với planner, y hệt một subquery. Niềm tin của tôi — chính xác cho PostgreSQL trước phiên bản 12 — đã lỗi thời từ nhiều năm; trên PG16, CTE thường được inline mặc định.
Để thấy cái hàng rào cũ, tôi ép nó bằng AS MATERIALIZED:
WITH c AS MATERIALIZED (SELECT * FROM t) SELECT * FROM c WHERE k = 12345;
B) AS MATERIALIZED: CTE Scan, dựng cả 2 triệu hàng, 10811 trang + temp, 95 ms
(Rows Removed by Filter: 1999999)
Giờ thì đúng như nỗi sợ: PostgreSQL dựng toàn bộ CTE — Seq Scan cả 2 triệu hàng, tràn ra file tạm — rồi CTE Scan mới lọc, vứt bỏ 1.999.999 hàng để giữ đúng một. Đọc 10811 trang thay vì 4, mất 95ms thay vì 0,134ms — chậm hơn khoảng 720 lần, cho cùng một logic, chỉ khác đúng một từ khóa MATERIALIZED. Đáng chú ý là con số trang (10811 so với 4) còn nói rõ hơn cả thời gian: nó cho thấy phiên bản materialized đọc gần 2700 lần nhiều trang hơn — đúng bằng việc nó phải chạm cả bảng thay vì một hàng. Như các bài trước của sê-ri, số trang đọc là thước đo công việc trung thực nhất. Bài học đo lường: một luật hiệu năng có thể đúng ở phiên bản này và sai ở phiên bản khác; đừng tin folklore, hãy chạy EXPLAIN trên đúng phiên bản của mình. Tôi suýt viết cả bài khuyên "tránh CTE vì nó là hàng rào" — một lời khuyên đúng năm 2018 và sai năm nay.
Khi CTE vẫn là hàng rào
Điều này không có nghĩa CTE luôn miễn phí. Hàng rào vẫn xuất hiện trong ba trường hợp. Một, khi bạn ép AS MATERIALIZED — đôi khi cố ý, để tính một kết quả tốn kém một lần rồi dùng lại nhiều nơi. Hai, CTE đệ quy (WITH RECURSIVE) luôn được vật chất hóa, không inline được. Ba, một CTE được tham chiếu nhiều lần trong truy vấn ngoài có thể được vật chất hóa để khỏi tính lại. Trong những ca đó, hành vi "dựng cả rồi mới dùng" quay lại, và nếu truy vấn ngoài lọc mạnh, bạn trả giá đúng như phép đo B.
Điều quan trọng là biết mình đang ở ca nào — và cách duy nhất chắc chắn là đọc kế hoạch. Như bài EXPLAIN đã cho thấy, kế hoạch thật kể đúng những gì CSDL làm; một CTE Scan với Rows Removed by Filter khổng lồ là dấu hiệu rõ ràng của một hàng rào đang quét thừa.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: trên PostgreSQL 12 trở lên, cứ dùng CTE cho dễ đọc — nó thường không phạt hiệu năng. Chia một truy vấn rối thành các bước WITH có tên làm code dễ hiểu và dễ sửa, và planner vẫn tối ưu xuyên qua như subquery. Nỗi sợ "CTE chậm" phần lớn là di sản của PostgreSQL cũ. Trong nhiều dự án, ưu tiên viết cho người đọc hiểu — với CTE có tên rõ ràng — đáng giá hơn một tối ưu vi mô mà planner hiện đại vốn đã tự làm; code dễ đọc ít lỗi hơn, và một truy vấn đúng bao giờ cũng thắng một truy vấn nhanh nhưng sai.
Hệ quả thứ hai: nhưng biết ba ca hàng rào, và kiểm bằng EXPLAIN. Nếu bạn thấy một truy vấn dùng CTE chậm bất ngờ, kiểm xem nó có AS MATERIALIZED, có đệ quy, hay bị tham chiếu nhiều lần không — và nhìn kế hoạch tìm CTE Scan với số hàng bị lọc bỏ lớn. Con số mang theo: từ PG12, CTE thường được inline như subquery — WHERE được đẩy vào trong, dùng index (0.134ms, 4 trang); nhưng AS MATERIALIZED (và CTE đệ quy) buộc dựng cả bảng trung gian rồi mới lọc, quét thừa (95ms, 10811 trang) — chậm 720 lần cho cùng logic; luật "CTE là hàng rào" đã hết đúng mặc định, phải kiểm theo phiên bản. Đừng để một quy tắc cũ quyết định thay cho một lần chạy EXPLAIN.
Thử ba mươi giây
Trên PostgreSQL 12 trở lên, tạo một bảng có index (CREATE TABLE t(k int); CREATE INDEX ON t(k); INSERT INTO t SELECT g FROM generate_series(1,500000) g;). Chạy EXPLAIN WITH c AS (SELECT * FROM t) SELECT * FROM c WHERE k = 42; — bạn sẽ thấy một Index Scan, chứng tỏ CTE được inline và WHERE đẩy vào trong. Giờ thêm một từ: EXPLAIN WITH c AS MATERIALIZED (SELECT * FROM t) SELECT * FROM c WHERE k = 42; — kế hoạch chuyển thành CTE Scan trên một Seq Scan cả bảng, với điều kiện lọc sau khi vật chất hóa. Một từ khóa, hai kế hoạch hoàn toàn khác nhau — và EXPLAIN cho bạn thấy đúng cái nào bạn đang chạy, không cần đoán theo phiên bản.
