Reading EXPLAIN / Query Plans
A query plan is the step-by-step strategy the database's optimizer chooses to execute a given SQL statement — which indexes (if any) to use, in what order to join tables, and how to filter and sort rows along the way. EXPLAIN asks the database to show that plan without necessarily running the query fully; EXPLAIN ANALYZE actually executes the query and reports real timing and row counts alongside the plan, making it far more useful for diagnosing genuine performance problems.
Sequential Scan vs Index Scan
The single most important distinction to recognize in a query plan is whether the database is scanning the whole table or using an index to jump directly to relevant rows.
Plan step | Meaning | Typically indicates |
|---|---|---|
Seq Scan / Full Table Scan | The database reads every row in the table | No usable index for this filter, or the table is small enough that scanning is actually cheaper |
Index Scan | The database uses an index to locate matching rows, then fetches each one | An index exists and is being used, but many matches still mean many row fetches |
Index Only Scan | The database satisfies the query entirely from the index, without touching the table | Fastest option — every needed column is present in the index itself |
Worked example: before adding an index
PostgreSQL: EXPLAIN before an index exists
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
On a large, unindexed table, the plan looks something like this:
Seq Scan on orders (cost=0.00..18334.00 rows=52 width=64) (actual time=0.021..142.558 rows=48 loops=1) Filter: (customer_id = 42) Rows Removed by Filter: 999952 Planning Time: 0.112 ms Execution Time: 142.601 ms
Seq Scan means the database read all one million rows and threw away the 999,952 that did not match, just to find the 48 that did. The execution time (about 143 ms here) grows roughly with the size of the table, regardless of how few rows actually match.
Worked example: after adding an index
Adding the index the query needs
CREATE INDEX idx_orders_customer_id ON orders (customer_id); EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
Index Scan using idx_orders_customer_id on orders (cost=0.42..8.62 rows=52 width=64) (actual time=0.018..0.041 rows=48 loops=1) Index Cond: (customer_id = 42) Planning Time: 0.098 ms Execution Time: 0.067 ms
With the index in place, the plan switches to Index Scan, and the execution time drops from roughly 143 ms to well under a millisecond — the database goes almost directly to the 48 matching rows instead of reading a million rows to find them.
What else to look at
cost — the optimizer's own estimate of relative expense, useful for comparing plans but not a real time unit.
actual time and rows — only present with ANALYZE; the real measured timing and row counts from actually running the query.
Rows Removed by Filter — a large number here is a strong sign that a more selective index or filter would help.
Join strategy (Nested Loop, Hash Join, Merge Join) — matters for multi-table queries and is worth investigating once single-table scans are already efficient.
EXPLAIN shows the execution plan the optimizer intends to use; EXPLAIN ANALYZE actually runs the query and reports real timing.
A Sequential/Full Table Scan reads every row; an Index Scan uses an index to jump to matching rows.
Comparing EXPLAIN output before and after adding an index is the most direct way to confirm an index actually helps a specific query.
EXPLAIN syntax and output format differ across PostgreSQL, MySQL, SQL Server, and Oracle, though the core concepts transfer.