The page is slow. Someone says “add an index.” You add an index on users.email because that column is in the query. The page is still slow. The query was filtering on status and sorting on created_at, and the new index is never used. EXPLAIN would have said so in ten seconds. Nobody ran EXPLAIN because indexes feel like folklore.
Indexes are not magic speed dust. They are extra data structures you pay for on every write. Used well, they turn a sequential scan of a million rows into a lookup. Used badly, they make inserts crawl and they sit unused while the sequential scan continues.
This is a practical Postgres-oriented guide for application developers who do not want to become DBAs, but who also do not want to guess.
What an index is, without the textbook
A table is a heap of rows. Finding “email = a@b.com” without an index means reading a lot of rows. A B-tree index is a sorted structure on that column (or those columns) that lets the database jump near the match, then read the matching rows.
Equality on a unique column is the easy case. Your primary key already has an index. You do not need a second one on id.
Range queries (created_at > now() - interval '7 days') can use a B-tree too. Leading wildcards (LIKE '%foo') generally cannot use a normal B-tree on that column. LIKE 'foo%' can.
If the query uses OR across unrelated columns, one index may not be enough. Postgres might bitmap-combine indexes, or it might give up and scan. EXPLAIN tells you which.
EXPLAIN is the whole skill
EXPLAIN ANALYZE runs the query and shows what happened. EXPLAIN without analyze plans without running. For a heavy query, start with EXPLAIN, then analyze on a copy or off-peak.
Look for Seq Scan on a large table when you expected a lookup. Look for Rows estimates that are wildly wrong compared to actual rows. Bad estimates mean bad statistics or a planner surprise. ANALYZE the table. If it is still wrong, the query might be wrapping the column in a function (WHERE DATE(created_at) = ...) which often prevents the index on created_at from being used. Filter on a range instead: created_at >= '2026-08-20' AND created_at < '2026-08-21'.
Index Scan vs Bitmap Heap Scan vs Index Only Scan are not moral categories. Index only scan is nice when the index covers all needed columns. If you SELECT *, you probably still visit the heap.
If the table is small, a sequential scan can be faster than an index. The planner is not stupid. Do not force an index because a blog said so. If the table will grow, test with realistic size.
Which columns to index
Start from the slow query, not from the schema. Index what you filter and join on, in a way that matches the query.
Single column
Foreign keys you join a lot: orders.user_id. Status columns if you always query WHERE status = 'open' and open is selective. If 95% of rows are active, an index on status may not help for active and may still help for banned.
Composite indexes
Order matters. (user_id, created_at) helps WHERE user_id = $1 ORDER BY created_at DESC. It is less ideal as a generic created_at index for a global feed. A leftmost prefix rule: (a, b) can serve a, and a and b, not b alone.
If you filter on org_id and created_at, put the equality column first, then the range: (org_id, created_at).
Partial indexes
CREATE INDEX ... ON users (email) WHERE deleted_at IS NULL keeps the index small if you never query deleted users. Partial indexes are one of the highest leverage Postgres features application developers skip.
Unique indexes
A unique constraint is an index. Use it for correctness (email unique) even if you did not need speed. Races without a unique constraint will duplicate rows. The application check is not enough.
The cost you pay
Every insert, update, and delete that touches indexed columns must update the indexes. Ten indexes on a hot table will show up as write latency. Index only the queries you have.
Updates that change an indexed column are more expensive than updates that do not. If you bump updated_at on every change and you indexed updated_at for no query, you paid for nothing.
Indexes take disk. pg_stat_user_indexes shows idx_scan. Unused indexes are candidates to drop after you confirm they are not for a rare admin job.
Common application mistakes
N+1 queries
The page is slow because you run 200 queries, not because one query lacks an index. An index will make each of the 200 faster and still be 200. Fix the fetch: join, WHERE id IN (...), or a loader. Then index the remaining query.
Pagination with OFFSET
OFFSET 100000 still walks a long way. Cursors on an indexed column are kinder. If you must offset, you will feel it, index or not.
SELECT *
You pull TOAST columns and wide JSON you do not need. Indexes cannot save the network. Select the columns you use. Covering indexes become possible.
ORMs that hide the SQL
Log the SQL. If the ORM wraps columns in CAST or lowercases with LOWER(email) and you indexed email as-is, you may need an index on LOWER(email) or a citext column. Look at the generated SQL before you add a random index.
Missing JOIN conditions
A missing join condition produces a huge intermediate set. An index will not make a cartesian product reasonable. EXPLAIN will show row counts exploding. Fix the join.
Migrations in production
CREATE INDEX on a large table can lock writes in older patterns. In Postgres, CREATE INDEX CONCURRENTLY avoids a long write lock and cannot run in a transaction. Your migration tool must allow that. Know it before you run a migration on 40 million rows during peak.
If the index creation fails concurrently, you can be left with an invalid index. Check \d or pg_index and drop invalid ones.
A simple loop when something is slow
- Confirm it is the database: logs, APM,
pg_stat_activity. - Get the SQL, not the ORM poetry.
EXPLAIN ANALYZEon staging with similar data.- Fix the query shape if it is N+1 or a function on a column.
- Add the smallest index that matches the filter and sort.
- Re-run explain. Confirm it is used.
- Watch write latency after deploy.
If you cannot get similar data, you will guess. Restore a subset. Guessing is how you get three unused indexes and a still-slow query.
When the index is used and the query is still slow
The index got you to 8,000 rows, then you sort in memory, then you join a wide table. EXPLAIN will show Sort Method: external merge if you spilled to disk. Increase work_mem carefully, or reduce the sort set with a better WHERE, or add the sort column to a composite index so the index order matches.
If you filter on a JSON field, you may need a GIN index or an expression index on (data->>'status'). A B-tree on a sibling column will not help that filter. Look at the Filter: vs Index Cond: in the plan. Filter means you already fetched rows and then threw some away.
Artikals is not a DBA manual. It is the subset you need on a weeknight. Indexes follow queries. Queries show up in EXPLAIN. Start there. “Add an index” is a hypothesis. The plan is the experiment. Run the experiment before you congratulate yourself.
