Your Index Isn't Being Used: A Senior Engineer's Guide to Database Indexing

Every slow query gets the same advice: "add an index." Sometimes that fixes it in seconds. Sometimes it does nothing, and sometimes it makes your writes twice as slow.

An index is a trade: faster reads in exchange for slower writes and more storage. The senior move is knowing exactly what you're buying.

Start with the query, not the table

Before you create anything, answer three things:

1. What does the query filter on, sort by, and return?
2. How selective is the filter? (1 row in a million, or half the table)
3. How often does this table get written to?

Indexes serve query patterns, not columns. "Index the user_id column" is a guess. "Index the query that fetches a user's latest orders" is a design.

How a B-tree actually helps

Without index:  scan every row            -> O(n)
With B-tree:    walk the tree to the row  -> O(log n)

                 [ 50 | 100 ]
                /     |      \
        [10|30]   [60|80]   [120|150]
         / | \     / | \      / |  \
       leaf pages with pointers to table rows

The tree keeps values sorted. That's why one index can serve equality (=), ranges (>, BETWEEN), and ORDER BY, but not arbitrary substring searches.

Composite indexes: order is everything

An index on (a, b, c) is sorted by a, then b within a, then c within b. It follows the leftmost prefix rule.

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

The ordering heuristic: equality columns first, then the range column, then the sort column.

-- Equality on user_id and status, sorted by created_at: one index walk, no sort step
SELECT * FROM orders
WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 20;

Read the plan, don't guess

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE user_id = 42 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;

Seq Scan on orders          <- bad on a big table: reads everything
Index Scan using idx_...    <- good: walks the tree
Index Only Scan             <- best: never touches the table
Bitmap Heap Scan            <- fine: many matching rows

Look at actual time, rows removed by filter, and buffers read. A plan that reads 100k rows to return 20 is the problem, whatever scan type it uses.

Why your index isn't being used

1. A function wraps the column

-- Can't use an index on email
WHERE LOWER(email) = '[email protected]'

-- Fix: index the expression
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

2. Implicit type casts

-- phone is VARCHAR, but you compare to a number: cast on every row
WHERE phone = 5551234

Match your types, or the planner can't use the index.

3. Leading wildcard

WHERE name LIKE '%smith'   -- full scan
WHERE name LIKE 'smith%'   -- can use an index

For true substring or fuzzy search, use pg_trgm or full-text search.

4. Low selectivity

An index on a boolean is_active where 95% of rows are true is useless. The planner correctly decides scanning is cheaper. Which leads to a better tool.

Partial and covering indexes

Partial index: index only the rows you query.

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

Smaller, faster, cheaper to maintain. Perfect for "the small hot slice" of a big table.

Covering index: include extra columns so the query never touches the table.

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

This enables an Index Only Scan, which is often the biggest single win for read-heavy endpoints.

The cost nobody puts in the tutorial

INSERT one row into a table with 8 indexes
 -> 1 table write + 8 index writes + WAL for all of them

Every index adds write amplification, slows UPDATEs that touch indexed columns, and eats memory that your hot data wanted.

Find the dead weight:

SELECT relname AS table, indexrelname AS index, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

An index with idx_scan = 0 over a full business cycle is a candidate for removal (check replicas too, since stats are per node).

Ship it safely in production

A plain CREATE INDEX blocks writes to the table. On a live system, use:

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

It takes longer and can't run inside a transaction, but it doesn't lock out your writers. If it fails it leaves an invalid index behind, so check for it and drop it before retrying.

What to measure

• p95/p99 latency of the specific query before and after
• Rows examined vs rows returned (the ratio should be close to 1)
• Write latency on the table after adding the index
• Index size vs table size (an index bigger than the table is a smell)

Rules I follow

• Index for a known query, never "just in case."
• Equality first, range second, sort last.
• Prefer partial and covering indexes for hot paths.
• Verify with EXPLAIN ANALYZE on production-sized data, since small tables lie.
• Audit unused indexes on a schedule.
• Always CONCURRENTLY on live tables.

Takeaway

A good index makes a query 1000x faster. A bad one is a tax on every write that you pay forever. Treat indexes as part of your schema design, measure them like any other dependency, and delete the ones that don't earn their keep.

What's the biggest speedup (or self-inflicted slowdown) an index ever gave you? Share it in the comments. 👇