How do you read an execution plan with EXPLAIN and EXPLAIN ANALYZE?
How do you read an execution plan with EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the plan the optimizer chose and its estimates. EXPLAIN ANALYZE runs the statement and adds the real timings and row counts, so you can compare estimate against reality.
-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;
-- MySQL 8.0.18+ (tree format with actual timing)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-- Oracle
EXPLAIN PLAN FOR SELECT * FROM orders WHERE customer_id = 42;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- SQL Server
SET STATISTICS IO, TIME ON; -- plus "Include Actual Execution Plan" in SSMS
Warning: EXPLAIN ANALYZE really executes the query. For INSERT/UPDATE/DELETE, wrap it in BEGIN; ... ROLLBACK;.
| Plan item | Meaning |
|---|---|
Seq Scan / Table Scan / ALL |
reads the whole table |
Index Scan / Index Seek |
uses an index to find rows, then reads the table |
Index Only Scan |
answered from the index alone (covering) |
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.