Khi một cơ sở dữ liệu chạy chậm, câu hỏi đầu tiên là: tối ưu truy vấn nào trước? Phản xạ tự nhiên là đi tìm truy vấn chậm nhất — cái mất nửa giây một lần chạy. Nhưng đó thường là câu hỏi sai, và trả lời sai nó nghĩa là bạn bỏ hàng giờ tối ưu một thứ mà kết quả chung gần như không nhúc nhích. Công cụ trả lời đúng là pg_stat_statements, một extension theo dõi mọi truy vấn và cho biết cái nào thật sự ngốn thời gian máy chủ — mà "thật sự ngốn" không phải lúc nào cũng là "chậm nhất". Bài này đo đúng khác biệt đó, trên một ví dụ cố ý dựng để hai câu hỏi cho hai câu trả lời trái ngược.
Theo dõi mọi truy vấn, gộp theo dạng
pg_stat_statements là một extension (cần khai shared_preload_libraries = 'pg_stat_statements' khi khởi động PostgreSQL, rồi CREATE EXTENSION). Một khi bật, nó ghi lại mọi truy vấn đã chạy và cộng dồn số liệu cho từng cái: số lần gọi (calls), tổng thời gian (total_exec_time), thời gian trung bình mỗi lần (mean_exec_time), số hàng trả về.
Điều làm nó hữu dụng là chuẩn hóa. Nó không lưu từng câu SQL nguyên văn, mà thay các hằng số bằng tham số: WHERE k = 42 và WHERE k = 99 đều trở thành WHERE k = $1. Nhờ vậy, một triệu lần gọi cùng một dạng truy vấn với giá trị khác nhau gộp thành một dòng với calls cộng dồn — thay vì một triệu dòng vô dụng. Đây chính là thứ cho phép ta hỏi "dạng truy vấn nào ngốn tổng thời gian nhiều nhất", câu hỏi thật sự đáng quan tâm.
Sự chuẩn hóa này giống hệt cách một prepared statement gộp truy vấn theo dạng: hằng số cụ thể không quan trọng với hình dạng kế hoạch, nên bỏ chúng đi để nhóm lại là đúng. Khác biệt là prepared statement dùng nó để tái dùng kế hoạch, còn pg_stat_statements dùng nó để cộng dồn số liệu — cùng một ý tưởng "cùng dạng thì tính chung".
Một lần tôi đo hớ: tối ưu nhầm truy vấn chậm nhất
Tôi dựng một bảng một triệu hàng và chạy hai dạng truy vấn rất khác nhau, mô phỏng một ứng dụng thật:
- Truy vấn A — một báo cáo nặng:
SELECT v, count(*) FROM t GROUP BY v ORDER BY ... LIMIT 10, quét và gom cả bảng. Chạy 2 lần (như một báo cáo mở vài lần trong ngày). - Truy vấn B — một tra cứu nhẹ:
SELECT id FROM t WHERE k = $1, dùng index. Chạy 100.000 lần (như một lookup trong mỗi request).
Rồi tôi hỏi pg_stat_statements. Nếu xếp theo thời gian trung bình mỗi lần — cái phản xạ đầu tiên bảo tôi làm để tìm "truy vấn chậm":
xếp theo mean_exec_time:
A (GROUP BY cả bảng): mean 23.47 ms <- chậm nhất
B (tra index): mean 0.0075 ms
Truy vấn A chậm hơn B tới 3130 lần mỗi lần chạy. Theo cách nhìn này, A rõ ràng là thủ phạm, và tôi sẽ bỏ công tối ưu nó. Nhưng khi xếp theo tổng thời gian, bức tranh lật ngược:
xếp theo total_exec_time:
B (tra index): 100000 lần, tổng 754 ms <- ngốn nhiều nhất
A (GROUP BY cả bảng): 2 lần, tổng 47 ms
Truy vấn B — cái "nhanh" — ngốn 754ms tổng cộng, gấp 16 lần truy vấn A "chậm" (47ms). Và đây mới là 100.000 lần gọi; một dịch vụ thật chạy con lookup đó hàng triệu lần mỗi ngày, lúc đó khoảng cách không phải 16 lần mà là hàng trăm lần — trong khi cái báo cáo nặng vẫn chỉ chạy vài lần. Lý do đơn giản: A chạy 2 lần, còn B chạy 100.000 lần. Mỗi lần B rẻ như cho, nhưng nhân với số lần gọi khổng lồ, nó là gánh nặng lớn nhất trên máy chủ. Cái sai của tôi là đo một lần chạy thay vì tổng tải: tối ưu A (giảm 47ms) gần như vô nghĩa, còn cải thiện B dù chỉ một chút (nó đã dùng index rồi, nhưng có thể giảm số lần gọi, hoặc cache) mới thật sự dời được kim.
total_exec_time chính là calls × mean_exec_time — nó gộp cả tần suất lẫn chi phí mỗi lần. Đó là lý do pg_stat_statements xếp theo cột này mới chỉ ra đúng nơi thời gian máy chủ thật sự chảy vào. "Truy vấn chậm nhất" và "truy vấn tốn nhiều thời gian nhất" là hai câu hỏi khác nhau, và chỉ câu sau đáng để bắt đầu tối ưu.
Ngoài thời gian, còn buffer và số hàng
Tổng thời gian là cột hay dùng nhất, nhưng pg_stat_statements còn ghi những cột khác giúp chẩn đoán. rows cho biết một dạng truy vấn trả về bao nhiêu hàng tổng cộng — một truy vấn trả hàng triệu hàng mỗi lần thường là dấu hiệu ai đó SELECT * rồi lọc ở tầng ứng dụng. Các cột shared_blks_hit và shared_blks_read cho biết truy vấn đọc bao nhiêu trang từ bộ đệm so với từ đĩa — một truy vấn read cao là ứng viên cho thêm index hay tăng bộ đệm. Như bài chi phí một truy vấn đã đo, số trang đọc là thước đo công việc đáng tin hơn thời gian; pg_stat_statements cho bạn con số đó cộng dồn trên toàn hệ thống, không chỉ một lần chạy.
Một lưu ý vận hành: pg_stat_statements chỉ giữ số liệu cho tới khi bạn gọi pg_stat_statements_reset() hoặc CSDL khởi động lại, và nó giới hạn số dạng truy vấn theo dõi (mặc định 5000). Nên nó là công cụ đang chạy — soi định kỳ trên production, không phải một bản ghi vĩnh viễn.
Vì sao điều này quan trọng khi lập trình
Hệ quả đầu tiên: xếp theo tổng thời gian, không theo thời gian một lần. Khi mở pg_stat_statements để tìm việc, ORDER BY total_exec_time DESC cho bạn danh sách đúng: những dạng truy vấn ngốn nhiều giây máy chủ nhất cộng dồn. Một truy vấn nhẹ gọi hàng triệu lần thường đứng trên một báo cáo nặng chạy vài lần — và tối ưu nó (thêm index, giảm số lần gọi, gộp N+1, cache kết quả) trả công lớn hơn nhiều.
Hệ quả thứ hai: một truy vấn nhanh không có nghĩa là rẻ. Chi phí thật của một dạng truy vấn là chi phí mỗi lần nhân tần suất. Con số mang theo: pg_stat_statements chuẩn hóa truy vấn (hằng số thành $1) rồi cộng dồn calls, total_exec_time, mean; xếp theo total_exec_time = calls × mean mới chỉ ra thủ phạm thật — một truy vấn tra index 0.0075ms gọi 100000 lần ngốn 754ms tổng, gấp 16 lần một báo cáo 23ms chạy 2 lần (47ms), dù mỗi lần báo cáo chậm hơn 3130 lần. Đừng săn truy vấn chậm nhất; săn truy vấn tốn tổng thời gian nhất.
Thử ba mươi giây
Trên một PostgreSQL đã bật extension (kiểm bằng SELECT * FROM pg_stat_statements LIMIT 1; — nếu lỗi thì cần thêm shared_preload_libraries và khởi động lại), chạy truy vấn vàng này: SELECT calls, round(total_exec_time::numeric,0) AS total_ms, round(mean_exec_time::numeric,3) AS mean_ms, query FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;. Nó cho bạn mười dạng truy vấn ngốn nhiều thời gian máy chủ nhất trên hệ thống đang chạy. So cột total_ms với mean_ms: bạn sẽ thấy những dòng đầu bảng thường không phải câu có mean_ms cao nhất, mà là câu calls lớn. Đó chính là danh sách việc cần làm — và gần như luôn khác với "truy vấn tôi thấy chậm khi bấm thử".
