Đừng Chạy EXPLAIN ANALYZE Thiếu BUFFERS Trong PostgreSQL

Khi điều tra một câu lệnh truy vấn chạy chậm trong PostgreSQL, thói quen của phần lớn lập trình viên là gõ ngay EXPLAIN ANALYZE. Khi nhìn thấy dòng kết luận Execution Time: 1.42 ms, chúng ta thường thở phào, đóng ticket và đẩy code lên production vì nghĩ rằng truy vấn đã tối ưu xuất sắc. Thế nhưng chỉ vài ngày sau, dưới lưu lượng truy cập thực tế, CPU của database chạm ngưỡng 100%, hàng đợi I/O nghẽn cứng và chính câu truy vấn tưởng chừng siêu nhanh đó lại kéo sập cả hệ thống.

Nguyên nhân nằm ở chỗ: thời gian thực thi (execution time) đo được trong môi trường thử nghiệm thường là một ảo ảnh do bộ nhớ đệm tạo ra. Để đánh giá chính xác một câu truy vấn có thực sự mở rộng tốt hay không, bạn luôn cần chạy EXPLAIN (ANALYZE, BUFFERS).

Cạm bẫy từ bộ nhớ đệm (Warm Cache)

PostgreSQL quản lý dữ liệu thông qua vùng nhớ có tên shared_buffers. Mỗi khi cần đọc một trang dữ liệu từ bảng hoặc index, database sẽ kiểm tra xem trang 8 KB đó đã nằm sẵn trên RAM hay chưa. Nếu đã có sẵn trong bộ nhớ đệm, việc truy xuất chỉ tốn vài chu kỳ CPU để đọc con trỏ bộ nhớ, diễn ra trong tích tắc vài phần nghìn mili-giây. Ngược lại, nếu trang dữ liệu chưa có trên RAM, PostgreSQL buộc phải gọi xuống page cache của hệ điều hành hoặc đọc trực tiếp từ ổ đĩa vật lý—vốn chậm hơn hàng trăm lần.

Trong quá trình lập trình hoặc kiểm thử ở môi trường staging, chúng ta thường chạy đi chạy lại một vài câu truy vấn mẫu. Toàn bộ dữ liệu liên quan khi đó đã nằm trọn trong shared_buffers. Một câu lệnh quét qua 50.000 trang dữ liệu vẫn có thể hoàn thành trong 15 ms đơn giản vì không có lần đọc đĩa nào xảy ra. Nhưng ở môi trường production thực tế, hàng trăm câu truy vấn khác nhau chạy đồng thời sẽ liên tục đẩy dữ liệu của bạn ra khỏi RAM. Khi câu truy vấn bị ép phải đọc từ ổ cứng, thời gian 15 ms đó sẽ biến thành 6.000 ms chỉ sau một đêm.

Thời gian thực thi chỉ phản ánh độ trễ tạm thời của môi trường tại một thời điểm nhất định. Ngược lại, số lượng buffer bị truy xuất mới chính là thước đo khối lượng công việc thực tế mà câu truy vấn bắt phần cứng phải thực hiện.

Đọc hiểu các chỉ số Buffer

Khi bạn thêm tùy chọn BUFFERS, PostgreSQL sẽ bổ sung các dòng thông tin chi tiết bên dưới từng node trong cây thực thi kế hoạch:

EXPLAIN (ANALYZE, BUFFERS) 
SELECT o.id, o.total_amount, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at >= '2026-01-01'
  AND o.status = 'completed';

Kết quả trả về sẽ hiển thị rõ ràng cách mà PostgreSQL tương tác với dữ liệu:

->  Hash Join  (cost=1420.00..5890.10 rows=4120 width=45) 
               (actual time=0.820..4.120 rows=4150 loops=1)
      Buffers: shared hit=18200 read=450

Các chỉ số này mang ý nghĩa kỹ thuật cụ thể như sau:

• shared hit: Số lượng block 8 KB được tìm thấy trực tiếp trong shared_buffers của PostgreSQL. Hãy nhân con số này với 8 KB để biết tổng dung lượng bộ nhớ mà truy vấn đã xới tung lên. Ví dụ: 18.200 hits tương đương với khoảng 145 MB dữ liệu được duyệt qua trong RAM.
• shared read: Số lượng block 8 KB chưa có trong bộ đệm và phải đọc từ bộ nhớ đệm hệ điều hành hoặc đĩa cứng. Chỉ số này cao là dấu hiệu cho thấy truy vấn dễ bị nghẽn I/O khi cache bị nguội hoặc do quét tuần tự (sequential scan) quá nhiều.
• shared dirtied: Số block bị câu lệnh sửa đổi và sẽ cần được ghi xuống đĩa trong các đợt checkpoint tiếp theo.
• shared written: Số block mà chính tiến trình xử lý câu truy vấn này phải trực tiếp ghi xuống đĩa ngay lúc chạy.
• temp read / temp write: Lượng dữ liệu phải tràn xuống các file tạm trên ổ đĩa do dung lượng work_mem được cấp phát không đủ để chứa các phép sắp xếp (Sort) hoặc bảng băm (Hash Join).

Hiểm họa giấu mặt từ Nested Loop

Một trong những trường hợp điển hình nhất mà chỉ có BUFFERS mới vạch trần được là cạm bẫy vòng lặp lồng nhau (Nested Loop). Hãy xem xét một câu truy vấn kết hợp hai bảng thông qua một index. Khi chạy thử, kết quả trông có vẻ rất ổn định:

->  Nested Loop (actual time=0.040..3.100 rows=500 loops=1)
      Buffers: shared hit=45200
      ->  Seq Scan on promotions (actual time=0.010..0.020 rows=500 loops=1)
      ->  Index Scan using idx_orders_promo on orders (actual time=0.004..0.005 rows=1 loops=500)
            Buffers: shared hit=45180

Nếu không bật BUFFERS, bạn chỉ thấy actual time=3.100 ms và ngỡ rằng truy vấn này chạy quá nhanh. Nhưng khi nhìn vào thống kê buffer, bạn sẽ phát hiện ra nó tốn tới shared hit=45200 (khoảng 361 MB) chỉ để lấy về đúng 500 dòng dữ liệu!

Node Index Scan bên trong bị lặp lại 500 lần (loops=500), mỗi lần lại duyệt qua cây B-Tree từ nhánh đến lá. Trên máy phát triển với RAM nhàn rỗi, 45.000 lần đọc RAM này chỉ tốn 3 mili-giây. Nhưng khi đưa lên production và có 50 worker chạy đồng thời, truy vấn này sẽ lập tức vét sạch băng thông bộ nhớ, liên tục đẩy các trang dữ liệu quan trọng của những bảng khác ra khỏi cache, gây ra hiện tượng sụt giảm hiệu năng trên diện rộng.

Nguyên tắc tối ưu trong công việc hàng ngày

Để đảm bảo các câu truy vấn có khả năng chịu tải vững vàng khi dữ liệu phình to, hãy áp dụng ba thói quen sau:

1. Lấy tổng số buffer làm thước đo chi phí chính: Thời gian mili-giây sẽ biến thiên liên tục tùy vào tải của CPU và tình trạng ổ đĩa. Nhưng tổng số block (hit + read) gần như là hằng số với một tập dữ liệu và kế hoạch thực thi cụ thể. Mục tiêu tối ưu đầu tiên luôn là giảm thiểu số lượng block phải duyệt qua.
2. Tận dụng Index-Only Scan: Nếu một Index Scan có số lượng buffer hit cao do liên tục phải nhảy sang đọc bảng dữ liệu gốc (Heap fetch), hãy xem xét thêm các cột cần lấy vào index bằng mệnh đề INCLUDE để chuyển hẳn sang Index Only Scan.
3. Cảnh giác với temp buffers: Bất cứ khi nào thấy xuất hiện temp read hay temp write, điều đó có nghĩa là dữ liệu đang bị tràn ra đĩa vì work_mem quá nhỏ. Việc tăng work_mem cục bộ cho phiên làm việc đó hoặc tinh chỉnh lại điều kiện lọc sẽ giúp loại bỏ chi phí I/O đắt đỏ này.

Thời gian thực thi chỉ cho biết câu truy vấn chạy nhanh thế nào trong điều kiện phòng thí nghiệm lý tưởng. Các chỉ số buffer mới là bản tường trình chính xác về cái giá tài nguyên phần cứng mà bạn đang phải trả. Hãy biến EXPLAIN (ANALYZE, BUFFERS) thành câu lệnh mặc định mỗi khi kiểm tra hiệu năng cơ sở dữ liệu.

Nguồn tham khảo

1. postgresql.org