Database Explain Plan — Vocabulary and Communication

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.

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.

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).

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.

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.