Database Explain Plan — Vocabulary and Communication
Learn vocabulary for reading and discussing query execution plans: sequential scan, index scan, cost estimates, and joins.
0 / 5 completed
1 / 5
What is a query 'execution plan' (or explain plan) in database vocabulary?
An execution plan (EXPLAIN in PostgreSQL/MySQL, Execution Plan in SQL Server) shows how the database engine will execute a query: table access methods (seq scan vs. index scan), join algorithms, sort operations, and estimated row counts and costs at each step.
2 / 5
What is the difference between a 'sequential scan' and an 'index scan'?
Sequential scan: reads all table pages linearly — efficient for small tables or low-selectivity queries (returning >10–20% of rows). Index scan: traverses the B-tree index to find matching rows — efficient for high-selectivity queries. The optimizer chooses based on row count estimates and cost.
3 / 5
What does 'cost' mean in a database execution plan?
Cost in execution plans is an abstract optimizer unit (not seconds or dollars). A cost of 100 vs. 10,000 means the optimizer estimates the latter is 100x more expensive. Costs are based on statistics (row counts, selectivity) and cost parameters (sequential_page_cost, random_page_cost in PostgreSQL).
4 / 5
What is a 'nested loop join' in execution plan vocabulary?
Nested loop join: for each outer row, scan inner table for matches. Best when outer table is small and inner table has an index (index scan per outer row). For large unindexed tables, hash join or merge join is typically more efficient.
5 / 5
What does 'rows' estimate mean in an explain plan output?
The rows estimate is the optimizer's prediction (not actual) based on table statistics (ANALYZE output). A large discrepancy between estimated and actual rows (shown in EXPLAIN ANALYZE) indicates stale or inadequate statistics — often the root cause of poor plan choices.