The execution plan
Reading the output of EXPLAIN and EXPLAIN ANALYZE: the tree of operations, sequential or index scans, joins, sorting, cost, estimated versus actual rows and statistics.
- 25 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- Scenarios
A few cards from the deck
Execution plan: what is it?
Show the answer
The tree of operations (scans, joins, sorts, aggregates) the database picked to answer an SQL query, which says how it will fetch the data.
SQL says what to fetch, never how. The plan is that “how”: the same SELECT can run in several ways, at very different speeds.
What is the difference between EXPLAIN and EXPLAIN ANALYZE?
- ANALYZE runs the query and adds the actual times and rows
- ANALYZE recomputes the table statistics before displaying the plan
- EXPLAIN runs the query, ANALYZE just displays it
- ANALYZE automatically adds missing indexes
Show the answer
ANALYZE runs the query and adds the actual times and rows
EXPLAIN shows the planned plan without running anything. EXPLAIN ANALYZE really runs it: you then compare estimated rows with actual rows to spot bad estimates. The ANALYZE command alone refreshes statistics.
The optimizer picks its plan from … about the tables: row counts, value distributions. When stale, they lead to a bad plan.
Show the answer
The optimizer picks its plan from statistics about the tables: row counts, value distributions. When stale, they lead to a bad plan.
After a big load or a big delete they must be refreshed (ANALYZE in PostgreSQL) so that estimates become accurate again.
EXPLAIN ANALYZE: what is it?
Show the answer
A command that really runs the query, then displays the plan with, for each operation, the time spent and the number of rows actually processed.
EXPLAIN alone runs nothing and only gives estimates. Careful: on an UPDATE or a DELETE, EXPLAIN ANALYZE really modifies the data.
You want to see the actual plan of a DELETE on the production database. How do you do it without losing data?
- Run EXPLAIN ANALYZE as is: it only reads
- Run EXPLAIN ANALYZE between BEGIN and ROLLBACK
- Add LIMIT 1 so only one row gets deleted
- Use EXPLAIN ANALYZE at READ UNCOMMITTED
Show the answer
Run EXPLAIN ANALYZE between BEGIN and ROLLBACK
EXPLAIN ANALYZE runs the query: the DELETE really happens. Inside a transaction later cancelled by ROLLBACK, you get the actual plan without keeping the deletion.