Đọc EXPLAIN

Bạn sẽ học được

• Đọc một dòng kế hoạch: cost, rows, width, actual time, loops.
• Phân biệt dự đoán của planner với số đo thực tế.
• Vì sao EXPLAIN khác EXPLAIN ANALYZE và khi nào không nên dùng bản sau.
• Dùng Buffers để biết truy vấn đọc bao nhiêu trang.
• Quy trình ba bước khi một truy vấn chậm.

Khái niệm cốt lõi

EXPLAIN trả về kế hoạch thực thi — cây các bước PostgreSQL dự định làm. EXPLAIN ANALYZE chạy truy vấn thật rồi in kèm số đo. Đây là công cụ đầu tiên phải mở khi ai đó nói "câu này chậm".

EXPLAIN SELECT * FROM orders WHERE customer_id = 2;

Seq Scan on orders  (cost=0.00..24.12 rows=6 width=44)
  Filter: (customer_id = 2)

Ba con số trong ngoặc:

Chi phí mặc định quy ước một lần đọc trang là 1.0, một phép so sánh dòng khoảng 0.01. Vì vậy 24.12 gần như nói "đọc khoảng 24 trang". Con số này chỉ dùng để so sánh các kế hoạch, không dùng để dự đoán thời gian.

Chạy bản có đo:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 2;

Seq Scan on orders  (cost=0.00..24.12 rows=6 width=44) (actual time=0.013..0.020 rows=3.00 loops=1)
  Filter: (customer_id = 2)
  Rows Removed by Filter: 13
  Buffers: shared hit=1
Planning Time: 0.023 ms
Execution Time: 0.089 ms

Phần thêm vào rất giàu thông tin:

• actual time=0.013..0.020: mili giây đến dòng đầu và đến dòng cuối.
• rows=3.00: thực tế 3 dòng, planner đoán 6 — sai gấp đôi nhưng không quan trọng vì bảng nhỏ.
• loops=1: số lần nút này được thực thi. Với nút nằm trong vòng lặp, actual time là trung bình mỗi vòng, phải nhân với loops mới ra tổng.
• Rows Removed by Filter: 13: đọc 16 dòng, bỏ 13. Đây là dấu hiệu điều kiện không chọn lọc.
• Buffers: shared hit=1: đọc một trang, đã nằm trong bộ nhớ đệm.
• Planning Time và Execution Time tách riêng: đôi khi truy vấn nhanh nhưng lập kế hoạch chậm.

Đọc một kế hoạch lớn

Kế hoạch trên bảng 200.000 dòng:

Aggregate  (cost=2979.68..2979.69 rows=1 width=8) (actual time=9.793..9.794 rows=1.00 loops=1)
  Buffers: shared hit=1274
  ->  Seq Scan on events  (cost=0.00..2977.98 rows=682 width=0) (actual time=0.012..9.777 rows=40.00 loops=1)
        Filter: (user_id = 42)
        Rows Removed by Filter: 199960
        Buffers: shared hit=1274
Planning Time: 0.063 ms
Execution Time: 9.819 ms

Cách đọc từ trong ra ngoài, từ dưới lên trên:

1. Seq Scan on events là nút lá — đọc toàn bảng.
2. Mũi tên -> cho biết nút cha. Aggregate nhận dòng từ con và đếm.
3. actual time=9.777 của nút con gần bằng Execution Time: 9.819 của cả câu → gần như toàn bộ thời gian nằm ở bước quét bảng.

Hai chỉ số quyết định chẩn đoán:

Rows Removed by Filter: 199960 trên tổng 200.000 dòng nghĩa là bộ lọc loại 99,98% dữ liệu. PostgreSQL đã làm việc rất nhiều để lấy rất ít. Đây là dấu hiệu rõ ràng cần index.

Buffers: shared hit=1274 nghĩa là đọc 1.274 trang — khoảng 10 MB. Dù tất cả đã nằm trong cache (hit), vẫn tốn thời gian CPU để kiểm tra từng dòng.

Sau khi thêm index, cùng câu chuyển thành:

Aggregate  (cost=1282.62..1282.63 rows=1 width=8) (actual time=0.066..0.067 rows=1)
  Buffers: shared hit=40 read=2
  ->  Bitmap Heap Scan on events  (actual time=0.020..0.056 rows=40.00 loops=1)
        Recheck Cond: (user_id = 42)
        ->  Bitmap Index Scan on idx_events_user  (cost=0.00..11.79 rows=1000 width=0)
              Index Cond: (user_id = 42)
Execution Time: 0.082 ms

Buffers giảm từ 1.274 xuống 42, thời gian từ 9,8 ms xuống 0,08 ms. Chú ý read=2: hai trang phải đọc từ đĩa, phần còn lại đã trong cache. Trong lần chạy đầu tiên, read sẽ cao hơn — luôn chạy lại lần hai trước khi kết luận về tốc độ.

Dự đoán sai — dấu hiệu quan trọng nhất

rows=682 dự đoán so với rows=40.00 thực tế là sai 17 lần. Planner dùng thống kê để đoán số dòng; thống kê cũ hoặc thiếu histogram khiến nó chọn sai phương án. Ba nguyên nhân thường gặp:

Trong môi trường học, mỗi bài chạy trên một cơ sở dữ liệu mới nên thống kê luôn mới. Trên production, sau khi nhập hàng loạt dữ liệu, chạy ANALYZE là bước bắt buộc — nếu không, planner vẫn tưởng bảng chỉ có vài trăm dòng.

EXPLAIN ANALYZE có tác dụng phụ

EXPLAIN ANALYZE thực thi câu lệnh. Với SELECT, điều đó vô hại. Với UPDATE, DELETE, INSERT, nó thay đổi dữ liệu thật. Cách an toàn:

BEGIN;
EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'cancelled';
ROLLBACK;

Trong PostgreSQL hiện đại còn có EXPLAIN (ANALYZE, BUFFERS, WAL) cho câu ghi, nhưng thói quen bọc transaction rồi rollback vẫn đáng giữ.

Quy trình ba bước

Khi nhận báo cáo "truy vấn chậm":

1. Đo — EXPLAIN (ANALYZE, BUFFERS) để biết thời gian thật và số trang đọc.
2. Tìm nút đắt nhất — nút có actual time lớn nhất, hoặc Rows Removed by Filter lớn nhất.
3. Sửa đúng nguyên nhân — index nếu quét nhiều, viết lại điều kiện nếu bọc hàm, giảm cột nếu width lớn, phân trang nếu trả quá nhiều dòng.

Đừng đoán. Kế hoạch thật thường khác trực giác: một truy vấn "trông nặng" có thể nhanh vì dùng index, còn một truy vấn "trông nhẹ" có thể chậm vì Nested Loop nhân số dòng.

Thực hành

1. Chạy EXPLAIN và EXPLAIN (ANALYZE, BUFFERS) cho cùng một câu trên orders. Ghi lại bốn con số khác nhau giữa hai bản.
2. Trên bảng events 200.000 dòng, tìm nút có Rows Removed by Filter lớn nhất và giải thích.
3. Chạy ANALYZE events; rồi so sánh rows= ước lượng trước và sau.
4. Đọc kế hoạch của SELECT count(*) FROM events; và cho biết nó có đọc bảng không.
5. Viết một câu UPDATE bọc trong BEGIN ... ROLLBACK cùng EXPLAIN ANALYZE và xác nhận dữ liệu không đổi.

Lỗi thường gặp

Tóm tắt

• EXPLAIN là kế hoạch dự kiến; EXPLAIN ANALYZE chạy thật và thêm số đo.
• cost là đơn vị so sánh, không phải mili giây; actual time mới là thời gian.
• Rows Removed by Filter lớn là dấu hiệu cần index hoặc viết lại điều kiện.
• Buffers cho biết số trang đọc — chỉ số ổn định hơn thời gian.
• Luôn kiểm tra rows dự đoán so với thực tế; lệch xa nghĩa là thống kê cần cập nhật.