Phân tích hiệu suất function và procedure: dùng EXPLAIN ANALYZE

EXPLAIN ANALYZE giúp tụi mình hiểu PostgreSQL "nghĩ gì" khi chạy truy vấn của bạn:

• Những bước nào được thực hiện để xử lý dữ liệu.
• Mỗi bước tốn bao nhiêu thời gian.
• Tại sao truy vấn lại chậm — có phải do quét toàn bộ bảng (tiếng Anh là Seq Scan) hay thiếu index không.

Lệnh EXPLAIN ANALYZE thực sự chạy truy vấn và cho bạn thấy PostgreSQL tối ưu hóa việc thực thi như thế nào. Hãy tưởng tượng bạn tháo đồng hồ ra để xem cơ chế bên trong hoạt động ra sao. EXPLAIN ANALYZE cũng làm vậy, chỉ là với truy vấn SQL của bạn thôi.

Cú pháp EXPLAIN ANALYZE

Bắt đầu đơn giản nhé. Đây là dạng cơ bản của lệnh:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Lệnh này sẽ chạy SELECT và cho bạn thấy PostgreSQL xử lý dữ liệu thế nào.

Kết quả EXPLAIN ANALYZE là một cây thực thi truy vấn. Mỗi tầng của cây mô tả một bước mà PostgreSQL thực hiện:

• Operation Type — loại thao tác (ví dụ, Seq Scan, Index Scan).
• Cost — PostgreSQL đánh giá thao tác này "đắt" cỡ nào.
• Rows — số dòng dự kiến và số dòng thực tế trả về.
• Time — thao tác mất bao lâu.

Ví dụ output:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Chú ý Seq Scan on students. Nghĩa là PostgreSQL đang duyệt TẤT CẢ các dòng trong bảng students. Nếu bảng to, sẽ RẤT CHẬM luôn đó.

Ví dụ dùng EXPLAIN ANALYZE

Cùng xem vài ví dụ thực tế để học cách phát hiện và xử lý vấn đề trong truy vấn nhé.

Ví dụ 1: quét toàn bộ bảng

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

Vấn đề ở đây là PostgreSQL thực hiện Seq Scan, tức là duyệt hết mọi dòng trong bảng. Nếu bảng có hàng triệu dòng, đây sẽ là bottleneck hiệu suất.

Giải pháp: tạo index cho cột age.

CREATE INDEX idx_students_age ON students(age);

Giờ chạy lại truy vấn nhé:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Output:

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

Giờ mình thấy Index Scan thay vì Seq Scan. Quá đã, truy vấn chạy vèo vèo luôn!

Ví dụ 2: truy vấn phức tạp với JOIN

Giả sử tụi mình có hai bảng: students và courses. Mình muốn biết tên sinh viên và tên khóa học mà họ đã đăng ký.

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

Output có thể như này:

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

Như bạn thấy, PostgreSQL kiểm soát tốt: dùng index trên bảng enrollments và courses, chạy rất nhanh. Nhưng nếu thiếu index nào đó, bạn sẽ thấy Seq Scan và truy vấn sẽ chậm hẳn.

Tối ưu hiệu suất function

Giờ giả sử tụi mình có function trả về danh sách sinh viên lớn hơn một tuổi nhất định:

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

Mình có thể phân tích hiệu suất function này bằng EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

Tăng tốc function

Nếu function chạy lâu, có thể do quét toàn bộ bảng. Để xử lý:

1. Đảm bảo cột dùng trong filter (age) đã có index.
2. Kiểm tra số dòng trong bảng, nếu quá nhiều thì nghĩ tới partitioning.

Bottleneck và cách xử lý

1. Quét toàn bộ bảng (Seq Scan). Dùng index để tăng tốc tìm kiếm dòng. Nhưng nhớ, nhiều index quá cũng làm chậm việc insert dữ liệu.

2. Trả về quá nhiều dòng. Nếu truy vấn trả về hàng triệu dòng, hãy thêm filter (WHERE, LIMIT) hoặc phân trang (OFFSET).

3. Thao tác "đắt". Một số thao tác như sort, aggregate hay join bảng lớn sẽ tốn nhiều tài nguyên. Dùng index hoặc chia nhỏ truy vấn thành nhiều bước.