Bài 19: Đọc Hiểu Advanced Execution Plan – JSON Format Và Visualization
"Execution Plan không chỉ là văn bản – hãy khám phá sức mạnh của JSON và công cụ trực quan hóa!"
1. Vấn Đề: Query Phức Tạp Với Execution Plan Khó Hiểu
Scenario:
Bảng transactions (50 triệu dòng) với cấu trúc:
transaction_id(PK)user_idamounttransaction_date
Query:
SELECT
user_id,
SUM(amount) AS total_amount,
COUNT(*) AS transaction_count
FROM transactions
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY user_id
HAVING SUM(amount) > 1000;
Kết quả: Thời gian thực thi ~45 giây, Execution Plan dạng text khó phân tích.
2. Phân Tích Execution Plan Dạng JSON
Bước 1: Chạy EXPLAIN (ANALYZE, FORMAT JSON) (PostgreSQL)
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT ...;
Kết quả:
[
{
"Plan": {
"Node Type": "Aggregate",
"Strategy": "Sorted",
"Partial Mode": "Finalize",
"Parallel Aware": false,
"Startup Cost": 50000.00,
"Total Cost": 70000.00,
"Plan Rows": 100000,
"Plan Width": 20,
"Actual Startup Time": 1000.123,
"Actual Total Time": 45000.456,
"Actual Rows": 50000,
"Actual Loops": 1,
"Plans": [
{
"Node Type": "Sort",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Startup Cost": 30000.00,
"Total Cost": 35000.00,
"Plan Rows": 1000000,
"Plan Width": 12,
"Actual Startup Time": 500.123,
"Actual Total Time": 30000.456,
"Actual Rows": 1000000,
"Actual Loops": 1,
"Sort Key": ["user_id"],
"Sort Method": "external merge",
"Sort Space Used": 500000,
"Sort Space Type": "Disk",
"Plans": [
{
"Node Type": "Seq Scan",
"Parent Relationship": "Outer",
"Parallel Aware": false,
"Relation Name": "transactions",
"Alias": "transactions",
"Startup Cost": 0.00,
"Total Cost": 20000.00,
"Plan Rows": 1000000,
"Plan Width": 12,
"Actual Startup Time": 0.012,
"Actual Total Time": 10000.123,
"Actual Rows": 1000000,
"Actual Loops": 1,
"Filter": "(transaction_date >= '2023-01-01'
AND transaction_date <= '2023-12-31')",
"Rows Removed by Filter": 4000000
}
]
}
]
}
}
]
Bước 2: Giải Mã JSON Plan
Node Type: Loại hoạt động (Aggregate, Sort, Seq Scan).
Cost: Chi phí ước lượng (Startup Cost, Total Cost).
Actual Time: Thời gian thực thi (Startup, Total).
Sort Method: Cách sắp xếp (external merge trên Disk).
Filter: Điều kiện lọc dữ liệu.
Phát Hiện Vấn Đề:
Full Scan trên
transactions: Tốn 10 giây để quét 50 triệu dòng.Sort trên Disk: Tốn 30 giây để sắp xếp 1 triệu dòng.
Aggregate: Tốn 5 giây để tổng hợp dữ liệu.
3. Tối Ưu: Sử Dụng Index và Parallel Query
Bước 1: Tạo Index Trên transaction_date và user_id
CREATE INDEX idx_transactions_date_user ON transactions(transaction_date, user_id);
Bước 2: Kích Hoạt Parallel Query
SET max_parallel_workers_per_gather = 4;
Bước 3: Execution Plan Sau Khi Tối Ưu
[
{
"Plan": {
"Node Type": "Gather",
"Parallel Aware": true,
"Startup Cost": 1000.00,
"Total Cost": 15000.00,
"Plan Rows": 50000,
"Plan Width": 20,
"Actual Startup Time": 200.123,
"Actual Total Time": 5000.456,
"Actual Rows": 50000,
"Actual Loops": 1,
"Workers Planned": 4,
"Workers Launched": 4,
"Plans": [
{
"Node Type": "HashAggregate",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Startup Cost": 800.00,
"Total Cost": 12000.00,
"Plan Rows": 50000,
"Plan Width": 20,
"Actual Startup Time": 100.123,
"Actual Total Time": 3000.456,
"Actual Rows": 50000,
"Actual Loops": 4,
"Group Key": ["user_id"],
"Plans": [
{
"Node Type": "Index Scan",
"Parent Relationship": "Outer",
"Parallel Aware": true,
"Relation Name": "transactions",
"Alias": "transactions",
"Index Name": "idx_transactions_date_user",
"Startup Cost": 0.42,
"Total Cost": 10000.00,
"Plan Rows": 1000000,
"Plan Width": 12,
"Actual Startup Time": 0.012,
"Actual Total Time": 2000.123,
"Actual Rows": 1000000,
"Actual Loops": 4,
"Index Cond": "(transaction_date >= '2023-01-01' AND
transaction_date <= '2023-12-31')"
}
]
}
]
}
}
]
Kết Quả:
Index Scan thay thế Full Scan: Giảm thời gian quét từ 10 giây → 2 giây.
Parallel HashAggregate: 4 workers xử lý song song → Giảm thời gian tổng hợp từ 5 giây → 3 giây.
Tổng thời gian: Từ 45 giây → 5 giây (89% cải thiện).
4. Tổng Kết
| Chỉ Số | Trước Khi Tối Ưu | Sau Khi Tối Ưu |
| Thời Gian Quét | 10,000 ms | 2,000 ms |
| Thời Gian Sort | 30,000 ms | 0 ms |
| Thời Gian Aggregate | 5,000 ms | 3,000 ms |
| Tổng Thời Gian | 45,000 ms | 5,000 ms |
| Phương Pháp Scan | Seq Scan | Index Scan |
Lý Do Hiệu Quả:
Index hỗ trợ lọc và truy cập dữ liệu nhanh.
Parallel Query tận dụng đa CPU.
5. Bài Tập Thực Hành
Dataset Mẫu:
CREATE TABLE logs (
log_id BIGSERIAL PRIMARY KEY,
event_type VARCHAR(50),
event_time TIMESTAMP,
user_agent TEXT
);
-- Insert 20 triệu dòng dữ liệu
Yêu Cầu:
Chạy query:
SELECT event_type, COUNT(*) FROM logs WHERE event_time BETWEEN '2024-01-01' AND '2024-06-30' GROUP BY event_type HAVING COUNT(*) > 1000;Xuất Execution Plan dạng JSON và phân tích.
Tối ưu query bằng index và parallel query.
Câu Hỏi:
- Tại sao JSON format lại hữu ích hơn text format trong việc phân tích Execution Plan?
6. Mở Rộng & Thảo Luận
Công Cụ Trực Quan Hóa Execution Plan
PEV (PostgreSQL Explain Visualizer):
Upload file JSON → Hiển thị plan dạng cây.
pgAdmin:
- Hỗ trợ xem Execution Plan dạng đồ họa.
DBeaver:
- Hiển thị plan chi tiết với các thông số I/O và thời gian.
Case Study: Phân Tích I/O và Worker Processes
Buffer Usage:
"Shared Hit Blocks": 5000, "Shared Read Blocks": 1000Shared Hit Blocks: Số block đọc từ cache.
Shared Read Blocks: Số block đọc từ disk.
Worker Processes:
"Workers Planned": 4, "Workers Launched": 4- Số lượng workers thực tế được sử dụng.
Edge Case: Khi Nào JSON Plan Không Đủ?
Query quá phức tạp: Cần kết hợp với log query và monitoring tools (ví dụ: pg_stat_activity).
Vấn đề về tài nguyên: Cần kiểm tra hệ thống (CPU, RAM, Disk I/O).
Kết Luận
JSON format và công cụ trực quan hóa giúp bạn "nhìn thấu" Execution Plan, từ đó tối ưu query hiệu quả hơn. Trong ví dụ này, thời gian query giảm từ 45 giây xuống 5 giây nhờ index và parallel query. Ở bài cuối cùng (Bài 20), chúng ta sẽ bàn về scale database và chuyển sang kiến trúc phân tán khi tối ưu không còn đủ!
👉 Bài Tập Về Nhà: Tải dataset tại đây, sử dụng PEV để phân tích Execution Plan và chia sẻ kết quả!