PostgreSQL Bài 15: Subqueries và CTEs (WITH query): Phân biệt Materialized vs Non-Materialized CTE

Khi xử lý các logic truy vấn phức tạp hoặc báo cáo phân tích đa tầng, hầu hết lập trình viên thường đứng trước hai lựa chọn: viết các truy vấn con lồng nhau (Subqueries) hoặc dùng mệnh đề CTE (Common Table Expression - cú pháp WITH ... AS).

Tuy nhiên, đằng sau vẻ ngoài gọn gàng, dễ đọc của CTE là một câu chuyện dài về cơ chế tối ưu hóa truy vấn của PostgreSQL:

• Tại sao một câu lệnh CTE viết rất đẹp lại chạy chậm hơn gấp hàng chục lần so với viết Subquery lồng nhau trên các phiên bản cũ?

• Bước ngoặt lớn từ phiên bản PostgreSQL 12 đã thay đổi hành vi mặc định của CTE ra sao?

• Làm thế nào để chủ động điều khiển Planner bằng hai từ khóa: AS MATERIALIZED và AS NOT MATERIALIZED?

Bài học này sẽ phân tích chi tiết cơ chế nội tại của Subqueries, CTEs và cách kiểm soát chúng trong thực tế.

1. Subqueries: Phân loại và Cách Planner tối ưu

Truy vấn con (Subquery) là một câu lệnh SELECT nằm lồng bên trong một câu lệnh SQL khác (nằm ở mệnh đề FROM, WHERE, SELECT, hoặc HAVING).

1.1. Subquery độc lập (Non-correlated Subquery) vs Subquery tương quan (Correlated Subquery)

• Non-correlated Subquery: Câu truy vấn con hoàn toàn độc lập, không tham chiếu đến bất kỳ cột nào của bảng bên ngoài. Engine chỉ cần thực thi nó đúng một lần, lấy kết quả dùng chung cho toàn bộ câu truy vấn cha.

  SQL

  -- Chạy subquery 1 lần duy nhất để lấy max_price:
  SELECT id, title, price 
  FROM join_lab.products 
  WHERE price = (SELECT MAX(price) FROM join_lab.products);
  
  

• Correlated Subquery: Câu truy vấn con tham chiếu trực tiếp đến dữ liệu của từng dòng từ bảng cha (Outer Query).

  SQL

  -- Với mỗi sản phẩm ở bảng ngoài (p1), subquery phải chạy lại để tính mức giá trung bình của danh mục đó:
  SELECT p1.id, p1.title, p1.price, p1.category_id
  FROM join_lab.products p1
  WHERE p1.price > (
      SELECT AVG(p2.price) 
      FROM join_lab.products p2 
      WHERE p2.category_id = p1.category_id
  );
  
  

  Bẫy hiệu năng: Nếu bảng cha có 1.000.000 dòng, subquery tương quan có thể bị ép chạy lặp lại 1.000.000 lần (tương đương với một phép Nested Loop không có kiểm soát).

1.2. Kỹ thuật Subquery Flattening (Kéo phẳng Subquery)

Query Planner của PostgreSQL rất thông minh. Khi bạn viết một Subquery ở mệnh đề WHERE kết hợp với IN hoặc EXISTS, Planner thường không giữ nguyên cấu trúc đó mà sẽ tìm cách kéo phẳng (flatten / pull-up) nó thành một phép JOIN vật lý:

• WHERE id IN (SELECT user_id FROM orders) thường được Planner tự động chuyển thành Semi-Join (bằng Hash Join hoặc Merge Join).

• WHERE NOT EXISTS (...) thường được tự động biến đổi thành Anti-Join.

Điều này giúp Subquery có cơ hội tận dụng toàn bộ sức mạnh tối ưu hóa phép JOIN đã học ở Bài 14.

2. Common Table Expressions (CTE): Cú pháp và Bản chất

CTE (mệnh đề WITH) cho phép bạn đặt tên cho một tập kết quả tạm thời để tái sử dụng hoặc phân rã một câu truy vấn lớn thành nhiều bước logic tuần tự, dễ bảo trì:

SQL

WITH high_value_orders AS (
    SELECT customer_id, SUM(total_amount) AS spent
    FROM sales.orders
    GROUP BY customer_id
    HAVING SUM(total_amount) > 10000
),
vip_customers AS (
    SELECT id, email 
    FROM sales.customers 
    WHERE status = 'ACTIVE'
)
SELECT v.email, h.spent
FROM vip_customers v
JOIN high_value_orders h ON v.id = h.customer_id
ORDER BY h.spent DESC;

CTE giúp code sáng sủa hơn hẳn việc lồng 4-5 tầng Subquery vào nhau. Nhưng câu hỏi đặt ra: PostgreSQL thực sự thực thi khối WITH này như thế nào?

3. Bước ngoặt PostgreSQL 12: Sự tiến hóa của CTE

Trước phiên bản PostgreSQL 12 và từ PostgreSQL 12 trở đi là hai thế giới hoàn toàn khác nhau về hiệu năng của CTE.

PostgreSQL 11 trở về trước:
CTE = HÀNG RÀO TỐI ƯU (Optimization Fence)
Engine luôn ép: Thực thi CTE độc lập ──► Ghi toàn bộ kết quả vào bộ nhớ tạm (Materialize) ──► Chạy câu query chính

PostgreSQL 12 trở về sau:
CTE = INLINE (Mặc định nếu CTE không có side-effects và chỉ dùng 1 lần)
Planner tự động "hòa tan" CTE vào câu query chính (giống như Subquery) ──► Đẩy điều kiện lọc WHERE xuống sâu nhất (Predicate Pushdown)

4. Phân tích chuyên sâu: Materialized vs Non-Materialized CTE

4.1. Non-Materialized CTE (Inlined CTE)

Khi một CTE là Non-Materialized (không vật chất hóa), PostgreSQL không tạo ra bất kỳ bảng tạm hay vùng nhớ đệm nào cho nó. Planner sẽ coi CTE tương đương với một Subquery và thực hiện kỹ thuật Predicate Pushdown (đẩy điều kiện lọc xuống).

Hãy quan sát ví dụ sau:

SQL

WITH all_products AS (
    SELECT id, category_id, title, price 
    FROM join_lab.products
)
SELECT * 
FROM all_products 
WHERE id = 500;

• Cơ chế: Dù khối CTE all_products viết là lấy toàn bộ 100.000 dòng từ bảng products, Planner sẽ nhận diện được điều kiện WHERE id = 500 ở câu lệnh ngoài cùng.

• Tối ưu: Nó "hòa tan" CTE và đẩy thẳng điều kiện id = 500 vào bảng gốc, kích hoạt Index Scan trên khóa chính products_pkey.

• Kết quả: Truy vấn chỉ tốn đúng 1 phép đọc index (~0.05 ms).

4.2. Materialized CTE (Vật chất hóa)

Khi một CTE bị Materialized:

1. Engine sẽ thực thi toàn bộ nội dung của CTE một cách độc lập và cô lập hoàn toàn.

2. Toàn bộ tập kết quả được ghi vào một bảng tạm trong bộ nhớ (hoặc tràn ra đĩa nếu vượt quá work_mem).

3. Mất khả năng Predicate Pushdown: Câu lệnh bên ngoài dù có WHERE id = 500 thì CTE bên trong vẫn phải quét toàn bộ bảng và gom toàn bộ dữ liệu trước!

4. Các Index của bảng gốc hoàn toàn vô dụng đối với tập kết quả trung gian này (vì bảng tạm không có index).

Thử nghiệm ép vật chất hóa:

SQL

-- Ép PostgreSQL phải Materialize bằng từ khóa tường minh:
WITH all_products AS MATERIALIZED (
    SELECT id, category_id, title, price 
    FROM join_lab.products
)
SELECT * 
FROM all_products 
WHERE id = 500;

Kế hoạch thực thi (EXPLAIN ANALYZE):

Plaintext

CTE Scan on all_products  (cost=1845.00..2095.00 rows=500 width=45) (actual time=14.250..18.600 rows=1 loops=1)
  Filter: (id = 500)
  Rows Removed by Filter: 99999
  CTE all_products
    ->  Seq Scan on products  (cost=0.00..1845.00 rows=100000 width=35) (actual time=0.015..8.500 rows=100000 loops=1)

Nhận xét kết quả:

• Engine phải Seq Scan đọc hết 100.000 dòng của bảng products vào CTE.

• Sau đó, nó thực hiện CTE Scan trên bảng tạm và duyệt qua từng dòng để lọc lấy dòng có id = 500 (loại bỏ 99.999 dòng thừa: Rows Removed by Filter: 99999).

• Thời gian chạy tăng từ 0.05 ms lên gần 19 ms (chậm hơn gần 400 lần!). Đây chính là "cơn ác mộng" tối ưu hóa mà các lập trình viên trên PostgreSQL 11 trở về trước thường xuyên gặp phải.

5. Khi nào nên dùng MATERIALIZED? Khi nào dùng NOT MATERIALIZED?

Từ PostgreSQL 12+, bạn có thể chủ động kiểm soát hành vi này bằng cú pháp:

SQL

WITH cte_name AS MATERIALIZED (...)      -- Bắt buộc vật chất hóa
WITH cte_name AS NOT MATERIALIZED (...)  -- Bắt buộc inline

Dù Non-Materialized (Inline) thường tối ưu hơn, vẫn có những kịch bản thực tế bắt buộc bạn phải dùng MATERIALIZED.

5.1. Khi nào nên dùng MATERIALIZED?

1. CTE được tham chiếu nhiều lần (Multiple References): Nếu một khối CTE tốn nhiều chi phí tính toán (ví dụ: aggregate dữ liệu phức tạp) và được JOIN nhiều lần trong câu lệnh chính:

   SQL

   WITH monthly_agg AS MATERIALIZED (
       -- Phép tính toán nặng tốn 2 giây
       SELECT department_id, AVG(salary) as avg_sal, SUM(bonus) as sum_bonus
       FROM employees
       GROUP BY department_id
   )
   SELECT * FROM monthly_agg m1 
   JOIN monthly_agg m2 ON m1.department_id = m2.department_id;
   
   

   Nếu để NOT MATERIALIZED, Planner có thể tính toán lại toàn bộ khối monthly_agg 2 lần. Bằng cách dùng MATERIALIZED, kết quả chỉ được tính đúng 1 lần duy nhất rồi tái sử dụng.

2. Cần dựng "Hàng rào ngăn cách" (Optimization Fence) cho các hàm có rủi ro lỗi: Khi bạn muốn đảm bảo một tập dữ liệu phải được lọc sạch trước khi gọi một hàm dễ ném Exception:

   SQL

   WITH clean_data AS MATERIALIZED (
       SELECT str_value 
       FROM raw_inputs 
       WHERE str_value ~ '^[0-9]+$' -- Chỉ lấy chuỗi hoàn toàn là số
   )
   SELECT str_value::INTEGER -- Ép kiểu sang số nguyên an toàn
   FROM clean_data;
   
   

   Nếu không dùng MATERIALIZED, Planner có thể đẩy phép ép kiểu ::INTEGER xuống trước điều kiện Regex lọc chuỗi, dẫn đến việc chương trình bị ném lỗi sập truy vấn do gặp chuỗi không phải số.

5.2. Khi nào nên dùng NOT MATERIALIZED?

• Khi CTE chỉ được tham chiếu đúng một lần trong câu query chính.

• Khi câu query chính có các điều kiện lọc WHERE hoặc LIMIT chặt chẽ mà bạn muốn đẩy ngược vào trong CTE để tận dụng Index của các bảng nguồn.

6. Bảng so sánh tổng kết