CTE tốt, CTE xấu

Biểu thức bảng chung hay CTE thường là tính năng đầu tiên mà các nhà phát triển tiếp cận với SQL cơ bản và thường là tính năng duy nhất. Bạn viết một truy vấn con sau WITH, đặt tên cho nó và sử dụng nó trong phần còn lại của truy vấn. Nó chỉ tồn tại trong khoảng thời gian của truy vấn đó.

Nhưng sự phổ biến của CTE thường ít liên quan đến việc hiện đại hóa mã mà liên quan nhiều hơn đến hứa hẹn về logic mệnh lệnh. Đối với nhiều người, CTE hoạt động như một phương pháp khắc phục dễ hiểu cho các 'truy vấn đáng sợ' và cách thức buộc thực hiện lệnh trên cơ sở dữ liệu. Số lượng truy vấn ghi giống như thể chúng yêu cầu trình tối ưu hóa "trước tiên hãy làm cái này, sau đó làm cái kia".

Điều này tạo ra một vấn đề. CTE xử lý các DDL phân tách truy vấn, đệ quy và đa câu lệnh. Tuy nhiên, Planner xử lý chúng một cách khác nhau tùy thuộc vào cách bạn viết và sử dụng chúng. Trong một thời gian dài (trước PostgreSQL 12), CTE đóng vai trò là hàng rào tối ưu hóa. Trình lập kế hoạch không thể đẩy các vị từ vào chúng, không thể sử dụng các chỉ mục trên các bảng bên dưới. Không thể làm bất cứ điều gì để hiện thực hóa chúng và quét qua kết quả.

PostgreSQL 12 đã thay đổi điều này. Giờ đây, CTE được nội tuyến, cụ thể hóa hoặc ở giữa, tùy thuộc vào cách bạn viết chúng.

Lược đồ mẫu

Chúng ta sẽ sử dụng cùng một lược đồ như trong bài viết Thống kê PostgreSQL: Tại sao truy vấn chạy chậm.

CREATE TABLE customers (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE orders (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id integer NOT NULL REFERENCES customers(id),
    amount numeric(10,2) NOT NULL,
    status text NOT NULL DEFAULT 'pending',
    note text,
    created_at date NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE orders_archive (LIKE orders INCLUDING ALL EXCLUDING IDENTITY);

INSERT INTO customers (name)
SELECT 'Customer ' || i
FROM generate_series(1, 2000) AS i;

INSERT INTO orders (customer_id, amount, status, note, created_at)
SELECT
    (random() * 1999 + 1)::int,
    (random() * 500 + 5)::numeric(10,2),
    (ARRAY['pending','shipped','delivered','cancelled'])[floor(random()*4+1)::int],
    CASE WHEN random() < 0.3 THEN 'Some note text here for padding' ELSE NULL END,
    '2022-01-01'::date + (random() * 1095)::int
FROM generate_series(1, 100000);

ANALYZE customers;
ANALYZE orders;

For recursive examples later on we'll also need an employees table with a self-referencing hierarchy:

CREATE TABLE employees (
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    manager_id integer REFERENCES employees(id),
    department text NOT NULL
);

INSERT INTO employees (name, manager_id, department) VALUES
    ('Alice',   NULL, 'Engineering'),
    ('Bob',     1,    'Engineering'),
    ('Charlie', 1,    'Engineering'),
    ('Diana',   2,    'Engineering'),
    ('Eve',     2,    'Engineering'),
    ('Frank',   3,    'Sales'),
    ('Grace',   3,    'Sales'),
    ('Hank',    6,    'Sales'),
    ('Ivy',     6,    'Sales');

ANALYZE employees;

Kỷ nguyên ràng tối ưu hóa (trước PG 12)

Như chúng ta đã đề cập trước PostgreSQL 12, mọi CTE đều được hiện thực hóa. Không có ngoại lệ. Trình lập kế hoạch sẽ tính toán đầy đủ tập kết quả CTE, lưu trữ nó trong một bộ dữ liệu tạm thời, sau đó quét bộ dữ liệu đó bất cứ khi nào truy vấn chính tham chiếu đến CTE. Điều này khiến CTE trở thành một hàng rào tối ưu hóa vì người lập kế hoạch không thể xem qua chúng.

Hãy xem xét truy vấn đơn giản này:

EXPLAIN WITH filtered AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

Trên PostgreSQL 11 trở về trước, kết quả EXPLAIN sẽ trông giống như thế này:

                            QUERY PLAN
-------------------------------------------------------------------
 CTE Scan on filtered  (cost=1840.00..2290.00 rows=2 width=58)
   Filter: (status = 'pending')
   CTE filtered
     ->  Seq Scan on orders  (cost=0.00..1840.00 rows=10000 width=58)
           Filter: (created_at > '2025-01-01'::date)

Hãy chú ý điều gì xảy ra ở đây. CTE chạy quét tuần tự trên đơn hàng bằng bộ lọc ngày. Nó hiện thực hóa tất cả các hàng phù hợp. Sau đó, truy vấn bên ngoài sẽ áp dụng bộ lọc status = 'pending' sau cụ thể hóa. Ngay cả khi tồn tại chỉ mục tổng hợp trên (created_at, status) thì trình lập kế hoạch cũng không thể sử dụng chỉ mục đó vì nó không thể nhìn xuyên qua ranh giới CTE để kết hợp các biến vị ngữ.

Tại sao nó được thiết kế theo cách này? Hai lý do. Đầu tiên, lý do là sự cô lập của ảnh chụp nhanh. Việc cụ thể hóa CTE đảm bảo rằng tập kết quả được tính toán một lần, từ một ảnh chụp nhanh, bất kể nó được tham chiếu bao nhiêu lần. Thứ hai, để bảo vệ cho các trường hợp có tác dụng phụ. Nếu CTE chứa câu lệnh sửa đổi dữ liệu (INSERT, UPDATE, DELETE), việc cụ thể hóa sẽ đảm bảo nó được thực thi chính xác một lần.

The workaround was well-known in the community: rewrite CTEs as subqueries. Các truy vấn con luôn tuân theo các quy tắc tối ưu hóa thông thường của người lập kế hoạch, bao gồm cả đẩy xuống vị ngữ và nội tuyến. Truy vấn tương tự được viết dưới dạng SELECT * FROM (SELECT * FROMorders WHERE create_at > '2025-01-01') sub WHERE status = 'pending' sẽ tạo ra một kế hoạch tốt hơn nhiều.

Điều này dẫn đến một nền văn hóa giải quyết hoàn toàn. Các nhà phát triển sẽ viết các truy vấn bằng CTE để dễ đọc trong quá trình phát triển, sau đó viết lại chúng dưới dạng các truy vấn con lồng nhau để sản xuất. Cộng đồng đã có câu nói: CTE là hàng rào tối ưu hóa. Nó đã được lặp đi lặp lại thường xuyên. Nhiều nhà phát triển vẫn tin vào điều đó cho đến ngày nay. Nhưng điều đó đã không còn đúng kể từ PostgreSQL 12.

PostgreSQL 12: Nội tuyến CTE

PostgreSQL 12 đã giới thiệu nội tuyến CTE tự động. Các CTE không đệ quy, không có tác dụng phụ, được tham chiếu đơn lẻ hiện được đặt nội tuyến theo mặc định. Người lập kế hoạch bắt đầu coi chúng như các truy vấn phụ và áp dụng tất cả các tối ưu hóa thông thường. Đẩy xuống vị ngữ, sử dụng chỉ mục, sắp xếp lại thứ tự tham gia áp dụng chính xác như thể cú pháp CTE chưa từng tồn tại.

Câu hỏi tương tự từ phần trước hiện tạo ra một kế hoạch hoàn toàn khác:

EXPLAIN WITH filtered AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

                                    QUERY PLAN
---------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2355.00 rows=2 width=58)
   Filter: ((created_at > '2025-01-01'::date) AND (status = 'pending'::text))

CTE hoàn toàn bị loại khỏi kế hoạch. Cả hai vị từ được hợp nhất thành một lần quét trên orders. Nếu có chỉ mục phù hợp thì người lập kế hoạch có thể sử dụng chỉ mục đó. Cú pháp CTE không thay đổi kế hoạch thực hiện.

PostgreSQL 12 cũng giới thiệu hai từ khóa mới cho phép bạn ghi đè quyết định của người lập kế hoạch:

MATERIALIZED - buộc CTE phải hiện thực hóa, ngay cả khi người lập kế hoạch đưa nó vào nội dung
NOT MATERIALIZED - bắt buộc phải nội tuyến hóa, ngay cả khi người lập kế hoạch sẽ hiện thực hóa nó

-- force materialization
EXPLAIN WITH filtered AS MATERIALIZED (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

-- Force inlining
EXPLAIN WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM filtered WHERE status = 'pending';

Điều này tuân theo nguyên tắc tương tự như XEM nội tuyến.

Khi nào CTE được hiện thực hóa?

Trường hợp 1: tham chiếu duy nhất, không có tác dụng phụ (INLINED)

Trường hợp đơn giản và phổ biến nhất. Nếu bạn tham chiếu CTE chính xác một lần và nó không chứa tác dụng phụ thì người lập kế hoạch sẽ đưa nó vào nội dung.

EXPLAIN WITH recent AS (
    SELECT * FROM orders WHERE created_at > '2025-01-01'
)
SELECT * FROM recent WHERE status = 'pending';

                                  QUERY PLAN
------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2355.00 rows=2 width=59)
   Filter: ((created_at > '2025-01-01'::date) AND (status = 'pending'::text))
(2 rows)

Cả hai vị từ đều được hợp nhất. Người lập kế hoạch xem xét trực tiếp tất cả các đường dẫn truy cập trên orders.

Trường hợp 2: nhiều tài liệu tham khảo (MATERIALIZED)

Khi một CTE được tham chiếu nhiều lần, người lập kế hoạch sẽ thực hiện nó. Đây thực chất là một tính năng, CTE được tính toán một lần và sử dụng lại. Do đó tránh làm việc dư thừa.

EXPLAIN WITH summary AS (
    SELECT status, count(*) AS cnt FROM orders GROUP BY status
)
SELECT a.status, b.status
FROM summary a, summary b
WHERE a.cnt > b.cnt;

                                 QUERY PLAN
----------------------------------------------------------------------------
 Nested Loop  (cost=2355.04..2355.52 rows=5 width=64)
   Join Filter: (a.cnt > b.cnt)
   CTE summary
     ->  HashAggregate  (cost=2355.00..2355.04 rows=4 width=17)
           Group Key: orders.status
           ->  Seq Scan on orders  (cost=0.00..1855.00 rows=100000 width=9)
   ->  CTE Scan on summary a  (cost=0.00..0.08 rows=4 width=40)
   ->  CTE Scan on summary b  (cost=0.00..0.08 rows=4 width=40)
(8 rows)

Các nút Quét CTE xuất hiện hai lần, nhưng HashAggregate chỉ chạy một lần. Đối với các phép tính tốn kém được tham chiếu nhiều lần, đây chính xác là điều bạn muốn.

Trường hợp 3: CTE đệ quy (ALWAYS MATERIALIZED)

CTE đệ quy phải duy trì một bảng làm việc giữa các lần lặp. Không có cách nào để nội tuyến chúng. Chúng tôi sẽ trình bày chi tiết về đệ quy ở phần sau của bài viết.

EXPLAIN WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;

                                          QUERY PLAN
-------------------------------------------------------------------------------
 CTE Scan on subordinates  (cost=17.21..18.83 rows=81 width=40)
   CTE subordinates
     ->  Recursive Union  (cost=0.00..17.21 rows=81 width=13)
           ->  Seq Scan on employees  (cost=0.00..1.11 rows=1 width=13)
                 Filter: (id = 1)
           ->  Hash Join  (cost=0.33..1.53 rows=8 width=13)
                 Hash Cond: (e.manager_id = s.id)
                 ->  Seq Scan on employees e  (cost=0.00..1.09 rows=9 width=13)
                 ->  Hash  (cost=0.20..0.20 rows=10 width=4)
                       ->  WorkTable Scan on subordinates s  (cost=0.00..0.20 rows=10 width=4)
(10 rows)

Trường hợp 4: CTE sửa đổi dữ liệu (ALWAYS MATERIALIZED)

CTE chứa INSERT, UPDATE hoặc DELETE luôn được hiện thực hóa. Các tác dụng phụ phải thực hiện chính xác một lần, theo thứ tự có thể dự đoán được.

EXPLAIN WITH deleted AS (
    DELETE FROM orders WHERE status = 'cancelled' RETURNING *
)
SELECT count(*) FROM deleted;

                                QUERY PLAN
---------------------------------------------------------------------------
 Aggregate  (cost=2670.13..2670.14 rows=1 width=8)
   CTE deleted
     ->  Delete on orders  (cost=0.00..2105.00 rows=25117 width=6)
           ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25117 width=6)
                 Filter: (status = 'cancelled'::text)
   ->  CTE Scan on deleted  (cost=0.00..502.34 rows=25117 width=0)
(6 rows)

Quét CTE hiện diện vì DELETE phải được thực thi đầy đủ trước khi count(*) có thể chạy.

Trường hợp 5: Hàm VOLATILE (MATERIALIZED)

Nếu CTE chứa VOLATILE thì người lập kế hoạch cụ thể hóa nó để ngăn việc đánh giá hàm nhiều lần với các kết quả có thể khác nhau.

EXPLAIN WITH rand AS (
    SELECT id, random() AS r FROM orders
)
SELECT * FROM rand WHERE r < 0.01;

                              QUERY PLAN
-----------------------------------------------------------------------
 CTE Scan on rand  (cost=2105.00..4355.00 rows=33333 width=12)
   Filter: (r < '0.01'::double precision)
   CTE rand
     ->  Seq Scan on orders  (cost=0.00..2105.00 rows=100000 width=12)
(4 rows)

Mặc dù rand chỉ được tham chiếu một lần nhưng CTE Scan vẫn ở đó. random() là VOLATILE, buộc phải hiện thực hóa.

Trường hợp 6: Hàm ỔN ĐỊNH (INLINED)

Các hàm

STABLE như now() không ngăn cản nội tuyến. Nguyên nhân là do thời gian bắt đầu giao dịch bị đóng băng.

EXPLAIN WITH recent AS (
    SELECT * FROM orders
    WHERE created_at > now() - interval '7 days'
)
SELECT * FROM recent WHERE status = 'pending';

                                       QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on orders  (cost=0.00..2855.00 rows=2 width=59)
   Filter: ((status = 'pending'::text) AND (created_at > (now() - '7 days'::interval)))
(2 rows)

Không Quét CTE. Trình lập kế hoạch nội tuyến CTE và hợp nhất cả hai vị từ, giống như Trường hợp 1. now() là STABLE vì nó trả về cùng một giá trị trong một giao dịch - và kiểm tra nội tuyến của người lập kế hoạch chỉ tìm kiếm các hàm VOLATILE (thông qua contain_volatile_functions()). STABLE vượt qua bước kiểm tra đó.

Tại sao mọi người nghĩ rằng STABLE chặn nội tuyến? Bởi vì trước PostgreSQL 12, tất cả CTE đều được hiện thực hóa bất chấp sự biến động. Khi PG 12 giới thiệu nội tuyến, rào cản cấp độ chức năng duy nhất là VOLATILE. Nhưng mô hình tinh thần "CTE là hàng rào tối ưu hóa" cũ đã ăn sâu đến mức nhiều nhà phát triển cho rằng ỔN ĐỊNH cũng là một vấn đề. Không phải vậy.

Hàm thực sự chặn nội tuyến: clock_timestamp(). Không giống như now(), nó VOLATILE và trả về một giá trị khác nhau cho mỗi lệnh gọi. CTE có clock_timestamp() sẽ được hiện thực hóa. Tương tự, random() và nextval() là VOLATILE và bắt buộc phải hiện thực hóa (như trong Trường hợp 5).

Nếu bạn thấy CTE có now() được hiện thực hóa thì nguyên nhân là do nguyên nhân khác. Hoặc nhiều tài liệu tham khảo, một câu lệnh sửa đổi dữ liệu hoặc một gợi ý VẬT LIỆU rõ ràng. Đừng đổ lỗi cho ỔN ĐỊNH.

Trường hợp 7: ép buộc hành vi bằng gợi ý

Bạn luôn có thể ghi đè quyết định của người lập kế hoạch.

-- force materialization on something that would normally be inlined
EXPLAIN WITH filtered AS MATERIALIZED (
    SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM filtered WHERE amount > 400;

                              QUERY PLAN
----------------------------------------------------------------------
 CTE Scan on filtered  (cost=2105.00..2670.58 rows=5290 width=92)
   Filter: (amount > '400'::numeric)
   CTE filtered
     ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=59)
           Filter: (status = 'pending'::text)
(5 rows)

-- force inlining on something that would normally be materialized
EXPLAIN WITH filtered AS NOT MATERIALIZED (
    SELECT * FROM orders WHERE status = 'pending'
)
SELECT * FROM filtered a
JOIN filtered b ON a.customer_id = b.customer_id;

                                    QUERY PLAN
--------------------------------------------------------------------------------
 Hash Join  (cost=2419.21..10686.65 rows=317742 width=118)
   Hash Cond: (orders.customer_id = orders_1.customer_id)
   ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=59)
         Filter: (status = 'pending'::text)
   ->  Hash  (cost=2105.00..2105.00 rows=25137 width=59)
         ->  Seq Scan on orders orders_1  (cost=0.00..2105.00 rows=25137 width=59)
               Filter: (status = 'pending'::text)
(7 rows)

Nếu CTE được cụ thể hóa, bạn sẽ thấy một lần quét bảng orders, sau đó là hai lần quét CTE trên kết quả. Thay vào đó, trình lập kế hoạch đã xử lý truy vấn của bạn như thể bạn đã viết một phép nối tiêu chuẩn giữa hai truy vấn phụ.

Hãy cẩn thận với NOT MATERIALIZED trên các CTE được tham chiếu nhiều lần. Khi bạn buộc nội tuyến, truy vấn con sẽ chạy một lần cho mỗi tham chiếu. Trong ví dụ trên, bảng orders được quét hai lần. Một lần cho a và một lần cho b. Đối với tập hợp kết quả nhỏ, điều này có thể ổn. Đối với những cái lớn, bạn đang làm công việc gấp đôi. Đo lường trước khi sử dụng.

Các mệnh đề khóa hàng buộc phải hiện thực hóa ngay cả trên CTE được tham chiếu đơn lẻ, không có tác dụng phụ. Trong nội bộ, kiểm tra contain_dml() của người lập kế hoạch xử lý CẬP NHẬT và FOR SHARE giống như các câu lệnh sửa đổi dữ liệu.

EXPLAIN WITH locked AS (
    SELECT * FROM orders WHERE status = 'pending' FOR UPDATE
)
SELECT * FROM locked WHERE amount > 400;

                                 QUERY PLAN
----------------------------------------------------------------------------
 CTE Scan on locked  (cost=2356.37..2921.95 rows=5290 width=92)
   Filter: (amount > '400'::numeric)
   CTE locked
     ->  LockRows  (cost=0.00..2356.37 rows=25137 width=65)
           ->  Seq Scan on orders  (cost=0.00..2105.00 rows=25137 width=65)
                 Filter: (status = 'pending'::text)
(6 rows)

Không có FOR UPDATE , CTE này sẽ được nội tuyến. Nút LockRows và CTE Scan xác nhận hiện thực hóa.

Ma trận quyết định

Đây là hình ảnh đầy đủ về các phiên bản PostgreSQL:

Condition	PG ≤ 11	PG 12–16	PG 17–18
Single ref, pure SELECT	Materialized	Inlined	Inlined
Multiple refs, pure SELECT	Materialized	Materialized	Materialized (better stats)
VOLATILE function	Materialized	Materialized	Materialized
STABLE function	Materialized	Inlined	Inlined
Data-modifying (DML)	Materialized	Materialized	Materialized
FOR UPDATE / FOR SHARE	Materialized	Materialized	Materialized
Recursive	Materialized	Materialized	Materialized
Explicit `MATERIALIZED`	-	Materialized	Materialized
Explicit `NOT MATERIALIZED`	-	Inlined	Inlined

Hố đen thống kê

Như đã đề cập trong Thống kê PostgreSQL: Tại sao truy vấn chạy chậm , CTE được cụ thể hóa là một trong những nơi "không có số liệu thống kê". Đây được cho là vấn đề thực tế lớn nhất với việc hiện thực hóa CTE.

Khi trình lập kế hoạch thực hiện CTE, tập kết quả sẽ được lưu trữ trong một bộ dữ liệu tạm thời. Tuplestore này không có mục nhập pg_statistic - không có biểu đồ, không có MCV, không có dữ liệu tương quan. Người lập kế hoạch phải ước tính số lượng hàng và mức phân bổ giá trị bằng cách sử dụng các giá trị mặc định được mã hóa cứng.

Hãy xem thực tế điều này. Đây là CTE hơn 10.000 hàng:

EXPLAIN WITH all_orders AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM all_orders WHERE status = 'pending' AND amount > 400;

                              QUERY PLAN
-----------------------------------------------------------------------
 CTE Scan on all_orders  (cost=1855.00..4355.00 rows=5290 width=92)
   Filter: ((amount > '400'::numeric) AND (status = 'pending'::text))
   CTE all_orders
     ->  Seq Scan on orders  (cost=0.00..1855.00 rows=100000 width=59)
(4 rows)

Người lập kế hoạch ước tính có 5.290 hàng. Con số đó đến từ đâu? Công cụ lập kế hoạch không có danh sách MCV cho trạng thái bên trong CTE, không có biểu đồ cho số tiền. Nó quay trở lại độ chọn lọc mặc định là 0,3333 để so sánh phạm vi trên số tiền và dự đoán sơ bộ về sự bằng nhau trên trạng thái rồi nhân chúng với 100.000 hàng đầu vào.

Nếu CTE này được nội tuyến, người lập kế hoạch sẽ đọc số liệu thống kê thực tế từ pg_statistic cho bảng orders và đưa ra ước tính dựa trên phân phối dữ liệu thực chứ không phải mặc định.

Trong một truy vấn đơn giản, điều này có thể không quan trọng lắm. Nhưng khi CTE được cụ thể hóa đưa vào một liên kết, các ước tính dựa trên mặc định có thể xếp tầng. Người lập kế hoạch có thể chọn một vòng lặp lồng nhau trong đó phép nối băm sẽ tốt hơn hoặc ngược lại. Nó có thể đánh giá thấp nhu cầu bộ nhớ và tràn vào đĩa một cách bất ngờ.

PG 17: Tuyên truyền thống kê

PostgreSQL 17 mang lại hai cải tiến đáng kể cho CTE được cụ thể hóa:

Truyền thống kê cột. Khi người lập kế hoạch tạo Quét CTE nút, bây giờ nó truyền số liệu thống kê cột từ truy vấn cơ bản vào nút quét. Điều này có nghĩa là n_distinct, danh sách MCV và biểu đồ từ bảng nguồn có thể cung cấp thông tin ước tính khi quét CTE.

Truyền bá khóa đường dẫn. CTE được cụ thể hóa hiện lưu giữ thông tin thứ tự sắp xếp. Nếu truy vấn con của CTE tạo ra kết quả được sắp xếp, thì người lập kế hoạch sẽ biết về nó và có thể bỏ qua các loại dư thừa ở phía dưới.

Những cải tiến này làm giảm đáng kể khoảng cách ước tính nhưng không loại bỏ được khoảng cách đó. CTE nội tuyến vẫn thực sự tốt hơn cho độ chính xác của việc lập kế hoạch, bởi vì trình lập kế hoạch làm việc trực tiếp với số liệu thống kê của bảng cơ sở thay vì các bản sao được truyền bá. Nếu CTE của bạn không cần phải hiện thực hóa thì đừng ép buộc.

Khi việc vật chất hóa có ích

Việc vật chất hóa không phải lúc nào cũng xấu.

Nhiều tham chiếu. Nếu kết quả CTE được sử dụng ở nhiều nơi, quá trình cụ thể hóa sẽ tính toán kết quả đó một lần. Nếu không có nó, truy vấn con sẽ chạy một lần cho mỗi tham chiếu.

EXPLAIN WITH monthly_totals AS (
    SELECT date_trunc('month', created_at) AS month,
           status,
           sum(amount) AS total
    FROM orders
    GROUP BY 1, 2
)
SELECT cur.month, cur.status, cur.total,
       prev.total AS prev_month_total,
       cur.total - prev.total AS delta
FROM monthly_totals cur
LEFT JOIN monthly_totals prev
    ON cur.month = prev.month + interval '1 month'
    AND cur.status = prev.status;

                                                  QUERY PLAN
-------------------------------------------------------------------------------
 Merge Left Join  (cost=3887.46..3990.90 rows=4384 width=136)
   Merge Cond: ((cur.month = ((prev.month + '1 mon'::interval))) AND (cur.status = prev.status))
   CTE monthly_totals
     ->  HashAggregate  (cost=3105.00..3181.72 rows=4384 width=49)
           Group Key: date_trunc('month'::text, (orders.created_at)::timestamp with time zone), orders.status
           ->  Seq Scan on orders  (cost=0.00..2355.00 rows=100000 width=23)
   ->  Sort  (cost=352.87..363.83 rows=4384 width=72)
         Sort Key: cur.month, cur.status
         ->  CTE Scan on monthly_totals cur  (cost=0.00..87.68 rows=4384 width=72)
   ->  Sort  (cost=352.87..363.83 rows=4384 width=72)
         Sort Key: ((prev.month + '1 mon'::interval)), prev.status
         ->  CTE Scan on monthly_totals prev  (cost=0.00..87.68 rows=4384 width=72)
(12 rows)

Việc tổng hợp chạy một lần. Cả cur và prev đều đọc từ kết quả cụ thể hóa. Nếu không cụ thể hóa, toàn bộ tập hợp sẽ chạy hai lần.

Biểu thức VOLATILE đắt tiền. Nếu CTE chứa các lệnh gọi đến các hàm dễ thay đổi hoặc các phép tính tốn kém, thì việc cụ thể hóa sẽ đảm bảo chúng thực thi chính xác một lần.

Hoạt động sửa đổi dữ liệu. Điểm chung của CTE có thể ghi là tác dụng phụ xảy ra một lần và dữ liệu RETURNING có sẵn ở phía dưới. Việc vật chất hóa không phải là tùy chọn ở đây.

Khi nội tuyến là không đủ

Tư duy mệnh lệnh trong phần giới thiệu, “trước tiên hãy làm cái này, sau đó làm cái kia”, sẽ không biến mất chỉ vì người lập kế hoạch đưa ra CTE của bạn. Và đây là nguồn yêu thích của cá nhân tôi và là nguồn không ngừng cung cấp khả năng tái cấu trúc truy vấn.

Các nhà phát triển vẫn cấu trúc các truy vấn dưới dạng các quy trình tuần tự và bản thân cấu trúc đó có thể tạo ra các vấn đề về hiệu suất mà không liên quan gì đến việc cụ thể hóa.

Một mẫu phổ biến là xây dựng các truy vấn dưới dạng một dây chuyền tập hợp: một CTE lọc các hàng, các bảng liên quan đến LEFT JOINs tiếp theo và tổng hợp siêu dữ liệu với GROUP BY, các bộ lọc tiếp theo trên kết quả tổng hợp. Nó giống như một đường dẫn rõ ràng, nhưng GROUP BY ở giữa tạo ra một bức tường mà người lập kế hoạch không thể tối ưu hóa trước đó.

WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at > '2024-01-01'
),
order_metadata AS (
    SELECT
        o.id,
        bool_or(oa.id IS NOT NULL) AS was_archived,
        count(o2.id) AS related_count
    FROM recent_orders o
    LEFT JOIN orders_archive oa ON o.id = oa.id
    LEFT JOIN orders o2 ON o.customer_id = o2.customer_id AND o2.id != o.id
    GROUP BY o.id
)
SELECT o.*, m.was_archived, m.related_count
FROM recent_orders o
JOIN order_metadata m ON o.id = m.id
WHERE m.was_archived = false
  AND m.related_count > 0;

Mỗi CTE ở đây được tham chiếu một lần nên tất cả đều được đưa vào cùng dòng. Không hiện thực hóa, không có hàng rào tối ưu hóa. Người lập kế hoạch nhìn thấy truy vấn đầy đủ. Vậy vấn đề là gì?

NHÓM THEO trong order_metadata. Ngay cả sau khi nội tuyến, trình lập kế hoạch không thể đẩy vị từ was_archived = false qua tập hợp. Trước tiên, nó phải TRÁI THAM GIA mọi đơn hàng được lọc theo orders_archive và orders tự tham gia, tính toán tổng hợp cho tất cả chúng và chỉ sau đó loại bỏ các hàng không khớp. Nếu recent_orders trả về 50.000 hàng nhưng chỉ có 200 hàng được lưu trữ thì bạn đang nối và tổng hợp 49.800 hàng mà chẳng thu được gì.

Cách khắc phục là thay thế mẫu tổng hợp rồi lọc bằng các truy vấn con EXISTS tương quan:

SELECT o.*
FROM orders o
WHERE o.created_at > '2024-01-01'
  AND NOT EXISTS (
    SELECT 1 FROM orders_archive oa WHERE oa.id = o.id
  )
  AND EXISTS (
    SELECT 1 FROM orders o2
    WHERE o2.customer_id = o.customer_id AND o2.id != o.id
  );

Tồn tại đoản mạch sau khi tìm thấy hàng phù hợp đầu tiên. Trình lập kế hoạch có thể đẩy created_at > '2024-01-01' xuống mức quét chỉ mục trên đơn hàng, sau đó thăm dò từng bảng liên quan cho mỗi kết quả. Không tổng hợp, không lãng phí công việc.

Quy tắc ngón tay cái: nếu CTE của bạn chứa GROUP BY hoặc LEFT JOIN chỉ để tính toán boolean ("hàng này có dữ liệu liên quan không?"), thì bạn đã xây một bức tường mà người lập kế hoạch không thể nhìn thấy. EXISTS tương quan cho phép người lập kế hoạch đẩy các bộ lọc xuống và ngừng quét sớm. Điều này áp dụng cho dù CTE được cụ thể hóa hay nội tuyến.

CTE có thể ghi (sức mạnh và bẫy)

CTE sửa đổi dữ liệu cho phép bạn INSERT, UPDATE hoặc DELETE bên trong mệnh đề WITH và sử dụng RETURNING dữ liệu trong CTE tiếp theo hoặc truy vấn chính.

EXPLAIN WITH deleted AS (
    DELETE FROM orders
    WHERE status = 'cancelled'
      AND created_at < '2023-01-01'
    RETURNING *
),
archived AS (
    INSERT INTO orders_archive
    SELECT * FROM deleted
    RETURNING id
)
SELECT count(*) FROM archived;

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Aggregate  (cost=2709.19..2709.20 rows=1 width=8)
   CTE deleted
     ->  Delete on orders  (cost=0.00..2355.00 rows=8334 width=6)
           ->  Seq Scan on orders  (cost=0.00..2355.00 rows=8334 width=6)
                 Filter: ((created_at < '2023-01-01'::date) AND (status = 'cancelled'::text))
   CTE archived
     ->  Insert on orders_archive  (cost=0.00..166.68 rows=8334 width=92)
           ->  CTE Scan on deleted  (cost=0.00..166.68 rows=8334 width=92)
   ->  CTE Scan on archived  (cost=0.00..166.68 rows=8334 width=0)
(9 rows)

Thao tác này sẽ xóa các đơn đặt hàng cũ đã bị hủy, chuyển chúng đến bảng lưu trữ và đếm số lượng đã được lưu trữ - tất cả chỉ trong một câu lệnh nguyên tử duy nhất, không cần phối hợp ở cấp ứng dụng.

Nhưng có những cạnh sắc nét.

Bạn không thể đọc những gì bạn vừa viết

Tất cả các câu lệnh phụ trong CTE sửa đổi dữ liệu đều có cùng một ảnh chụp nhanh. Điều này có nghĩa là tác động của một CTE không hiển thị đối với các CTE khác hoặc truy vấn chính khi chúng đọc bảng mục tiêu. Chỉ mệnh đề RETURNING mới truyền dữ liệu giữa các bước CTE.

SELECT count(1) FROM orders WHERE customer_id = 1;

 count
-------
    31
(1 row)

WITH ins AS (
    INSERT INTO orders (customer_id, amount, status, created_at)
    VALUES (1, 100.00, 'pending', CURRENT_DATE)
    RETURNING id
)
-- this does NOT see the row we just inserted
SELECT count(1) FROM orders WHERE customer_id = 1;

 count
-------
    31
(1 row)

Truy vấn count(1) xem ảnh chụp nhanh được chèn trước. Nếu cần dữ liệu được chèn vào thì phải sử dụng mệnh đề RETURNING từ ins CTE chứ không được đọc lại bảng.

Xáo trộn bộ dữ liệu

Mẫu phổ biến là sử dụng CTE có thể ghi để di chuyển các hàng giữa các bảng một cách nguyên tử:

WITH moved AS (
    DELETE FROM orders_staging
    RETURNING *
)
INSERT INTO orders
SELECT * FROM moved;

Thao tác này sẽ xóa tất cả các hàng khỏi bảng phân tầng và chèn chúng vào bảng sản xuất trong một thao tác đơn lẻ. Không có cửa sổ nào chứa dữ liệu ở cả hai hoặc không có bảng nào.

CTE sửa đổi dữ liệu vô hiệu hóa truy vấn song song cho toàn bộ câu lệnh. Nếu bạn có một truy vấn phức tạp kết hợp đọc và ghi, CTE ghi sẽ ngăn chặn sự song song ngay cả đối với các phần chỉ đọc.

CTE đệ quy luôn được cụ thể hóa

CTE đệ quy sử dụng cơ chế bàn làm việc lặp lại. Mặc dù có tên như vậy nhưng chúng không thực sự đệ quy. PostgreSQL không "tự gọi chính nó" bằng cách tạo ra một chồng các truy vấn chưa hoàn thành lồng nhau. Thay vào đó, nó hoạt động theo vòng lặp.

Thực hiện thuật ngữ không đệ quy ("hạt giống"). Đưa kết quả vào bàn làm việc.
Thực hiện thuật ngữ đệ quy bằng cách sử dụng bàn làm việc làm đầu vào. Các hàng mới trở thành bàn làm việc tiếp theo.
Lặp lại cho đến khi thuật ngữ đệ quy không trả về hàng mới.
Trả về kết hợp của tất cả các lần lặp.

WITH RECURSIVE org_chart AS (
    -- Seed: start from the CEO
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive term: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth, name;

 id |  name   | manager_id | depth
----+---------+------------+-------
  1 | Alice   |            |     1
  2 | Bob     |          1 |     2
  3 | Charlie |          1 |     2
  4 | Diana   |          2 |     3
  5 | Eve     |          2 |     3
  6 | Frank   |          3 |     3
  7 | Grace   |          3 |     3
  8 | Hank    |          6 |     4
  9 | Ivy     |          6 |     4
(9 rows)

UNION vs UNION ALL

Sự lựa chọn giữa UNION và UNION ALL trong CTE đệ quy quan trọng hơn trong các truy vấn thông thường.

UNION ALL giữ tất cả các hàng, kể cả các hàng trùng lặp. Điều này nhanh hơn nhưng nguy hiểm: nếu đồ thị của bạn có chu kỳ, đệ quy sẽ không bao giờ kết thúc. PostgreSQL sẽ chạy cho đến khi bạn hủy truy vấn hoặc hết bộ nhớ.

UNION loại bỏ trùng lặp ở mỗi lần lặp. Điều này ngăn chặn các vòng lặp vô hạn trong biểu đồ tuần hoàn nhưng làm tăng thêm chi phí băm và so sánh các hàng ở mỗi bước.

PostgreSQL 14 đã thêm các mệnh đề SEARCH và CYCLE tiêu chuẩn SQL thay thế các mẫu thủ công để kiểm soát thứ tự truyền tải (theo chiều rộng trước so với theo chiều sâu) và phát hiện chu kỳ. SEARCH BREADTH FIRST BY / SEARCH DEPTH FIRST BY kiểm soát thứ tự, trong khi CYCLE tự động phát hiện và đánh dấu các chu kỳ, rõ ràng hơn nhiều so với mô hình cũ là tích lũy một mảng ID đã truy cập.

Đối với dữ liệu phân cấp thuần túy (cây không có chu trình), hãy xem xét tiện ích mở rộng ltree thay thế cho CTE đệ quy. Nó lưu trữ đường dẫn đầy đủ dưới dạng cây nhãn và hỗ trợ các truy vấn tổ tiên/con cháu hiệu quả với các chỉ mục GiST. Sự đánh đổi là lưu trữ không chuẩn hóa so với lưu trữ không chuẩn hóa. đệ quy nhanh chóng.

Các trường hợp cạnh kỳ lạ

Mất việc cắt tỉa phân vùng

Khi bạn cụ thể hóa CTE trên một bảng được phân vùng, việc cắt bớt phân vùng không thể xảy ra ở phía quét CTE. Kết quả cụ thể hóa là một tuplestore phẳng bị ngắt kết nối khỏi siêu dữ liệu phân vùng.

-- assume orders is range-partitioned by created_at
WITH recent AS MATERIALIZED (
    SELECT * FROM orders
)
SELECT * FROM recent WHERE created_at > '2025-06-01';

Vị ngữ created_at > '2025-06-01' được áp dụng sau khi hiện thực hóa. Tất cả các phân vùng đều được quét để xây dựng CTE, mặc dù chỉ cần một hoặc hai phân vùng. Sử dụng NOT MATERIALIZED (hoặc đơn giản là để người lập kế hoạch nội tuyến) để duy trì việc cắt bớt phân vùng.

Các câu lệnh đã chuẩn bị và kế hoạch lưu vào bộ nhớ đệm

PostgreSQL tạo các kế hoạch tùy chỉnh cho 5 lần thực thi đầu tiên của câu lệnh đã chuẩn bị sẵn. Sau đó, nó có thể chuyển sang một kế hoạch chung. Các quyết định nội tuyến CTE có thể khác nhau giữa gói tùy chỉnh và gói chung vì gói chung không biết giá trị thông số thực tế.

Điều này có nghĩa là CTE được thực hiện trong 5 cuộc gọi đầu tiên của bạn có thể bắt đầu thực hiện vào ngày thứ 6 hoặc ngược lại. Nếu bạn thấy kế hoạch thay đổi đột ngột với các câu lệnh đã chuẩn bị sẵn, hãy kiểm tra xem hành vi nội tuyến CTE có thay đổi hay không.

work_mem tràn

CTE được vật chất hóa lưu trữ kết quả của chúng trong bộ nhớ, được giới hạn bởi work_mem. Khi tập kết quả vượt quá giới hạn này, nó sẽ âm thầm tràn vào đĩa dưới dạng tệp tạm thời. Đây không phải là lỗi - nó chỉ chậm hơn thôi.

Giám sát bằng log_temp_files = 0 (ghi lại tất cả các tệp tạm thời) hoặc kiểm tra EXPLAIN (ANALYZE, BUFFERS) để biết số lần đọc/ghi tạm thời.

PostgreSQL 18: EXPLAIN hiện hiển thị mức sử dụng bộ nhớ/đĩa
Bắt đầu với PostgreSQL 18, EXPLAIN ANALYZE báo cáo mức sử dụng bộ nhớ và ổ đĩa cho các nút Vật liệu, bao gồm cả việc cụ thể hóa CTE. Bạn có thể biết chính xác lượng bộ nhớ mà CTE cụ thể hóa đã tiêu thụ và liệu nó có tràn vào đĩa hay không.

CTE và chế độ xem rào cản bảo mật

Chế độ xem `security_barrier` là một "hộp đen" buộc cơ sở dữ liệu phải giải quyết hoàn toàn logic bên trong của chế độ xem trước khi áp dụng bất kỳ bộ lọc bên ngoài nào.

Chế độ xem rào cản bảo mật đã ngăn chặn việc làm phẳng truy vấn phụ như một biện pháp bảo mật (để ngăn các hàm do người dùng xác định nhìn thấy các hàng mà lẽ ra chúng không nên nhìn thấy). Khi bạn kết hợp chế độ xem rào cản bảo mật với CTE, bạn sẽ kết hợp các rào cản tối ưu hóa. Người lập kế hoạch không thể nội tuyến chế độ xem cũng như CTE. Nếu hiệu suất có vấn đề trong trường hợp này, trước tiên hãy xem xét cụ thể hóa bộ lọc nhạy cảm bảo mật vào bảng tạm thời.

CTE so với truy vấn con so với bảng tạm thời

CTE, được tham chiếu một lần người lập kế hoạch sẽ đưa nó vào PG 12+ nên không ảnh hưởng đến kế hoạch thực hiện. Đây là lựa chọn mặc định để chia nhỏ các truy vấn phức tạp.

CTE, được tham chiếu nhiều lần (kết quả nhỏ) thể hiện chi phí có thể chấp nhận được. Vật chất hóa có nghĩa là truy vấn con chạy một lần. Đối với các tập hợp tổng hợp hoặc tập hợp con được lọc tạo ra vài trăm hàng thì chi phí chung là tối thiểu.

Như Henrietta Dombrovskaya nhấn mạnh: "Bảng tạm thời tốt nhất là bảng bạn không tạo". Luôn sử dụng hết các tùy chọn lập chỉ mục và viết lại truy vấn trước khi sử dụng bảng tạm thời, vì chi phí DDL thường lớn hơn lợi ích thực thi.

**CTE, được tham chiếu nhiều lần (kết quả lớn)** trường hợp ss khi bạn có thể xem xét một bảng tạm thời thay thế. CTE được cụ thể hóa không có chỉ mục và không có số liệu thống kê. Một bảng tạm thời có thể có cả hai, và như được đề cập trong [Giới thiệu về bộ đệm](/posts/introduction-to-buffers/), các bảng tạm thời sử dụng bộ đệm cục bộ với khóa đơn giản hơn và không có chi phí WAL. Nếu bạn đang kết hợp hơn 100 nghìn hàng từ CTE, hãy tạo bảng tạm thời, thêm chỉ mục và `PHÂN TÍCH` nó.

Hoạt động sửa đổi dữ liệu với CTE có thể ghi. Không có giải pháp thay thế nào cung cấp cho bạn hành vi đơn lẻ, nguyên tử.

CTE đệ quy. Không có sự thay thế nào trong SQL thuần túy.

Các kết quả trung gian lớn cần chỉ mục/thống kê. Nếu bạn thực sự cần chúng, hãy sử dụng bảng tạm thời. Như đã thảo luận trong Đọc số liệu thống kê về bộ đệm, các bảng tạm thời cung cấp hỗ trợ lập kế hoạch đầy đủ - chỉ mục, số liệu thống kê và quản lý bộ đệm - mà CTE cụ thể hóa không có.

Kịch bản	Đề xuất
Khả năng đọc, tham chiếu đơn	CTE (nội tuyến, miễn phí)
Tính một lần, dùng nhiều lần (nhỏ)	CTE (materialized)
Tính một lần, sử dụng nhiều lần (lớn)	Bảng tạm thời
Sửa đổi dữ liệu nguyên tử	CTE có thể ghi
Truyền tải đồ thị/phân cấp	CTE đệ quy
Cần lập chỉ mục trên dữ liệu trung gian	Bảng tạm thời

Tình hình PG 18

PostgreSQL 18 tiếp tục cải tiến việc xử lý CTE mà không có bất kỳ thay đổi mang tính cách mạng nào:

EXPLAIN hiển thị mức sử dụng bộ nhớ/đĩa cho các nút hiện thực hóa CTE. Cuối cùng, bạn có thể xem liệu CTE của mình có vừa với work_mem hay bị tràn vào đĩa hay không.
Kế hoạch truy vấn tốt hơn cho các CTE trên cùng một bảng. Trình lập kế hoạch thông minh hơn trong việc loại bỏ các lần quét dư thừa khi nhiều CTE tham chiếu đến cùng một bảng cơ bản.
Bản sửa lỗi CTE sửa đổi dữ liệu cho các chế độ xem có thể cập nhật bằng quy tắc. Một trường hợp đặc biệt trong đó các CTE có thể ghi tương tác không chính xác với các chế độ xem được xác định bằng quy tắc đã được giải quyết.
Nội tuyến CTE đã hoàn thiện. Logic nội tuyến cốt lõi không thay đổi kể từ PG 12. Điều đã được cải thiện là mọi thứ xung quanh nó - truyền bá số liệu thống kê tốt hơn (PG 17), chẩn đoán cụ thể hóa tốt hơn (PG 18), ước tính chi phí tốt hơn.

CTE là một công cụ tốt. Chỉ cần biết khi nào bạn đang cầm đầu nhọn.