PostgreSQL Bài 25: Đọc và phân tích Execution Plan bằng EXPLAIN (ANALYZE, BUFFERS, VERBOSE)

Khi một câu truy vấn trên môi trường sản xuất chạy chậm, cách giải quyết thiếu căn cứ nhất là đoán mò rồi gắn bừa index. Để tối ưu hóa truy vấn một cách khoa học, công cụ duy nhất cung cấp sự thật khách quan là EXPLAIN.

EXPLAIN cho phép bạn nhìn thấu suy nghĩ của Query Optimizer (Planner): Nó dự định làm gì? Nó ước lượng tốn bao nhiêu chi phí? Và khi câu lệnh thực sự chạy trên hệ thống, từng node thực thi tiêu tốn bao nhiêu mili-giây, đọc bao nhiêu block từ RAM hay ổ đĩa?

Bài học này sẽ hướng dẫn bạn phương pháp đọc hiểu toàn diện một bản kế hoạch thực thi (Execution Plan) chuẩn công nghiệp.

1. Cú pháp và Các tùy chọn của lệnh EXPLAIN

Cú pháp chuẩn hiện đại của EXPLAIN sử dụng danh sách tham số đặt trong cặp ngoặc đơn ():

SQL

EXPLAIN [ ( option [, ...] ) ] statement;

1.1. Bốn tùy chọn cốt lõi bắt buộc phải biết:

Tùy chọn	Cơ chế hoạt động	Có thực sự chạy câu SQL không?	Kịch bản sử dụng
EXPLAIN (Mặc định)	Chỉ phân tích cú pháp và hiển thị ước lượng (Estimation) của Planner.	KHÔNG CHẠY. Không tạo side-effect.	Xem nhanh kế hoạch ban đầu, an toàn tuyệt đối với lệnh DELETE/UPDATE.
ANALYZE	Bắt buộc thực thi câu SQL thật, đo đếm thời gian chạy thực tế và số dòng trả về.	CÓ CHẠY THẬT. Ghi nhận kết quả thật.	Đo đạc hiệu năng thực tế. Cẩn thận: Lệnh DELETE sẽ xóa dữ liệu thật!
BUFFERS	Đo đạc số lượng Data Page (8 KB) được đọc từ RAM Cache (shared hit) hay từ Ổ đĩa (read). (Bắt buộc đi kèm ANALYZE).	Phụ thuộc vào ANALYZE.	Chỉ số quan trọng nhất để chẩn đoán nghẽn I/O.
VERBOSE	Hiển thị thêm danh sách các cột đầu ra (Output list) của từng node và tiền tố schema định danh.	Tùy chọn.	Soi chi tiết các biểu thức lọc và cột được truyền qua từng node.

Cảnh báo an toàn với EXPLAIN ANALYZE: Nếu bạn chạy:

SQL

EXPLAIN ANALYZE DELETE FROM orders WHERE status = 'CANCELLED';

PostgreSQL sẽ xóa sạch dữ liệu thật! Để kiểm tra kế hoạch của các câu lệnh ghi (INSERT/UPDATE/DELETE) mà không làm thay đổi dữ liệu, hãy bọc nó trong một Transaction và ROLLBACK:

SQL

BEGIN;
EXPLAIN (ANALYZE, BUFFERS) DELETE FROM orders WHERE status = 'CANCELLED';
ROLLBACK;

2. Giải phẫu một dòng Node trong Execution Plan

Một Execution Plan được cấu tạo từ các Node thực thi (Plan Nodes) xếp phân tầng hình cây. Mỗi node nhận dữ liệu từ các node con bên dưới, xử lý và đẩy dữ liệu lên node cha.

Hãy quan sát một dòng thông báo điển hình:

Plaintext

->  Seq Scan on products  (cost=0.00..1845.00 rows=100000 width=35) (actual time=0.015..8.500 rows=100000 loops=1)

Dòng này chứa hai thế giới hoàn toàn tách biệt: Thế giới ước lượng (bên trái) và Thế giới thực tế (bên phải).

                      THẾ GIỚI ƯỚC LƯỢNG                   THẾ GIỚI THỰC TẾ
                    (Planner tính toán trước)             (Khi chạy thật bằng ANALYZE)
                   ┌─────────────────────────┐           ┌────────────────────────────┐
-> Seq Scan ...   ( cost=0.00 .. 1845.00     │          ( actual time=0.015 .. 8.500  │
                   │  │           │          │           │   │            │           │
                   │  │           └─ Total   │           │   │            └─ Total    │
                   │  └─ Startup             │           │   └─ Startup               │
                   │     Cost        Cost    │           │      Time (ms)    Time (ms)│
                   │                         │           │                            │
                   │ rows=100000 width=35 )  │           │ rows=100000 loops=1 )      │
                   └─────────────────────────┘           └────────────────────────────┘

2.1. Phân tích Thế giới Ước lượng (Cost Model)

Đơn vị tính của cost không phải là mili-giây, mà là điểm chi phí trừu tượng (Cost Units) do Planner quy đổi dựa trên việc đọc đĩa và CPU:

• 0.00 (Startup Cost): Chi phí để chuẩn bị sẵn sàng trước khi dòng đầu tiên có thể được trả về. Với Seq Scan, con số này bằng 0.00 vì đọc được dòng nào là trả ngay dòng đó. Nhưng với node Sort, Startup Cost sẽ rất lớn vì nó phải gom đủ 100% dữ liệu rồi sắp xếp xong mới trả được dòng đầu tiên.

• 1845.00 (Total Cost): Tổng chi phí ước tính để đọc hết toàn bộ dữ liệu của node này.

• rows=100000: Planner đoán node này sẽ sinh ra 100.000 dòng.

• width=35: Dung lượng trung bình của mỗi dòng là 35 bytes.

2.2. Phân tích Thế giới Thực tế (Actual Metrics)

• actual time=0.015..8.500: Thời gian thực tế đo bằng mili-giây (ms):

  • 0.015 ms: Thời điểm dòng đầu tiên được trả về.

  • 8.500 ms: Thời điểm toàn bộ công việc của node này hoàn tất.

• rows=100000: Số lượng dòng thực tế mà node này đã trả ra.

• loops=1: Số lần node này bị lặp lại.

  Quy tắc tính toán với loops > 1: Nếu một node nằm trong vòng lặp của Nested Loop Join và có loops=100, thì actual time và rows hiển thị trên màn hình là giá trị trung bình cho mỗi vòng lặp.

  Tổng thời gian thực teˆˊ=actual time×loops

  Tổng soˆˊ doˋng thực teˆˊ=rows×loops

3. Chỉ số vàng BUFFERS: Bóc tách I/O đĩa và RAM Cache

Thêm tùy chọn BUFFERS là cách duy nhất để biết câu lệnh của bạn đang "hành hạ" ổ đĩa hay đang chạy êm ái trên RAM.

Ví dụ kết quả có chứa Buffers:

Plaintext

Buffers: shared hit=854 read=120 written=5

Mỗi đơn vị Buffer tương ứng với 1 Data Page (8 KB):

• shared hit: Số lượng block 8KB được tìm thấy và đọc trực tiếp từ bộ nhớ đệm RAM (shared_buffers). Thao tác này có độ trễ cực thấp (tính bằng micro-giây).

• read: Số lượng block 8KB không có sẵn trên RAM (Cache Miss) và buộc phải yêu cầu hệ điều hành đọc từ ổ cứng (Disk/NVMe). Đây là nguyên nhân số 1 gây chậm truy vấn.

• dirtied: Số lượng block trên RAM bị câu lệnh này sửa đổi làm dữ liệu bị bẩn (Dirty Page).

• written: Số lượng dirty block bị node này ép xả thẳng xuống đĩa cứng.

Công thức tính dung lượng I/O:

Dung lượng đọc từ RAM=shared hit×8 KB

Dung lượng đọc từ Đı˜a=read×8 KB

Ở ví dụ trên: Đã đọc 854×8 KB≈6.8 MB từ RAM và 120×8 KB≈960 KB từ Đĩa.

4. Các kiểu quét dữ liệu cốt lõi (Scan Types)

Khi truy vấn dữ liệu từ một bảng, bạn sẽ bắt gặp 4 kiểu Scan cơ bản sau trong plan:

4.1. Sequential Scan (Seq Scan)

• Quét tuần tự toàn bộ Data Pages của bảng từ đầu đến cuối file.

• Tốt khi: Cần đọc một lượng lớn dữ liệu (> 20-30% bảng), hoặc bảng quá nhỏ (dưới vài trăm dòng).

• Xấu khi: Bảng hàng triệu dòng mà chỉ cần tìm 1 vài bản ghi nhưng thiếu index.

4.2. Index Scan

• Duyệt qua cây B-Tree Index để tìm ctid, sau đó dùng ctid nhảy sang đọc Heap Page tương ứng để lấy dữ liệu dòng.

• Tối ưu khi: Lọc lấy một lượng rất nhỏ các dòng rời rạc (High Selectivity).

4.3. Index Only Scan

• Toàn bộ các cột cần lấy (trong cả mệnh đề SELECT lẫn WHERE) đều nằm trọn trong trang lá của Index (nhờ Covering Index hoặc INCLUDE đã học ở Bài 22).

• Engine hoàn toàn không cần chạm vào bảng Heap (Heap Fetches: 0). Đây là kiểu quét nhanh nhất.

4.4. Bitmap Index Scan kết hợp Bitmap Heap Scan

Thường xuất hiện khi bạn query một lượng dữ liệu trung bình (khoảng 2% – 15% bảng), hoặc khi kết hợp nhiều điều kiện OR / AND giữa các index khác nhau.

QUY TRÌNH 2 BƯỚC CỦA BITMAP SCAN:

Bước 1: Bitmap Index Scan (Trên Index)
  └── Đọc Index và xây dựng một "Bản đồ Bit" (Bitmap) trong RAM.
      Mỗi bit đại diện cho 1 Data Page: Bit=1 (có dòng thỏa mãn), Bit=0 (không có).

Bước 2: Bitmap Heap Scan (Trên Heap Table)
  └── Nhìn vào Bitmap, engine đọc các Data Page có Bit=1 theo thứ tự vật lý tuần tự trên đĩa.
      (Biến thao tác Random I/O rời rạc thành Sequential I/O gom cụm!).

5. Dấu hiệu nhận diện các "Điểm nghẽn" (Red Flags) trong Execution Plan

Khi phân tích một Plan có độ dài hàng chục dòng, hãy tập trung tìm kiếm 4 dấu hiệu cảnh báo nguy hiểm sau:

Dấu hiệu 1: Sự lệch pha nghiêm trọng giữa rows ước lượng và rows thực tế

Plaintext

Seq Scan on orders (cost=0.00..5000.00 rows=1 width=32) (actual time=0.05..120.00 rows=850000 loops=1)

• Planner đoán chỉ có 1 dòng (rows=1), nhưng khi chạy thật lại ra tới 850.000 dòng (rows=850000).

• Hậu quả: Do đoán có 1 dòng, Planner đã chọn thuật toán Nested Loop Join (thay vì Hash Join). Dẫn đến việc câu truy vấn bị treo hàng phút.

• Nguyên nhân: Dữ liệu thống kê của bảng đã quá cũ (Outdated Statistics).

• Cách khắc phục: Chạy ngay lệnh ANALYZE table_name; để cập nhật lại catalog.

Dấu hiệu 2: Sort hoặc Hash bị tràn ra đĩa (Spill to Disk)

Plaintext

Sort Method: external merge  Disk: 45280kB
-- HOẶC --
Buckets: 1024  Batches: 8  Memory Usage: 4096kB

• Bộ nhớ work_mem không đủ chứa dữ liệu sắp xếp hoặc bảng băm, ép engine phải ghi file tạm ra đĩa cứng (external merge).

• Cách khắc phục: Tăng thông số work_mem cho session hoặc toàn hệ thống.

Dấu hiệu 3: Số lượng dòng bị loại bỏ quá lớn (Rows Removed by Filter)

Plaintext

Filter: (status = 'FAILED'::text)
Rows Removed by Filter: 9950000

• Engine phải quét qua gần 10 triệu dòng chỉ để nhặt ra một vài dòng thỏa mãn, vứt bỏ 9.950.000 dòng thừa.

• Cách khắc phục: Cần bổ sung ngay một B-Tree Index hoặc Partial Index trên cột được lọc.

6. Thực hành: Phân tích và Tối ưu một Plan thực tế

Khởi tạo dữ liệu thực nghiệm:

SQL

CREATE SCHEMA IF NOT EXISTS plan_lab;

CREATE TABLE plan_lab.customers (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    country VARCHAR(50) NOT NULL
);

CREATE TABLE plan_lab.orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
);

-- Nạp 10.000 customers và 200.000 orders
INSERT INTO plan_lab.customers (name, country)
SELECT 'User ' || i, (ARRAY['Vietnam', 'Singapore', 'Japan', 'USA'])[1 + (i % 4)]
FROM generate_series(1, 10000) AS i;

INSERT INTO plan_lab.orders (customer_id, amount, created_at)
SELECT 
    (random() * 9999 + 1)::INT,
    (random() * 500)::NUMERIC(10, 2),
    '2026-01-01'::timestamptz + (random() * interval '280 days')
FROM generate_series(1, 200000) AS i;

ANALYZE plan_lab.customers;
ANALYZE plan_lab.orders;

Chạy truy vấn khi CHƯA có Index hỗ trợ:

SQL

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.name, o.amount, o.created_at
FROM plan_lab.customers c
JOIN plan_lab.orders o ON c.id = o.customer_id
WHERE c.country = 'Vietnam' 
  AND o.created_at >= '2026-10-01'
ORDER BY o.amount DESC
LIMIT 10;

Bản kế hoạch khi chưa tối ưu:

Plaintext

Limit  (cost=4320.15..4320.18 rows=10 width=45) (actual time=38.450..38.455 rows=10 loops=1)
  Buffers: shared hit=1320
  ->  Sort  (cost=4320.15..4332.65 rows=5000 width=45) (actual time=38.448..38.451 rows=10 loops=1)
        Sort Key: o.amount DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Hash Join  (cost=175.00..4180.00 rows=5000 width=45) (actual time=1.850..35.200 rows=4850 loops=1)
              Hash Cond: (o.customer_id = c.id)
              Buffers: shared hit=1320
              ->  Seq Scan on orders o  (cost=0.00..3850.00 rows=20000 width=26) (actual time=0.040..28.500 rows=19800 loops=1)
                    Filter: (created_at >= '2026-10-01 00:00:00+00'::timestamptz)
                    Rows Removed by Filter: 180200
              ->  Hash  (cost=143.75..143.75 rows=2500 width=19) (actual time=1.750..1.750 rows=2500 loops=1)
                    Buckets: 4096  Batches: 1  Memory Usage: 152kB
                    ->  Seq Scan on customers c  (cost=0.00..143.75 rows=2500 width=19) (actual time=0.015..1.200 rows=2500 loops=1)
                          Filter: (country = 'Vietnam'::text)
                          Rows Removed by Filter: 7500
Total Runtime: 38.650 ms

Điểm nghẽn phát hiện qua Plan:

1. Seq Scan on orders mất tới 28.5 ms vì phải duyệt 200.000 dòng để lọc ngày, loại bỏ 180.200 dòng thừa (Rows Removed by Filter: 180200).

2. Seq Scan on customers cũng phải duyệt toàn bộ bảng và loại bỏ 7.500 dòng thừa.

3. Không có index trên Foreign Key orders(customer_id).

Tiến hành Tối ưu hóa:

Tạo 2 Index chính xác theo các quy tắc đã học ở Bài 22:

SQL

-- 1. Index hỗ trợ lọc quốc gia trên customers
CREATE INDEX idx_customers_country ON plan_lab.customers(country);

-- 2. Index hỗ trợ lọc ngày kết hợp Covering Index trên orders
CREATE INDEX idx_orders_created_at_covering 
ON plan_lab.orders (created_at) 
INCLUDE (customer_id, amount);

Chạy lại EXPLAIN (ANALYZE, BUFFERS):

Plaintext

Limit  (cost=215.10..215.12 rows=10 width=45) (actual time=4.120..4.124 rows=10 loops=1)
  Buffers: shared hit=340
  ->  Sort  (cost=215.10..227.22 rows=4850 width=45) (actual time=4.118..4.120 rows=10 loops=1)
        Sort Key: o.amount DESC
        Sort Method: top-N heapsort  Memory: 26kB
        ->  Nested Loop  (cost=0.57..198.40 rows=4850 width=45) (actual time=0.065..3.200 rows=4850 loops=1)
              Buffers: shared hit=340
              ->  Bitmap Heap Scan on customers c  (cost=0.29..45.10 rows=2500 width=19) (actual time=0.035..0.450 rows=2500 loops=1)
                    Recheck Cond: (country = 'Vietnam'::text)
                    ->  Bitmap Index Scan on idx_customers_country  (cost=0.00..0.15 rows=2500 width=0)
              ->  Index Only Scan using idx_orders_created_at_covering on orders o  (cost=0.28..0.55 rows=2 width=26)
                    Index Cond: (created_at >= '2026-10-01 00:00:00+00'::timestamptz)
Total Runtime: 4.210 ms

Kết quả sau khi tối ưu:

• Thời gian thực thi giảm từ 38.6 ms xuống còn 4.2 ms (nhanh hơn gần 10 lần).

• Số lượng block đọc từ RAM (shared hit) giảm từ 1320 xuống còn 340.

• Phép Seq Scan nặng nề trên bảng orders đã biến thành Index Only Scan.

7. Tóm tắt & Bài tiếp theo

• Đừng tối ưu mù quáng; hãy luôn dùng EXPLAIN (ANALYZE, BUFFERS) làm căn cứ kỹ thuật.

• cost là điểm ước lượng lý thuyết của Planner; actual time (ms) và rows là số liệu thực tế khi chạy lệnh.

• Khi loops > 1, hãy nhớ nhân actual time và rows với số loops để ra tổng giá trị thật.

• Chỉ số Buffers giúp đo lường chính xác tác động I/O: shared hit (đọc từ RAM) vs read (đọc từ đĩa).

• Chú ý bắt các dấu hiệu nguy hiểm: Rows Removed by Filter quá cao, lệch pha ước lượng (cần ANALYZE), hoặc Sort/Hash tràn ra đĩa.

Bài 26 xem tiếp: Query Planner & Statistics: Vai trò của ANALYZE, pg_statistic và Stored Statistics — tại sao Planner lại đoán được bảng có 100.000 dòng? Chúng ta sẽ khám phá bên trong "bộ não" của Query Planner: cách PostgreSQL lấy mẫu dữ liệu (Data Sampling), biểu đồ tần suất (Histogram) trong pg_stats, và cách dùng Extended Statistics (CREATE STATISTICS) để giải quyết bài toán tương quan đa cột.