Đọc EXPLAIN trong PostgreSQL: Đừng tối ưu truy vấn bằng phỏng đoán

Một truy vấn chậm không tự động có nghĩa là thiếu index. Nó có thể trả quá nhiều dữ liệu, dùng tham số khác với lúc thử hoặc bị ảnh hưởng bởi tải hệ thống. EXPLAIN giúp nhìn kế hoạch PostgreSQL chọn, từ đó đặt giả thuyết cụ thể trước khi sửa.

Bài viết dùng cú pháp PostgreSQL và một bảng orders giả định có các cột id, customer_id, created_at. Các lệnh là ví dụ để áp dụng trên môi trường thử phù hợp; không có số đo hiệu năng production hay lời hứa tăng tốc trong bài.

1. Bắt đầu bằng câu hỏi cần trả lời

Trước khi mở plan, ghi lại endpoint, SQL, bộ tham số gây chậm, số dòng mong đợi và thời gian quan sát. Với ứng dụng đa khách hàng, lấy mẫu cả khách hàng ít dữ liệu lẫn nhiều dữ liệu. Một truy vấn nhanh với tài khoản thử nhỏ chưa đại diện cho dữ liệu vận hành.

Đề xuất lưu bằng chứng thành một hồ sơ nhỏ: schema và index liên quan, phiên bản PostgreSQL, thời điểm đo và điều kiện tải. Loại bỏ thông tin nhạy cảm khỏi SQL hoặc plan trước khi chia sẻ ra ngoài nhóm được phép.

2. EXPLAIN và EXPLAIN ANALYZE khác nhau

Tài liệu EXPLAIN phân biệt việc hiển thị kế hoạch với lựa chọn ANALYZE thực thi câu lệnh để thu số liệu. Vì vậy, không thêm ANALYZE như một tùy chọn trang trí vào SQL chưa kiểm tra.

EXPLAIN (FORMAT TEXT)
SELECT id, created_at
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 20;

Ví dụ trên xem kế hoạch mà không chạy phần thực thi thông thường của SELECT. Khi cần đo thật, hãy thử trên môi trường kiểm soát với dữ liệu đại diện. Truy vấn có ANALYZE vẫn tiêu thụ tài nguyên; với câu lệnh ghi hoặc hàm có tác dụng phụ, nó còn có thể gây thay đổi. Không coi bọc transaction rồi rollback là bảo đảm xóa được mọi tác động bên ngoài.

3. Đọc các trường chính đúng đơn vị

Theo hướng dẫn đọc plan, cost là đơn vị ước tính của planner, không phải mili giây. Rows ước tính là số dòng đầu ra của node, không nhất thiết là số dòng đã quét. Khi có ANALYZE, actual rows và thời gian của node chạy nhiều lần được báo theo trung bình mỗi lần; cần đọc cùng loops.

Bảng này là khung điều tra, không phải quy tắc “thấy node nào thì thêm index đó”. Không cộng tùy tiện thời gian mọi node vì các tầng cha và con có quan hệ bao hàm; kế hoạch song song còn cần đọc bối cảnh worker.

4. Không xem Seq Scan là dấu hiệu thất bại

Với bảng nhỏ hoặc truy vấn lấy phần lớn dữ liệu, quét tuần tự có thể là lựa chọn hợp lý. Ngược lại, có chữ Index Scan không tự chứng minh truy vấn tối ưu. Mục tiêu là kết quả đúng với chi phí phù hợp, không phải làm plan chứa tên một index bằng mọi cách.

Đề xuất cho ví dụ orders: kiểm tra mục đích lọc theo customer_id và thứ tự created_at, id trước khi cân nhắc index ghép. Nếu ứng dụng còn điều kiện tenant hoặc trạng thái, giữ nguyên khi đo. Không xóa điều kiện phân quyền để tạo ra một benchmark đẹp hơn.

5. Thử từng thay đổi và giữ khả năng so sánh

1. Chụp plan ban đầu cùng bộ tham số và kết quả mong đợi.
2. Đặt một giả thuyết, chẳng hạn điều kiện lọc chưa phù hợp với index hiện có.
3. Thử thay đổi trong môi trường kiểm soát, không đồng thời đổi nhiều cấu hình.
4. Đo lại cùng trường hợp và thêm vài bộ tham số đại diện.
5. Kiểm tra tập kết quả, thứ tự và số dòng không bị sai.
6. Xem tác động tới ghi dữ liệu, dung lượng và truy vấn khác trước khi triển khai.

Đo lặp lại và ghi điều kiện cache, tải hệ thống. Một lần chạy nhanh hơn ngay sau lần đầu có thể do dữ liệu đã ở cache, chưa chứng minh thay đổi vừa làm là nguyên nhân. Tránh xóa cache của hệ thống production chỉ để tạo điều kiện thử nghiệm.

6. Plan không phải toàn bộ độ trễ ứng dụng

Nếu người dùng chờ lâu nhưng thời gian database thấp, tiếp tục kiểm tra thời gian đợi connection pool, số lượng truy vấn, truyền dữ liệu, chuyển đổi dữ liệu và render giao diện. Một endpoint có thể chậm vì chạy nhiều truy vấn nhỏ, dù từng plan riêng không đáng lo.

Trước khi kết luận, đối chiếu số đo database với log hoặc trace của request. Giữ ranh giới giữa điều đã quan sát và giả thuyết cần kiểm chứng: “ước tính dòng lệch” là bằng chứng, còn “cần thêm index X” vẫn là đề xuất phải thử.

EXPLAIN có ích nhất khi đi cùng quy trình đo có kiểm soát. Đọc đúng đơn vị, chọn dữ liệu đại diện và kiểm tra tính đúng đắn sẽ giúp tối ưu có căn cứ thay vì sửa schema theo cảm giác.