Database Indexing That Actually Speeds Things Up

Most slow queries I have debugged were not caused by missing indexes. They were caused by indexes that existed but could not be used. The index was there, the query was slow, and nobody connected the two. Here is how I think about making an index actually do its job.

The index only helps if the query can use it

A B-tree index on users(email) lets the database jump straight to a row instead of scanning the table. But the moment you wrap the column in a function or an expression, that ability disappears:

-- index on email is ignored here
SELECT * FROM users WHERE lower(email) = '[e-mail]';

The planner sees lower(email), not email, so it falls back to a sequential scan. Two fixes:

-- option 1: match the expression
CREATE INDEX idx_users_lower_email ON users (lower(email));

-- option 2: store the value already normalized
-- (and index the plain column)
SELECT * FROM users WHERE email = '[e-mail]';

This is the single most common reason an index sits unused. Arithmetic, casts, and COALESCE cause the same problem.

Column order in a composite index matters

For CREATE INDEX ON orders (customer_id, created_at), the index is sorted by customer_id first, then created_at. That means:

• WHERE customer_id = 42 uses it.
• WHERE customer_id = 42 AND created_at > '2024-01-01' uses it and is fast.
• WHERE created_at > '2024-01-01' alone does not, because created_at is not the leading column.

The rule I use: put equality columns first, range columns last. If a query filters on a range across several columns, only the leading range column gets the fast seek; the rest become a filter on top.

Covering indexes avoid the table lookup

A normal index finds the row, then the database reads the table to get the rest of the columns. If the index already contains everything the query needs, that second step disappears. In Postgres you can include extra columns:

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id) INCLUDE (total, status);

SELECT total, status FROM orders WHERE customer_id = 42;

This is a real win on hot read paths. The tradeoff is a bigger index and slower writes, so I only do it when I have measured the read cost.

Selectivity decides whether it is worth it

An index on a boolean column with a 50/50 split is nearly useless. The planner will often choose a sequential scan anyway, because reading half the table through an index is slower than reading the table directly. Indexes pay off when the condition matches a small fraction of rows. Before adding one, ask how many rows a typical query returns versus how many exist.

Verify with the plan, not with hope

Never assume an index is used. Check:

EXPLAIN ANALYZE
SELECT total FROM orders WHERE customer_id = 42;

Look for Index Scan or Index Only Scan versus Seq Scan. Index Only Scan means the covering index did its job. If you see a sequential scan on a large table, the index is not being applied and you now know to look at the expression or column order.

A few habits that keep indexes honest

• Index the columns in your WHERE, JOIN, and ORDER BY clauses, not every column you can think of.
• Every index adds write cost. A table with ten indexes is slow to insert into.
• Drop indexes nobody uses. Postgres tracks usage in pg_stat_user_indexes.
• Rebuild or reindex after large deletes if the planner starts misjudging row counts.

Indexing is not about adding more indexes. It is about shaping the query and the index so the planner can actually use one. Match the expression, order the columns for the filter pattern, cover the read when it pays off, and confirm with EXPLAIN. That sequence fixes more slow queries than any amount of guessing.