SQL indexes and query plans
Most slow queries are slow for one reason: the database reads every row to find a few. An index fixes that, if your query is written so the index can be used.
What an index is
An index is a sorted copy of one or more columns with pointers back to the rows, like the index at the back of a book. Lookups, joins and sorts on those columns can skip the full scan.
CREATE INDEX idx_orders_customer ON orders (customer_id); SELECT * FROM orders WHERE customer_id = 1;
Reading the plan
Ask the database how it will run a query. PostgreSQL shows “Seq Scan” (whole table) or “Index Scan”; SQLite shows SCAN or SEARCH … USING INDEX. On tiny tables the planner may still scan, because that's cheaper.
CREATE INDEX idx_orders_customer ON orders (customer_id); EXPLAIN QUERY PLAN SELECT * FROM orders WHERE customer_id = 1;
Composite indexes
An index on (a, b) helps filters on a, and on a and b together, but not on b alone, just like a phone book sorted by last name and then first name.
CREATE INDEX idx_items ON order_items (order_id, item_id); EXPLAIN QUERY PLAN SELECT * FROM order_items WHERE order_id = 3;
Index-friendly (sargable) filters
Keep the indexed column bare. WHERE EXTRACT(MONTH FROM ordered_on) = 3 hides the column inside a function, so the index can't help; WHERE ordered_on >= '2026-03-01' AND ordered_on < '2026-04-01' returns the same rows and can use it. Likewise LIKE 'M%' can use an index but LIKE '%m' can't.
SELECT id, ordered_on FROM orders WHERE ordered_on >= '2026-03-01' AND ordered_on < '2026-03-08';
Common mistakes
Indexing every column. Each index slows down every insert and update.
Wrapping indexed columns in functions or casts in WHERE.
Expecting an index on (a, b) to speed up filters on b alone.
Questions
Do primary keys have an index?+
Yes, both databases index primary keys and UNIQUE columns automatically.
What's EXPLAIN ANALYZE?+
In PostgreSQL it actually runs the query and reports real timings and row counts. It's the best tool for finding where a query spends its time.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.