advanced · 23 minute study

Explain why a query does work

Adding indexes by habit increases write cost and may not help the query that is slow. Performance work starts with the real predicate and ordering.

Loading progressEvidence grows with practice

Useful preparation · 1 prerequisite

Use this as a map, not a lock. If these ideas are familiar, continue.

Name intermediate relations; keep row-level context — A query is easier to debug when you can name its intermediate result and distinguish summarizing rows from annotating them.

First, build a mental model

An index is a second organized representation of selected values. It can reduce searching or sorting, but the database still chooses whether that route is worthwhile.

A B-tree on (customer_id, status, id) can be useful for equality filters on the first two columns and ordered traversal by id.
Index usefulness depends on selectivity, table size, available statistics and the query shape. A tiny table can reasonably use a sequential scan.
Every maintained index consumes storage and adds work to inserts, updates and deletes. More indexes are not free speed.
SQLite's EXPLAIN QUERY PLAN reports a different format from PostgreSQL's EXPLAIN. PostgreSQL EXPLAIN ANALYZE executes the statement; do not use it casually on writes.

Read the codesql · educational example

CREATE INDEX orders_customer_status_id
  ON orders(customer_id, status, id);
SELECT id, total_cents FROM orders
WHERE customer_id = 1 AND status = 'paid'
ORDER BY id;
-- SQLite lab: prepend EXPLAIN QUERY PLAN to SELECT.
-- PostgreSQL: inspect EXPLAIN; ANALYZE executes the query.

Question
Find a particular customer's paid orders in stable ID order.
Candidate access path
The index organizes the filtered prefix and the remaining order. It is a candidate, not a forced plan.
Evidence
Read the actual engine plan, then compare representative workloads and unchanged result rows.

Production thinking

Take this beyond the example.

A useful performance report states query, dataset, engine, cache conditions, plan, repeated timing and correctness. One fast empty result is not evidence of a scalable design.

Sources and further reading

Your evidence

unseen is a local learning signal, not a credential or proof of production competence.

Explain the mental model, then explicitly acknowledge it. Opening a page is not evidence.

Supported retries can complete this lesson. Independent evidence and delayed review determine the skill stage.

Next lesson · Make a change an all-or-nothing unit →