Introduction

Slow queries are the most visible symptom of a backend system under load. In PostgreSQL, the difference between a query that scans millions of rows and one that fetches exactly ten usually comes down to one thing: the right index. This lesson walks through the three index types you'll use most often in backend work — B-tree, composite, and partial — and shows you how to read an EXPLAIN ANALYZE plan to know which one you need.

B-tree Indexes: The Default Workhorse

A B-tree index is what PostgreSQL creates when you simply write CREATE INDEX. It keeps the column values sorted, so lookups by equality (=) and range (>, <, BETWEEN) are fast. Use it whenever a column appears frequently in WHERE clauses or JOIN conditions.

CREATE INDEX idx_users_email ON users (email);

SELECT * FROM users WHERE email = 'alice@example.com';

Without the index, PostgreSQL performs a sequential scan — it reads every row. With the index, it jumps straight to the matching entry. On a table with ten million rows, that difference is the gap between 200ms and 2ms.

Composite Indexes: Order Matters

When a query filters or sorts by multiple columns, a composite index can serve both at once. The critical detail is column order: the index can only be used efficiently when the leading columns are constrained.

CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);

SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Here the index on (user_id, created_at) lets PostgreSQL filter by user_id and already have the rows sorted by created_at — no separate sort step needed. A common mistake is creating the index with columns in the wrong order. An index on (created_at, user_id) would not help this query, because user_id is not the leading column.

Partial Indexes: Index Only What Matters

A partial index adds a WHERE clause to the index itself, so it only stores rows that match a condition. This keeps the index small and fast when most of your queries only care about a subset of the table.

CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

If 99% of orders are already completed and only1% are pending, a full index on status would be wasteful. The partial index might be 1000× smaller and just as useful for the queries that actually matter.

Reading EXPLAIN ANALYZE

EXPLAIN ANALYZE runs the query and reports the actual plan PostgreSQL chose, with real timing and row counts. The most important signals to look for:

- Sequential Scan on a large table → you likely need an index. - Rows removed by filter much higher than Rows estimated by planner → your statistics are stale; run ANALYZE table_name. - Sort node on top of a large set → consider an index that matches the ORDER BY, or add a LIMIT earlier.

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look for Index Scan using idx_orders_user_date in the output. If you see Seq Scan instead, the index is not being used — usually because the table is too small for the planner to bother, or your statistics are outdated.

Common Pitfalls

Indexing every column is worse than indexing none: every index slows down writes (INSERT, UPDATE, DELETE) because PostgreSQL must update the index too. A good rule is: index columns that appear in WHERE, ORDER BY, or JOIN of queries that run often and touch many rows.

Another frequent mistake is using a function on the indexed column in WHERE, which prevents index usage:

-- This WILL use an index on users.email
SELECT * FROM users WHERE email = 'alice@example.com';

-- This WILL NOT use that index
SELECT * FROM users WHERE lower(email) = 'alice@example.com';

If you need case-insensitive lookup, either store a normalized column or create a functional index: CREATE INDEX ON users (lower(email)).

Summary

Effective indexing in PostgreSQL comes down to matching index type and column order to real query patterns. B-tree handles most equality and range lookups. Composite indexes combine filtering and sorting when the leading column is constrained. Partial indexes shrink the index to only the rows that matter. EXPLAIN ANALYZE is your feedback loop — always run it before and after adding an index to confirm the plan actually changed.