Tối ưu hóa truy vấn với CTE

Hôm nay tụi mình sẽ lặn sâu vào thế giới (vừa thú vị vừa hơi đáng sợ) của tối ưu hóa truy vấn với CTE (Common Table Expressions). Nếu bạn đã biết cách tạo CTE (tụi mình đã nói về nó ở các bài trước), thì giờ là lúc bàn về những chi tiết bên trong, các “bẫy ngầm” và cách vắt kiệt hiệu suất từ nó.

Nhìn sơ qua thì CTE có vẻ hoàn hảo: code gọn, dễ viết, chia nhỏ logic rõ ràng. Nhưng có một điểm nhỏ (hoặc không nhỏ lắm) cần chú ý. PostgreSQL có chiến lược riêng khi xử lý CTE, và nó ảnh hưởng đến hiệu năng.

Khi PostgreSQL thấy WITH, nó thường sẽ materialize kết quả của CTE. Nghĩa là dữ liệu trả về từ CTE sẽ được tính toán và lưu lại thành bảng tạm, sau đó mới dùng trong truy vấn chính. Cách này tiện nếu cần dùng lại nhiều lần, nhưng có thể thành vấn đề nếu:

1. Dữ liệu trong CTE quá nhiều, mà kết quả chỉ dùng một phần nhỏ.
2. CTE bị gọi quá nhiều lần, làm tăng overhead.
3. Mình tạo ra CTE phức tạp không cần thiết.

Làm quen với materialization

Materialization — là quá trình PostgreSQL lưu kết quả CTE vào RAM hoặc ổ đĩa (tùy kích thước dữ liệu). Nghĩa là dữ liệu chỉ được lấy một lần, nhưng nếu bạn chỉ dùng CTE ở một chỗ, thì materialization có thể hơi thừa. Ví dụ:

WITH large_set AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Trong trường hợp này, PostgreSQL sẽ tạo bảng tạm với toàn bộ kết quả CTE (grade > 60), rồi mới lọc tiếp các dòng grade > 90. Như vậy sẽ có bước trung gian không cần thiết và ảnh hưởng đến hiệu năng.

Làm sao tránh materialization thừa?

Từ PostgreSQL 12, bạn có thể tránh materialization CTE khi không cần thiết. Dùng từ khóa MATERIALIZED (mặc định) hoặc NOT MATERIALIZED. Ví dụ:

WITH large_set AS NOT MATERIALIZED (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)
SELECT student_id, grade
FROM large_set
WHERE grade > 90;

Ở đây mình bảo PostgreSQL đừng materialize dữ liệu large_set, mà nhúng truy vấn thẳng vào biểu thức chính. Như vậy truy vấn chạy nhanh hơn vì không tạo bảng tạm.

Khi nào materialization lại có ích?

Đừng nghĩ materialization lúc nào cũng xấu nha! Nếu dữ liệu CTE được dùng nhiều lần trong truy vấn hoặc cần tính toán độc lập, materialization lại có ích. Ví dụ:

WITH materialized_example AS (
    SELECT *
    FROM students_grades
    WHERE grade > 60
)

SELECT student_id
FROM materialized_example
WHERE grade > 90

UNION ALL

SELECT student_id
FROM materialized_example
WHERE grade < 70;

Ở đây materialization giúp tránh phải tính lại filter grade > 60 nhiều lần.

Tối ưu hóa truy vấn với index

Để CTE chạy nhanh hơn, nên dùng index trên các bảng gốc mà bạn lấy dữ liệu. Ví dụ:

CREATE INDEX idx_students_grades_grade ON students_grades(grade);

WITH filtered_students AS (
    SELECT student_id, grade
    FROM students_grades
    WHERE grade > 90
)
SELECT *
FROM filtered_students;

Index trên cột grade giúp PostgreSQL lấy dòng phù hợp với grade > 90 nhanh hơn. Điều này cực kỳ quan trọng khi làm việc với bảng lớn.

Chia nhỏ CTE lớn thành nhiều phần nhỏ

Nếu CTE trả về nhiều dữ liệu rồi lại bị filter hoặc aggregate, tốt nhất nên chia nhỏ thành nhiều bước. Thay vì một CTE phức tạp, hãy tạo vài cái nhỏ:

Không nên (CTE to đùng):

WITH large_query AS (
    SELECT s.student_id, AVG(g.grade) AS avg_grade
    FROM students s
    JOIN grades g ON s.student_id = g.student_id
    WHERE g.subject_id = 101 AND g.grade > 85
    GROUP BY s.student_id
)
SELECT *
FROM large_query
WHERE avg_grade > 90;

Nên làm (chia nhỏ từng bước):

WITH filtered_grades AS (
    SELECT student_id, grade
    FROM grades
    WHERE subject_id = 101 AND grade > 85
),
average_grades AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM filtered_grades
    GROUP BY student_id
)
SELECT *
FROM average_grades
WHERE avg_grade > 90;

Cách này giúp PostgreSQL tối ưu hóa truy vấn tốt hơn.

Ví dụ thực tế: phân tích cấu trúc và tối ưu hóa

Cùng xem ví dụ phức tạp hơn nhé. Giả sử có bảng students, courses và grades. Mình muốn tìm sinh viên có điểm trung bình cao và liệt kê cùng các khóa học tương ứng:

WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
),
student_courses AS (
    SELECT e.student_id, c.course_name
    FROM enrollments e
    JOIN courses c ON e.course_id = c.course_id
)
SELECT ha.student_id, ha.avg_grade, sc.course_name
FROM high_achievers ha
JOIN student_courses sc ON ha.student_id = sc.student_id;

Truy vấn này sẽ tối ưu hơn nếu bạn thêm index cho bảng grades và enrollments, giúp filter và join nhanh hơn.

Monitoring: phân tích hiệu năng

Để biết truy vấn hiệu quả tới đâu, hãy dùng EXPLAIN hoặc EXPLAIN ANALYZE. Ví dụ:

EXPLAIN ANALYZE
WITH high_achievers AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
    HAVING AVG(grade) > 90
)
SELECT *
FROM high_achievers;

Truy vấn này sẽ cho bạn biết mỗi bước tốn bao nhiêu thời gian, từ đó biết chỗ nào cần tối ưu thêm.

Chi tiết về EXPLAIN ANALYZE tụi mình sẽ bàn ở các level sau nha :P

Lỗi thường gặp khi tối ưu hóa CTE

1. Quên tạo index. Nếu filter dữ liệu trong CTE mà bảng gốc không có index, hiệu năng sẽ tệ.
2. Dùng CTE quá to. Nếu một truy vấn làm quá nhiều thứ, có thể dẫn đến materialization dữ liệu lớn.
3. Lạm dụng NOT MATERIALIZED. Có lúc materialization là cần thiết để tránh phải chạy lại CTE nhiều lần.
4. Bỏ qua monitoring. Không dùng EXPLAIN thì có thể không biết truy vấn đang chạy chậm.

Giờ thì bạn đã sẵn sàng tối ưu hóa truy vấn với CTE, tránh bẫy và tăng hiệu năng rồi đó! Nhớ rằng CTE chỉ là công cụ, không phải thần dược. Dùng hợp lý, nó sẽ là bạn thân trong PostgreSQL.