sqlstreak

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.

Practise it now