Indexing and Query Planning

An index is a data structure the database maintains so it can find rows without scanning an entire table. Indexes make reads faster, but they are not free. Every insert, update, and delete must also update the relevant indexes.

When an Index Helps

Indexes are useful for columns used in WHERE, JOIN, ORDER BY, and uniqueness checks.

CREATE INDEX tasks_user_status_idx
ON tasks (user_id, status);

SELECT *
FROM tasks
WHERE user_id = 'u_123' AND status = 'open';

The column order matters in composite indexes. An index on (user_id, status) helps queries filtering by user_id, or by both user_id and status. It does not help much for a query filtering only by status.

Query Plans

Most databases can explain how they plan to run a query.

EXPLAIN ANALYZE
SELECT * FROM tasks WHERE user_id = 'u_123' AND status = 'open';

Look for whether the database uses an index scan or falls back to a sequential scan. A sequential scan is not always bad for small tables, but it becomes expensive as data grows.

NOTE

Query performance is about the actual data shape. A query that is fast with 200 rows can be painfully slow with 20 million rows.

Avoid Index Sprawl

Each index consumes storage and slows writes. Add indexes for real access patterns, not hypothetical ones.

TIP

Start from the slow query, then design the index. Do not start from the table and add indexes to every column that looks important.

Further Learning

Search these terms to go deeper:

“database index explained B tree” — how common indexes work internally
“PostgreSQL EXPLAIN ANALYZE tutorial” — reading real query plans
“composite index column order” — designing multi-column indexes
“index write overhead database” — trade-offs behind indexing

Indexing and Query Planning

Imagine looking up a word in a book with no index — you’d have to read every page. A database index is exactly like a book’s index: a shortcut that lets the database jump straight to the rows you want, instead of scanning the whole table.

When an index helps

Add an index on columns you frequently search by, filter by, or sort by:

CREATE INDEX tasks_user_idx ON tasks (user_id);

SELECT * FROM tasks WHERE user_id = 'u_123';

Without the index, the database checks every row. With it, the database jumps straight there.

Indexes aren’t free

Every index speeds up reads but slows down writes a little — because every insert/update also has to update the index. So don’t add indexes “just in case”; add them for columns you actually query often.

TIP

Start from a slow query you actually have, then add an index for it — don’t guess ahead of time and index everything.

Checking what the database is doing

Most databases can show you how they plan to run a query:

EXPLAIN ANALYZE SELECT * FROM tasks WHERE user_id = 'u_123';

This tells you whether it used your index (fast) or scanned the whole table (slow on big tables). It’s the go-to tool when a query feels sluggish.

In one sentence

An index is a shortcut that lets the database find rows without scanning the whole table — add one for columns you filter/sort by often, but don’t over-index, since each one costs a little on every write.

Want to go deeper?

Switch to Expert mode above for composite index column ordering and reading full query plans.