Phân tích so sánh EXPLAIN ANALYZE và pg_stat_statements

Đến giai đoạn này chắc hẳn bạn sẽ tự hỏi: tại sao lại cần hai công cụ khác nhau để phân tích? Thường thì người ta dùng EXPLAIN ANALYZE hay pg_stat_statements nhiều hơn? Cùng mình tìm hiểu hai cách tiếp cận này, điểm mạnh yếu của từng cái, và khi nào nên dùng cái nào nhé.

Bài toán mà các công cụ giải quyết

EXPLAIN ANALYZE: công cụ phân tích sâu một truy vấn cụ thể. Nếu bạn muốn biết PostgreSQL thực thi truy vấn như thế nào, dùng node nào, xử lý bao nhiêu dòng, mỗi thao tác mất bao lâu, thì đây là lựa chọn của bạn. Nó giúp trả lời câu hỏi: "Tại sao truy vấn này của mình lại chạy chậm vậy?"

pg_stat_statements: công cụ để monitor ở mức cao hơn, cung cấp thông tin về hiệu năng của tất cả truy vấn chạy trong database. Đây là lựa chọn nếu bạn muốn có cái nhìn tổng thể: "Những truy vấn nào trong database của mình là chậm nhất?" hoặc "Truy vấn nào gây tải lớn nhất lên server?"

Khi nào dùng EXPLAIN ANALYZE

EXPLAIN ANALYZE là công cụ debug giúp bạn hiểu PostgreSQL thực thi truy vấn cụ thể ra sao. Dùng nó trong các trường hợp sau:

Tối ưu truy vấn cụ thể Nếu bạn nhận được phàn nàn rằng trang app của bạn load mãi không xong, việc đầu tiên là tìm truy vấn gây ra vấn đề đó và dùng EXPLAIN ANALYZE. Nó sẽ cho bạn thấy execution plan và các chỉ số thực tế như thời gian chạy, số dòng xử lý.

Chọn index phù hợp Khi bạn tạo index mới hoặc thay đổi index cũ, hãy dùng EXPLAIN ANALYZE để xem PostgreSQL có chọn index đó không. Nếu không, có thể index bạn tạo ra không giúp tối ưu truy vấn.

Debug truy vấn phức tạp Nếu bạn viết truy vấn phức tạp với nhiều JOIN hoặc WHERE, phân tích execution plan thực tế với EXPLAIN ANALYZE sẽ giúp bạn phát hiện bottleneck, ví dụ như scan tuần tự không cần thiết (hello, Seq Scan).

Ví dụ: Tối ưu truy vấn với EXPLAIN ANALYZE

-- Truy vấn chạy chậm
SELECT *
FROM students
WHERE name = 'Alice';

-- Phân tích execution plan
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Nếu bạn thấy Seq Scan được dùng, có thể bạn quên tạo index:

-- Tạo index trên cột name
CREATE INDEX idx_students_name ON students(name);

-- Kiểm tra lại lần nữa
EXPLAIN ANALYZE
SELECT *
FROM students
WHERE name = 'Alice';

Khi nào dùng pg_stat_statements

Công cụ này cực kỳ hữu ích để phân tích hiệu năng toàn hệ thống. Dùng nó trong các trường hợp sau:

Monitor trên production pg_stat_statements hiển thị thống kê thực thi truy vấn trong một khoảng thời gian nhất định. Bạn dễ dàng tìm truy vấn chậm nhất nhờ cột total_time, cho biết tổng thời gian thực thi của từng truy vấn.

Tìm truy vấn "nặng" Muốn biết truy vấn nào thường xuyên gây tải lên database? Hãy sort truy vấn theo số lần đọc từ bộ nhớ (shared_blks_hit) hoặc số dòng đã xử lý (rows).

Phát hiện truy vấn chạy quá thường xuyên Đôi khi không chỉ truy vấn lâu mới gây vấn đề, mà cả truy vấn chạy quá nhiều lần cũng vậy. Ví dụ, nếu một truy vấn chạy 100 lần/phút, chỉ cần tối ưu nhẹ cũng giảm tải server đáng kể.

Ví dụ: Tìm truy vấn chậm với pg_stat_statements

-- Xem thống kê truy vấn
SELECT query,
       calls,
       total_time,
       rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 5;

Truy vấn này sẽ show top 5 truy vấn tốn nhiều thời gian nhất.

So sánh hai cách tiếp cận: khác biệt ở đâu?

Dùng cả hai công cụ cùng lúc

Như mọi thứ trong lập trình, không có nút thần kỳ nào giải quyết hết mọi vấn đề. Cách tốt nhất là kết hợp cả hai công cụ. Ví dụ:

1. Dùng pg_stat_statements để xác định truy vấn chậm nhất hoặc chạy nhiều nhất trong hệ thống.

2. Sau đó, phân tích truy vấn đó bằng EXPLAIN ANALYZE để hiểu nguyên nhân tại sao nó lại có vấn đề.

Ví dụ thực tế: tiếp cận tổng thể

-- Bước 1: Tìm truy vấn chậm nhất
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 1;

-- Bước 2: Phân tích truy vấn này
EXPLAIN ANALYZE
<copy truy vấn từ bước trước>;

Lỗi phổ biến khi sử dụng

Khi làm việc với EXPLAIN ANALYZE và pg_stat_statements có vài lỗi newbie hay gặp:

1. Quên kiểm tra dữ liệu thực tế. Nếu bạn phân tích truy vấn trên bảng trống, kết quả EXPLAIN ANALYZE có thể gây hiểu nhầm. Hãy đảm bảo database test phản ánh đúng lượng dữ liệu thực tế.

2. Bỏ qua việc monitor cũng tốn tài nguyên. Nếu extension pg_stat_statements bật trên production, hãy chắc chắn nó được cấu hình tối ưu và không gây quá tải.

3. Chỉ đọc execution plan lý thuyết. Nhớ rằng EXPLAIN chỉ cho plan lý thuyết. Dùng EXPLAIN ANALYZE để lấy dữ liệu thực tế.

Giờ thì bạn đã có đủ kiến thức để không chỉ xử lý truy vấn chậm mà còn phòng tránh chúng. PostgreSQL cung cấp công cụ mạnh mẽ, và biết kết hợp chúng sẽ giúp bạn đạt hiệu năng tối ưu ngay cả trên hệ thống nhiều tải.

2

Nhiệm vụ

SQL SELF, mức độ, bài học

Đã khóa

Tìm kiếm các truy vấn "nặng" nhất thông qua `pg_stat_statements`

Tìm kiếm các truy vấn "nặng" nhất thông qua `pg_stat_statements`

Mở

1

Khảo sát/đố vui

, cấp độ , bài học

Không có sẵn

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

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

Mở