Database Indexing 101: Make Slow Queries Fast

Most slow queries are slow because of a missing or wrong index. Here is enough theory to fix the next one.

What an index is

An index is a separate data structure that the engine maintains alongside your table. It trades write cost for read speed.

The four index types you actually use

Type	Best for
B-tree	equality and range (=, <, >, BETWEEN, LIKE 'foo%')
Hash	equality only (=); slightly faster than B-tree, smaller
GIN	arrays, JSONB, full-text search
BRIN	append-only time-series tables; tiny footprint

Read EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid';

Look for:

• Seq Scan on a big table → likely missing index.
• Index Scan + low rows → good.
• Bitmap Index Scan → multiple indexes combined; usually fine.
• High rows removed by filter → index not selective enough.

The most useful tricks

Composite indexes follow column order

CREATE INDEX ON orders (user_id, status, created_at);

This index serves queries on user_id, on (user_id, status), and on (user_id, status, created_at) — but not queries on status alone.

Partial indexes for skewed data

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

Smaller, faster, cheaper to maintain.

Covering indexes (Postgres INCLUDE)

CREATE INDEX ON orders (user_id) INCLUDE (status, total);

Lets the engine answer the query from the index alone (index-only scan).

Indexes on expressions

CREATE INDEX ON users (lower(email));
SELECT * FROM users WHERE lower(email) = 'a@b.com';

When indexes hurt

• High-write tables (UPDATE-heavy) with many indexes — every index must be updated.
• Indexes that are never used — they consume disk and slow down writes. Drop with pg_stat_user_indexes.
• Indexing low-cardinality columns (a boolean) — usually pointless without WHERE.

A practical workflow

1. Enable slow-query logging (log_min_duration_statement = 250ms).
2. Run EXPLAIN ANALYZE on the worst offender.
3. Add the smallest index that flips Seq Scan → Index Scan.
4. Re-run; verify the row counts shrink.
5. After a week, audit pg_stat_user_indexes and drop unused indexes.