Bắt đầu nhanh (5 phút)
Độ trễ API endpoint của bạn tăng đột biến từ 45ms lên 3.2s vào giờ cao điểm. Đến chín phần mười trường hợp, thủ phạm chính là do một database query bị nghẽn dưới tải lớn. Trong khi các công cụ như MySQL hay MongoDB cung cấp khả năng kiểm tra query, PostgreSQL mang lại cho bạn khả năng quan sát vượt trội về các quyết định của bộ lập lịch (planner). Bạn chỉ cần biết cách đọc EXPLAIN ANALYZE.
Thêm tiền tố EXPLAIN ANALYZE vào trước bất kỳ câu lệnh SQL nào trong psql hoặc database GUI để xem cách Postgres thực sự thực thi query của bạn:
EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
AND created_at >= '2026-01-01';
Cơ sở dữ liệu sẽ thực thi query và trả về một cây thực thi chi tiết:
Seq Scan on orders (cost=0.00..1845.00 rows=1420 width=36) (actual time=0.045..14.821 rows=1530 loops=1)
Filter: ((created_at >= '2026-01-01'::date) AND ((status)::text = 'completed'::text))
Rows Removed by Filter: 85470
Planning Time: 0.182 ms
Execution Time: 15.012 ms
Hãy bắt đầu từ dưới lên. Planning Time (0.182 ms) đại diện cho thời gian optimizer tính toán đường dẫn thực thi tối ưu nhất (chi phí thấp nhất). Execution Time (15.012 ms) đo lường thời gian thực tế cho disk I/O, truy xuất bản ghi và đánh giá bộ lọc. Câu lệnh EXPLAIN thông thường chỉ hiển thị mô hình chi phí lý thuyết. Khi thêm ANALYZE, query sẽ thực sự chạy, trả về thời gian chạy thực tế song song với các ước lượng của planner.
Tìm hiểu chuyên sâu
Execution plan được đọc từ trong ra ngoài và từ dưới lên trên. Các child node sẽ chạy trước, sau đó chuyển các dòng kết quả lên cho parent node phía trên.
Phân tích chi tiết các chỉ số của Node
Mỗi plan node hiển thị hai bộ chỉ số riêng biệt: các giá trị ước lượng theo sau bởi số liệu thực thi thực tế.
- cost=0.00..1845.00: Đơn vị tính toán tùy biến dựa trên số lần nạp disk page và chu kỳ CPU.
0.00là startup cost (thời gian cho đến khi node trả về bản ghi đầu tiên).1845.00là chi phí ước lượng để xử lý toàn bộ các bản ghi khớp điều kiện. - rows=1420: Số bản ghi ước lượng của optimizer, được tính toán từ các phân phối thống kê lưu trong
pg_statistic. - width=36: Dung lượng ước lượng của mỗi bản ghi tính bằng byte.
- actual time=0.045..14.821: Thời gian thực tế tính bằng mili-giây. Bản ghi đầu tiên được xử lý ở 0.045 ms; toàn bộ 1.530 bản ghi hoàn tất ở 14.821 ms.
- rows=1530: Số lượng bản ghi thực tế được trả về. So sánh con số này với rows ước lượng (1.420). Hai số này gần nhau cho thấy số liệu thống kê đang hoạt động chính xác.
- loops=1: Số vòng lặp thực hiện. Nếu một vòng lặp con bên trong nested join chạy 50 lần, hãy nhân thời gian và số rows hiển thị với 50 để tính tổng tài nguyên tiêu thụ.
Các chiến lược quét: Sequential Scan vs Index Scan
Postgres lựa chọn một trong bốn phương pháp scan chính tùy thuộc vào dung lượng bảng, sự hiện diện của index và độ phân tán (cardinality) của cột:
- Seq Scan (Sequential Scan): Quét toàn bộ bảng từ disk theo từng page. Hãy chú ý đến dòng
Rows Removed by Filter: 85470trong plan phía trên. Engine đã phải kiểm tra 87.000 dòng vật lý chỉ để giữ lại 1.530 dòng—gây lãng phí băng thông disk với tỷ lệ loại bỏ lên tới 98.2%. - Index Scan: Duyệt qua B-Tree index để định vị các con trỏ bản ghi cụ thể (TID), sau đó truy xuất trực tiếp từng dòng từ heap page của bảng.
- Bitmap Index Scan / Bitmap Heap Scan: Cầu nối giữa sequential scan và index scan. Đầu tiên, index scan xây dựng một bitmap trong bộ nhớ để đánh dấu các physical page khớp điều kiện. Tiếp đó, heap scan đọc các page này theo thứ tự lưu trữ vật lý, tránh việc di chuyển đầu đọc ngẫu nhiên (random seek thrashing).
- Index Only Scan: Phương thức scan nhanh nhất. Index chứa toàn bộ các cột được yêu cầu, do đó Postgres hoàn toàn bỏ qua việc truy cập vào heap bất cứ khi nào table visibility map xác nhận các page đều sạch.
Kỹ thuật nâng cao
Đọc được node cost mới chỉ giải quyết được một nửa vấn đề. Để xác định chính xác các điểm nghẽn trên production, bạn cần đào sâu vào việc tràn bộ nhớ (memory spill), tỷ lệ trúng buffer (buffer hit) và các trường hợp đặc biệt của planner.
Theo dõi bộ nhớ và Disk I/O với BUFFERS
Luôn thêm tùy chọn BUFFERS khi điều tra các slow query. Nó giúp xác minh dữ liệu đã nằm sẵn trên RAM hay cần phải đọc từ ổ cứng vật lý:
EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'US'
GROUP BY u.email
ORDER BY total_orders DESC
LIMIT 10;
Plan trả về sẽ hiển thị các chỉ số buffer:
Buffers: shared hit=412 read=1895 dirtied=0 written=0
- shared hit (412): Các page được nạp trực tiếp từ
shared_bufferscủa PostgreSQL trong RAM. Rất nhanh và độ trễ gần như bằng 0. - shared read (1895): Các page được nạp từ OS cache hoặc disk. Với kích thước mặc định 8 kB mỗi page, 1.895 lượt đọc tương đương ~14.8 MB băng thông disk hoặc cache. Số lượt read cao phản ánh tình trạng cold cache hoặc thiếu index.
Truy tìm các Sequential Scan tiềm ẩn
Bạn đã tạo index trên cột created_at, nhưng planner vẫn quyết định quét toàn bộ bảng. Dưới đây là ba nguyên nhân phổ biến dẫn đến hành vi này:
1. Bất đồng kiểu dữ liệu và ép kiểu ngầm định (Implicit Casting)
Khi application driver truyền tham số không khớp kiểu dữ liệu—chẳng hạn truyền một chuỗi ký tự vào cột bigint hoặc so sánh các text collation khác nhau—Postgres sẽ áp dụng hàm chuyển đổi kiểu dữ liệu bao bọc lấy cột đó. B-Tree index không thể sử dụng cho biểu thức trừ khi bạn chủ động tạo expression index.
-- Không tối ưu: Hàm bọc ngoài ngăn cản sử dụng B-Tree index
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';
-- Tối ưu: So khớp chính xác tận dụng index tiêu chuẩn trên email
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';
-- Giải pháp thay thế: Tạo expression index để hỗ trợ tìm kiếm không phân biệt hoa thường
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
2. Biến đổi cột trong mệnh đề WHERE
Bao bọc các cột timestamp bằng các hàm ngày tháng sẽ làm mất khả năng tận dụng index. Hãy tránh các biểu thức như WHERE DATE(created_at) = '2026-03-01'. Thay vào đó, hãy sử dụng các khoảng giá trị sargable:
-- Sử dụng index range scan một cách tối ưu
EXPLAIN ANALYZE
SELECT id, total_amount
FROM orders
WHERE created_at >= '2026-03-01 00:00:00'
AND created_at < '2026-03-02 00:00:00';
3. Ngưỡng độ chọn lọc thấp (Low Selectivity)
Query planner sẽ tính toán xem chi phí random I/O có đắt hơn thông lượng đọc tuần tự thô hay không. Nếu điều kiện lọc của bạn khớp hơn 10% đến 20% toàn bộ bảng, việc sequential scan thường thực sự nhanh hơn so với việc nhảy qua lại liên tục trên index. Trên các ổ cứng NVMe tốc độ cao nơi random_page_cost gần bằng seq_page_cost (ví dụ: 1.1), sự cân nhắc này có thể thay đổi, nhưng sequential scan trên tập kết quả lớn vẫn là hành vi hoàn toàn bình thường.
Phát hiện tràn bộ nhớ khi Sort và Hash
Hãy kiểm tra chi tiết của sort node trong kết quả:
Sort Method: external merge Disk: 18432kB
PostgreSQL đặt work_mem mặc định là 4 MB. Khi các thao tác sort hoặc hash table vượt quá mức dung lượng này, engine sẽ ghi các phần dữ liệu tạm thời ra disk, làm sụt giảm nghiêm trọng thông lượng. Tăng work_mem cho session hiện tại sẽ giải quyết được vấn đề tràn bộ nhớ mà không cần khởi động lại toàn bộ database:
SET work_mem = '64MB';
-- Chạy lại query lúc này sẽ cho kết quả:
-- Sort Method: quicksort Memory: 4120kB
Kinh nghiệm thực tế
Việc debug an toàn trên production đòi hỏi các nguyên tắc vận hành hợp lý:
- Bảo vệ dữ liệu thật khi thao tác ghi/sửa:
EXPLAIN ANALYZEthực sự thực thi các câu lệnh. Nếu bạn truyền câu lệnhDELETEhoặcUPDATE, dữ liệu sẽ bị thay đổi ngay lập tức. Hãy bọc các query làm thay đổi dữ liệu trong một transaction block và rollback:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM sessions WHERE expired_at < NOW();
ROLLBACK;
- Làm mới số liệu thống kê đã cũ: Khi rows ước lượng (10) chênh lệch quá lớn so với rows thực tế (50.000), optimizer sẽ chọn nested loop join kém hiệu quả thay vì hash join. Hãy cập nhật lại số liệu thống kê của cột bằng lệnh
ANALYZE orders;để khôi phục ước lượng chính xác cho plan. - Tự động hóa ghi log slow query: Thay vì bắt query thủ công, hãy nạp module
auto_explaintrongpostgresql.confđể tự động ghi log execution plan cho bất kỳ query nào vượt quá ngưỡng SLA hiệu năng của bạn:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
- Trực quan hóa cây thực thi phức tạp: Các query phức tạp kết hợp hơn 15 phép join sẽ tạo ra plan dài hàng trăm dòng. Hãy xuất plan bằng lệnh
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)và nạp vào các công cụ chẩn đoán trực quan như PEV2 hoặc Explain của Dalibo. Những công cụ này sẽ làm nổi bật các node thắt nút cổ chai tốn kém nhất chỉ trong vài giây.
