EXPLAIN and Query Performance

Read the plan before you optimize

When a query is slow, guessing is a waste of time; ask the database what it's doing. EXPLAIN QUERY PLAN <query> returns the plan: how the engine will access each table. The word you're hunting for is SCAN versus SEARCH:

• SCAN table is a full table scan: the engine reads every row. Fine for tiny tables, catastrophic for large facts.
• SEARCH table USING INDEX … is an index seek: the engine jumps straight to the matching rows. This is what you want on a filtered or joined column.

EXPLAIN QUERY PLAN
SELECT * FROM fact_sales WHERE customer_key = 42;

On an unindexed customer_key this reports SCAN fact_sales. Add the index:

CREATE INDEX idx_fact_sales_customer ON fact_sales(customer_key);

and the same EXPLAIN QUERY PLAN now reports SEARCH fact_sales USING INDEX idx_fact_sales_customer (customer_key=?): the scan became a seek. Index the columns queries filter on, join on, and often order by.

Sargable predicates

An index can only be used if the predicate is sargable (Search-ARGument-able): the indexed column must appear bare on one side of the comparison. Wrapping it in a function defeats the index:

-- NON-sargable: function on the column -> index unusable -> full scan
WHERE strftime('%Y', order_date) = '2026'

-- Sargable: bare column vs a literal range -> index seek
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'

Both select 2026, but only the second lets the engine seek. Rewriting a function-wrapped predicate as a range on the bare column is one of the highest-leverage performance fixes a DE makes. Note the half-open upper bound (< '2027-01-01'): it keeps the very last day of the year (2026-12-31) and cleanly drops 2027-01-01: no fencepost bug, and no BETWEEN gotcha.

Common pitfalls

• Indexing everything. Every index speeds reads but slows writes (each INSERT / UPDATE must maintain it) and costs storage. Index selectively: the FK/join columns and the hot filter columns, not every column.
• Redundant indexes. PRIMARY KEY and UNIQUE columns are already indexed; don't add a second index on them.
• Reading the plan wrong. SCAN on a 5-row lookup table is totally fine; a seek's overhead isn't worth it. Optimize the scans that hurt: big tables in the hot path.

In the warehouse: SQLite's EXPLAIN QUERY PLAN is the lightweight cousin of Postgres's EXPLAIN ANALYZE (which also runs the query and reports real timings) and Snowflake's query-profile UI. Columnar warehouses lean on partition pruning and clustering rather than B-tree indexes, but the sargable-predicate rule (bare column, no function) is universal.

Recap: EXPLAIN QUERY PLAN reveals SCAN (full read) vs SEARCH … USING INDEX (seek); turn a hot-path scan into a seek by indexing the filter/join column and keeping predicates sargable (bare column, range not function), and index selectively, because every index taxes writes.

Execution mode: you write a multi-statement script against a fresh seeded SQLite DB, then hidden assertion queries check that the right indexes exist and that your rewritten query returns exactly the right rows. You can eyeball the plan yourself with EXPLAIN QUERY PLAN <your query>; while you work, but the grader checks the index and the result, not the plan text.