Query performance and EXPLAIN
Hook
The query was instant on the 500-row sample. In production it runs for two
minutes, and the pipeline step that depends on it now misses its window. You did
not change the SQL — only the table grew. The database is doing something on
every run that you cannot see from the query text alone, and staring harder at
the SELECT will not reveal it. The execution plan will.
Concept
Before running a query, the database builds an execution plan: the step-by-
step strategy it will use to produce your rows. EXPLAIN QUERY PLAN shows you
that strategy without running the query for real. Reading it turns "the query is
slow" from a guess into a diagnosis.
The single most important distinction in a plan is scan versus search:
- A
SCANreads every row in the table and checks each one. Cost grows with the size of the table — double the rows, double the work. This is fine for a tiny table and ruinous for a large one. - A
SEARCH … USING INDEXjumps to the matching rows through an index, a sorted structure the database maintains alongside the table. Cost grows with the size of the result, not the table.
The mental model is a book. Finding every mention of a word by reading all 900 pages front to back is a scan. Finding it through the index at the back — which lists the exact pages — is a search. The index exists precisely so you do not read the whole book.
An index on a column is what lets the engine search instead of scan when you filter or join on that column. The trade-off, stated honestly:
| Without an index | With an index on the filter column | |
|---|---|---|
| Read strategy | Full SCAN of the table | SEARCH straight to matching rows |
| Query cost | Grows with table size | Grows with result size |
| Write cost | Lower | Every insert/update maintains the index too |
| Storage | None extra | The index occupies disk |
Indexes are not free — they cost storage and slow down writes, because every
insert must update them too. So you do not index every column. You index the
columns you filter and join on in your hot queries, confirm the plan changed
from SCAN to SEARCH, and leave the rest alone.
Plan wording is engine-specific. This lesson uses SQLite's EXPLAIN QUERY PLAN,
which prints SCAN and SEARCH. PostgreSQL's EXPLAIN says Seq Scan and
Index Scan; the concept is identical across engines even when the words differ.
Worked example
Let me make a slow query fast and prove it from the plan. Here is a
measurements table with 2,000 rows, filtered by sensor_id — the kind of
lookup a pipeline runs constantly:
import sqlite3
con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE measurements (sensor_id INTEGER, reading REAL)")
con.executemany(
"INSERT INTO measurements VALUES (?, ?)",
[(i % 100, i * 1.5) for i in range(2000)],
)
First, ask the engine how it plans to run the lookup — before running it. Note
the query text is prefixed with EXPLAIN QUERY PLAN:
query = "SELECT reading FROM measurements WHERE sensor_id = 42"
print("BEFORE index:")
for row in con.execute("EXPLAIN QUERY PLAN " + query):
print(" ", row[-1])
BEFORE index:
SCAN measurements
SCAN measurements means the engine will read all 2,000 rows and test
sensor_id = 42 on each. Now add an index on the filtered column and ask again:
con.execute("CREATE INDEX idx_measurements_sensor ON measurements(sensor_id)")
print("AFTER index:")
for row in con.execute("EXPLAIN QUERY PLAN " + query):
print(" ", row[-1])
AFTER index:
SEARCH measurements USING INDEX idx_measurements_sensor (sensor_id=?)
The plan changed from SCAN to SEARCH … USING INDEX. The engine now walks the
index straight to the sensor_id = 42 rows instead of reading the whole table.
The SQL text never changed — only the plan did, because the index gave the
planner a faster path. That is the whole workflow: read the plan, see a scan on
a column you filter, add the index, confirm the plan now searches.
An index only helps queries that filter or join on its column. Adding
idx_measurements_sensor does nothing for a query that filters on reading,
and it still adds cost to every insert. Confirm the plan actually changed before
you conclude an index earned its keep.
Hands-on
Your turn, on a different table. The exercise below gives you an events table
with an unindexed user_id column and a query that filters on it. You will read
the plan, confirm it is a scan, add the index, and confirm the plan becomes a
search.
Success criteria: you report the pre-index plan as a scan, create an index on the filtered column, and show the post-index plan searching that index. SQL Kingdom checks that your index targets the filtered column and that the plan changed.
Recap
- You can read an execution plan with
EXPLAIN QUERY PLANand tell a fullSCANfrom aSEARCH … USING INDEX. - You can explain why a scan's cost grows with table size while an indexed search's cost grows with result size.
- You can add an index on a filtered or joined column and confirm from the plan that the engine now uses it — and you know the write and storage cost you pay.
Next, in the lab, you will put all three lessons together: join several tables, aggregate and rank with a window function, then read the plan and add an index — building one analytical query end to end. See Lab 1 — Build an Analytical Query.
EXPLAIN QUERY PLANturns "why is this slow?" into a diagnosis by showing the engine's strategy.SCANreads every row (cost grows with the table);SEARCH … USING INDEXjumps to matches (cost grows with the result).- Index the columns your hot queries filter and join on, confirm the plan changed, and accept the write and storage cost you pay for it.