Cách dùng EXPLAIN ANALYZE để đo thời gian thực thi thực tế của truy vấn

Nếu lệnh EXPLAIN cho phép bạn nhìn vào quả cầu pha lê để xem PostgreSQL “dự định” thực hiện truy vấn như thế nào, thì EXPLAIN ANALYZE biến bạn thành thám tử thực thụ, người sẽ tìm hiểu chuyện gì thực sự đã xảy ra.

Sự khác biệt chính giữa EXPLAIN và EXPLAIN ANALYZE:

EXPLAIN – là lý thuyết, cho thấy PostgreSQL dự định thực hiện truy vấn ra sao. Bạn sẽ thấy các giá trị dự đoán như số dòng (rows) và chi phí thực thi (cost).

EXPLAIN ANALYZE – là thực tế. PostgreSQL thực sự chạy truy vấn và cho bạn thấy:

• Số dòng thực tế được xử lý ở mỗi bước.
• Thời gian thực tế thực hiện từng thao tác.
• So sánh với dự đoán của plan (rows và cost).

Ví dụ: nếu truy vấn của bạn dự đoán xử lý 100 dòng, nhưng thực tế lại xử lý 10 000 dòng, EXPLAIN ANALYZE sẽ bóc trần sự thật “lộn xộn” này ngay lập tức!

Cú pháp cơ bản và cách dùng

Giống như EXPLAIN, EXPLAIN ANALYZE rất dễ dùng. Chỉ cần thêm từ ANALYZE vào lệnh EXPLAIN của bạn.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

PostgreSQL sẽ làm gì:

• Nó thực thi truy vấn.
• Ghi lại từng thao tác trong execution plan, bao gồm các chỉ số thực tế.
• Trả về mô tả đầy đủ quá trình thực thi truy vấn.

EXPLAIN ANALYZE cung cấp những dữ liệu gì?

Thời gian thực tế thực hiện thao tác:

• Actual Start Time: khi thao tác bắt đầu.
• Actual End Time: khi thao tác kết thúc.

Tổng số dòng được xử lý:

Điều này giúp bạn đánh giá độ chính xác của dự đoán trong plan (giá trị rows).

Thông tin về buffer:

Cách các buffer trên đĩa và RAM được sử dụng.

Ví dụ sử dụng EXPLAIN ANALYZE

Cùng xem một ví dụ cụ thể nhé. Chúng ta có bảng students chứa dữ liệu về sinh viên:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER,
    grade FLOAT
);

INSERT INTO students (name, age, grade)
VALUES
('Alice', 22, 4.1),
('Bob', 19, 3.8),
('Charlie', 23, 4.5),
('Diana', 20, 3.9);

Chạy truy vấn để lấy các sinh viên lớn hơn 20 tuổi:

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

Ví dụ kết quả:

Seq Scan on students  (cost=0.00..14.00 rows=2 width=116) (actual time=0.025..0.026 rows=2 loops=1)
  Filter: (age > 20)
  Rows Removed by Filter: 2
Planning Time: 0.032 ms
Execution Time: 0.048 ms

Phân tích kết quả:

• Seq Scan – cho biết PostgreSQL đang quét tuần tự bảng.
• cost=0.00..14.00 – đây là chi phí dự đoán của thao tác.
• rows=2 – PostgreSQL dự đoán truy vấn sẽ trả về 2 dòng (và đúng thế thật!).
• actual time=0.025..0.026 – thời gian thực tế thực hiện thao tác (tính bằng mili giây).
• Rows Removed by Filter: 2 – hai dòng đã bị loại bỏ vì không thỏa mãn điều kiện WHERE.

So sánh lý thuyết và thực tế

Đây chính là “ma thuật” của EXPLAIN ANALYZE: nó cho bạn thấy truy vấn thực sự được thực hiện ra sao, đồng thời cho phép so sánh với execution plan lý thuyết.

Cùng xem một ví dụ phức tạp hơn nhé.

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20 AND grade > 4.0;

Ví dụ kết quả:

Seq Scan on students  (cost=0.00..14.00 rows=1 width=116) (actual time=0.026..0.027 rows=1 loops=1)
  Filter: ((age > 20) AND (grade > 4.0))
  Rows Removed by Filter: 3
Planning Time: 0.035 ms
Execution Time: 0.057 ms

Chúng ta thấy gì:

1. PostgreSQL thực thi truy vấn trong 0.057 mili giây.
2. Chỉ một dòng (rows=1) thỏa mãn điều kiện WHERE.
3. Ba dòng đã bị loại bởi filter (Rows Removed by Filter: 3).

Tóm tắt

Dùng EXPLAIN ANALYZE giúp bạn tìm ra điểm nghẽn và hiểu cách tối ưu truy vấn. Ví dụ:

• Nếu Seq Scan quá “nặng”, có lẽ đã đến lúc thêm index.
• Nếu dự đoán của PostgreSQL khác xa dữ liệu thực tế, hãy kiểm tra lại thống kê bảng (ANALYZE) hoặc cấu trúc index.