Làm ngay trong 5 phút: Chạy EXPLAIN ANALYZE lần đầu
Mình nhớ lần đầu vào công ty, senior toss cho mình một ticket: “Query này chạy 8 giây, fix nó đi.” Ngồi nhìn câu SQL mà không biết bắt đầu từ đâu. Sau đó mới biết đến EXPLAIN ANALYZE — công cụ đơn giản nhất nhưng mạnh nhất để debug query PostgreSQL.
Thử ngay bằng cách chạy lệnh này trên bất kỳ query nào bạn đang có:
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
AND status = 'pending';
PostgreSQL sẽ trả về một đống text trông như thế này:
Seq Scan on orders (cost=0.00..1845.00 rows=12 width=156) (actual time=0.043..18.234 rows=12 loops=1)
Filter: ((user_id = 42) AND ((status)::text = 'pending'::text))
Rows Removed by Filter: 89988
Planning Time: 0.215 ms
Execution Time: 18.251 ms
Chưa hiểu gì? Bình thường. Đọc tiếp — mình sẽ giải thích từng phần.
Đọc execution plan: Hiểu PostgreSQL đang làm gì
Cấu trúc của một execution plan
Execution plan đọc từ trong ra ngoài, từ dưới lên trên. Node nằm sâu nhất chạy trước. Mỗi dòng có dạng:
[Loại scan] (cost=X..Y rows=N width=W) (actual time=A..B rows=R loops=L)
- cost=X..Y: Chi phí ước tính — X là chi phí khởi tạo, Y là tổng chi phí (đơn vị tương đối, không phải millisecond)
- rows=N: Số hàng PostgreSQL dự đoán sẽ trả về
- actual time=A..B: Thời gian thực tế (millisecond) — A là thời gian row đầu tiên, B là tổng
- rows=R: Số hàng thực tế trả về
- loops=L: Node này chạy bao nhiêu lần — nhân với actual time để ra tổng thực sự
Các loại scan — cái nào nhanh, cái nào chậm
Cái mình check đầu tiên khi mở plan là loại scan. Bốn kiểu cần nắm:
- Seq Scan: Quét toàn bộ bảng từ đầu đến cuối. Thấy cái này trên bảng triệu row là phải lo ngay.
- Index Scan: Dùng index để tìm row. Nhanh hơn nhiều với bảng lớn.
- Index Only Scan: Tốt nhất — lấy dữ liệu trực tiếp từ index, không cần đọc heap.
- Bitmap Heap Scan: Kết hợp nhiều index, hiệu quả khi WHERE có nhiều điều kiện.
Dấu hiệu query đang có vấn đề
Nhìn vào hai con số này và so sánh:
-- Dự đoán: rows=1, Thực tế: rows=50000
-- → Statistics lỗi thời, cần ANALYZE
-- Dự đoán: rows=50000, Thực tế: rows=1
-- → Planner chọn sai plan, query scan thừa
Khi hai con số này lệch nhau hơn 10 lần, planner đang chọn sai execution plan — thường do statistics lỗi thời. Chạy ANALYZE orders; là bước đầu tiên để fix.
Thực hành: Debug một query chậm từ đầu đến cuối
Setup môi trường test
-- Tạo bảng test với 1 triệu row
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT,
product_id INT,
status VARCHAR(20),
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO orders (user_id, product_id, status, total)
SELECT
(random() * 10000)::INT,
(random() * 1000)::INT,
CASE (random() * 3)::INT
WHEN 0 THEN 'pending'
WHEN 1 THEN 'completed'
ELSE 'cancelled'
END,
(random() * 1000)::DECIMAL(10,2)
FROM generate_series(1, 1000000);
Query chậm — không có index
EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';
Kết quả:
Seq Scan on orders (cost=0.00..23334.00 rows=5 width=28) (actual time=2.341..145.672 rows=48 loops=1)
Filter: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
Rows Removed by Filter: 999952
Execution Time: 145.891 ms
Quét 1 triệu row để lấy 48 row — lãng phí hoàn toàn. Thêm index:
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
-- Chạy lại
EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';
Kết quả sau khi thêm index:
Index Scan using idx_orders_user_created on orders
(cost=0.42..18.63 rows=48 width=28) (actual time=0.052..0.312 rows=48 loops=1)
Index Cond: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
Execution Time: 0.387 ms
Từ 145ms xuống còn 0.4ms. Đó là sức mạnh của index đúng chỗ.
Nâng cao: EXPLAIN với các option hữu ích
EXPLAIN (ANALYZE, BUFFERS) — Xem I/O thực tế
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10;
Option BUFFERS cho thấy bao nhiêu block được đọc từ disk (read) vs từ cache (hit). Số read cao nghĩa là data chưa được cache — đây là I/O bottleneck, không phải CPU.
Phân tích JOIN: Hash Join vs Nested Loop vs Merge Join
PostgreSQL có ba chiến lược JOIN, planner tự chọn dựa trên kích thước bảng, index hiện có và statistics:
- Hash Join: Build hash table từ bảng nhỏ hơn, probe với bảng lớn. Hiệu quả khi không có index trên join column.
- Nested Loop: Với mỗi row của outer table, tìm trong inner table. Nhanh khi inner table nhỏ hoặc có index tốt.
- Merge Join: Yêu cầu cả hai bảng được sort theo join key. Hiệu quả nhất khi cả hai đã có index sorted.
-- Tắt một strategy để so sánh performance
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_hashjoin = on;
pgBadger và auto_explain: Tìm query chậm tự động
EXPLAIN từng query thủ công tốn thời gian. Bật auto_explain để PostgreSQL tự log query chậm:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000 # Log query chạy > 1 giây
auto_explain.log_analyze = true
auto_explain.log_buffers = true
Sau đó dùng pgBadger để parse log và tạo report HTML:
pgbadger /var/log/postgresql/postgresql-14-main.log -o report.html
Tips thực tế từ kinh nghiệm làm việc
1. Luôn check estimated rows vs actual rows
Nếu planner đoán sai nhiều, chạy VACUUM ANALYZE để refresh statistics. Bảng có INSERT/DELETE/UPDATE liên tục? Tăng tần suất autovacuum:
ALTER TABLE orders SET (
autovacuum_analyze_scale_factor = 0.01, -- ANALYZE khi 1% data thay đổi
autovacuum_vacuum_scale_factor = 0.05
);
2. Index không phải lúc nào cũng giúp
PostgreSQL tự chọn Seq Scan khi ước tính query trả về nhiều hơn ~10-15% tổng số row. Lúc đó quét toàn bộ bảng thực sự nhanh hơn nhảy qua index. Đừng cố force — planner hầu như luôn đúng trong trường hợp này.
3. Composite index — thứ tự column quan trọng
-- Index này hiệu quả cho: WHERE user_id = ? AND status = ?
-- Hoặc chỉ: WHERE user_id = ?
CREATE INDEX idx_user_status ON orders(user_id, status);
-- Nhưng KHÔNG hiệu quả cho: WHERE status = ? (không có user_id)
-- Cần index riêng: CREATE INDEX idx_status ON orders(status);
4. Covering index để đạt Index Only Scan
Query chỉ SELECT một số column nhất định? Tạo covering index để PostgreSQL không cần đọc heap:
-- Query hay chạy:
SELECT id, total FROM orders WHERE user_id = 42 AND status = 'pending';
-- Covering index (INCLUDE các column SELECT):
CREATE INDEX idx_orders_covering
ON orders(user_id, status)
INCLUDE (id, total);
5. Dùng explain.depesz.com để visualize
Copy output của EXPLAIN ANALYZE và paste vào explain.depesz.com hoặc explain.dalibo.com để xem visualization — đặc biệt hữu ích với plan phức tạp nhiều node.
6. Đừng tối ưu vội — profile trước
Sai lầm mình hay thấy nhất: thêm index lung tung mà chưa đo. Mỗi index tốn dung lượng và làm chậm INSERT/UPDATE/DELETE. Chỉ thêm khi EXPLAIN ANALYZE xác nhận đó là bottleneck thực sự — và đo lại sau khi thêm để confirm.
