Menu

EXPLAIN ANALYZE trong PostgreSQL: cách đọc query plan

EXPLAIN cho thấy kế hoạch PostgreSQL chọn cho một truy vấn, còn EXPLAIN ANALYZE chạy truy vấn và thêm số hàng và thời gian thực tế của từng bước. Học cách đọc cây kế hoạch và tên các node thường gặp.

Trang này có trình soạn thảo chạy trực tiếp. Sửa, chạy và xem đầu ra ngay lập tức.

EXPLAIN query cho thấy kế hoạch thực thi (query plan) mà PostgreSQL chọn cho một truy vấn: một cây gồm các bước (node) với cost và số hàng ước lượng. EXPLAIN ANALYZE query còn chạy truy vấn và thêm thời gian, số hàng và số vòng lặp thực tế của mọi node. Hãy đọc cây từ node thụt vào sâu nhất ra ngoài: các node bên trong đưa hàng của chúng lên node phía trên.

Không có index trên email, nên kế hoạch là một Seq Scan: PostgreSQL đọc mọi hàng và giữ những hàng qua được Filter. Rows Removed by Filter cho thấy phần công sức bị lãng phí. Thời gian mỗi lần chạy mỗi khác; hình dạng của kế hoạch quan trọng hơn các con số.

Đọc một dòng kế hoạch

Mỗi node in ra một dòng có dạng này:

Seq Scan on users  (cost=STARTUP..TOTAL rows=N width=W) (actual time=FIRST..LAST rows=N loops=L)
PhầnÝ nghĩa
cost=STARTUP..TOTALcông sức ước lượng trước hàng đầu tiên, và cho tất cả các hàng, theo đơn vị của bộ lập kế hoạch (không phải mili giây)
rows (đầu tiên)số hàng node được ước lượng sẽ trả về
widthkích thước trung bình ước lượng của một hàng, tính bằng byte
actual timesố mili giây tới hàng đầu tiên và tới hàng cuối cùng, cho mỗi vòng lặp
rows (thực tế)số hàng được trả về, cho mỗi vòng lặp
loopssố lần node đã chạy; nhân thời gian và số hàng với giá trị này

Cost của node cha đã bao gồm các node con. Planning Time và Execution Time ở cuối là tổng cho cả câu lệnh.

Nhận biết các node thường gặp

EXPLAIN (COSTS OFF) ẩn các con số, nên hình dạng của kế hoạch dễ nhìn hơn:

Đọc từ bên trong ra. Cả hai bảng đều được quét và các khách hàng đã lọc được đưa vào một Hash. Hash Join dò nó bằng từng đơn hàng, và GroupAggregate ở trên cùng đếm kết quả. Những node bạn sẽ gặp nhiều nhất:

NodeViệc nó làm
Seq Scanđọc toàn bộ bảng
Index Scantìm hàng qua một index, rồi đọc từng hàng từ bảng
Index Only Scantrả lời từ index mà không cần vào bảng
Bitmap Index Scan + Bitmap Heap Scangom vị trí các hàng khớp từ một index, rồi đọc chúng theo thứ tự trong bảng
Nested Loopvới mỗi hàng bên ngoài, tìm các hàng khớp ở đầu vào bên trong; tốt khi phía ngoài nhỏ
Hash Join + Hashdựng bảng băm từ một đầu vào, rồi dò nó bằng đầu vào kia
Merge Joinjoin hai đầu vào đã được sắp xếp theo khóa join
Sort, Incremental Sortsắp xếp hàng cho ORDER BY, DISTINCT hoặc merge join
HashAggregate, GroupAggregateGROUP BY bằng bảng băm, hoặc trên đầu vào đã sắp xếp
Limitdừng sau N hàng
Gathergom hàng từ các worker chạy song song

Tìm ước lượng số hàng sai

Phép kiểm tra hữu ích nhất trong EXPLAIN ANALYZE là so rows ước lượng với rows thực tế trên cùng một node. Khi chúng chênh nhau xa, bộ lập kế hoạch đã chọn kế hoạch dựa trên con số sai, thường vì thống kê bị thiếu hoặc đã cũ:

Phép quét trả về 10 hàng thực tế, nhưng ước lượng cao hơn nhiều vì bảng chưa được phân tích. Thêm ANALYZE tasks; trước EXPLAIN và ước lượng sẽ tiến gần tới 10. Autovacuum phân tích bảng ở chế độ nền, nhưng hãy tự chạy ANALYZE sau khi nạp dữ liệu hàng loạt (xem VACUUM và autovacuum).

EXPLAIN một thay đổi dữ liệu mà không giữ lại

EXPLAIN ANALYZE thực thi câu lệnh, nên một lệnh UPDATE hoặc DELETE sẽ thay đổi các hàng. Hãy bọc nó trong một transaction rồi rollback:

Cả 1000 hàng vẫn còn đó sau khi rollback.

Xem I/O với BUFFERS và các tùy chọn khác

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;        -- pages read from cache (hit) and disk (read)
EXPLAIN (ANALYZE, BUFFERS, SETTINGS) SELECT ...; -- also list changed planner settings
EXPLAIN (FORMAT JSON) SELECT ...;              -- machine-readable, for plan visualizers
EXPLAIN (ANALYZE, TIMING OFF) SELECT ...;      -- row counts without per-node timing overhead

BUFFERS cho thấy I/O: một node có nhiều trang read đã phải đọc từ đĩa. PostgreSQL 17 thêm SERIALIZE (chi phí chuyển các hàng đầu ra thành văn bản) và MEMORY (bộ nhớ của bộ lập kế hoạch). Hướng dẫn EXPLAIN chính thức đi qua nhiều kế hoạch khác.

Thử kế hoạch bằng thiết lập của bộ lập kế hoạch

Để xem PostgreSQL sẽ dùng kế hoạch nào khi không có một phương pháp, hãy tắt phương pháp đó cho phiên của bạn. Chỉ làm vậy khi thử nghiệm, không bao giờ trong code production:

Với 100 hàng, bộ lập kế hoạch thích Seq Scan hơn; với enable_seqscan = off, kế hoạch hiển thị Index Scan using items_pkey. So sánh thời gian của hai lần EXPLAIN ANALYZE để xem index có giúp ích không. RESET enable_seqscan; khôi phục giá trị mặc định.

Câu hỏi thường gặp

EXPLAIN và EXPLAIN ANALYZE khác nhau thế nào?

EXPLAIN chỉ lập kế hoạch cho truy vấn và hiển thị các ước lượng. EXPLAIN ANALYZE còn thực thi nó và thêm thời gian, số hàng và số vòng lặp thực tế của từng node, nên nó mất đúng bằng thời gian chạy truy vấn.

cost trong kế hoạch của PostgreSQL nghĩa là gì?

Cost là ước lượng của bộ lập kế hoạch theo một đơn vị tùy ý, được quy đổi sao cho đọc tuần tự một trang tốn 1 (seq_page_cost). Nó hữu ích để so sánh các kế hoạch của cùng một truy vấn, chứ không phải là thời gian tính bằng mili giây.

EXPLAIN ANALYZE có thay đổi dữ liệu không?

Có, với INSERT, UPDATE, DELETE và MERGE, nó thực hiện thay đổi. Hãy chạy nó giữa BEGIN và ROLLBACK để đo mà không giữ lại kết quả.

Vì sao Seq Scan được chọn dù đã có index?

Vì bộ lập kế hoạch ước lượng cách đó rẻ hơn: bảng nhỏ, truy vấn trả về một phần lớn số hàng, hoặc điều kiện không dùng được index. Thống kê cũ cũng có thể đánh lừa nó, và ANALYZE sẽ sửa điều đó.

Hình minh họa các ngôn ngữ lập trình của Coddy

Học lập trình cùng Coddy

Bắt đầu