You added the index. The query is still slow. Or worse: the table now carries nine indexes, writes have got heavier, and the query you were trying to fix takes exactly as long as it did before.
Almost every indexing mistake reduces to one of two things: the index exists but the query as written cannot use it, or the planner has correctly decided that using it would be slower. The check that catches nearly all of them takes about thirty seconds — run EXPLAIN on the actual query, with actual parameter values, against a dataset shaped like production. If the plan shows a sequential scan, or an index scan followed by a filter that throws away most of the rows it just fetched, the index is not doing what you assumed. What follows are the mistakes that come up most often in review: why each is tempting, the cheap check that exposes it, and the fix.
Why isn't my query using the index I created?
Usually because the query hides the indexed column inside an expression. A B-tree index stores the values of the column, in order. The moment you wrap that column in a function, a cast, or a concatenation, you have stopped asking about the stored values and started asking about the output of a function applied to them — and the index has no entries for that.
-- Index on created_at exists, but this cannot use it:
SELECT * FROM orders WHERE DATE(created_at) = :day;
-- The same question, asked against the stored values:
SELECT * FROM orders
WHERE created_at >= :day
AND created_at < :day + INTERVAL '1 day';
The same trap catches case-insensitive lookups (WHERE LOWER(email) = :email), implicit casts when a parameter arrives as a string against a numeric column, and collation mismatches between a column and a literal. It is tempting because each of these reads naturally and works fine in development, where a full scan of a small table is instant.
The cheap check: run EXPLAIN and read the Filter line. If the function you wrote appears there rather than in an index condition, you are scanning.
The fix: rewrite the predicate as a range over the raw column where you can, and create an expression index where you cannot — CREATE INDEX idx_users_lower_email ON users (LOWER(email)) works, but only for queries whose expression matches it exactly.
One special case: LIKE '%widget%'. A B-tree is ordered by prefix, so a leading wildcard has no starting point to seek to. LIKE 'widget%' can use an index; the unanchored version cannot, and no amount of index-adding changes that. Trigram or full-text indexing is the real answer — and if you need it across several columns at once, you are using a relational store as a search engine.
Should I add an index for every column in the WHERE clause?
No — and this is the most expensive habit on the list. Give a table single-column indexes on tenant_id, status and created_at, then ask a query that filters on all three, and the database will typically scan whichever one it thinks is most selective and filter the rest afterwards, or combine them, which is real work in itself. One composite index usually beats three separate ones for that query.
Column order in a composite index is not cosmetic. An index on (a, b, c) serves queries on a, on a and b, and on all three. It generally cannot serve a query on b alone, because the entries for any given b are scattered across every value of a. The working rule: equality predicates first, then the column you range-scan or sort on — once the scan reaches a range predicate, the columns after it are no longer in a useful order.
-- Serves: tenant_id = ?
-- tenant_id = ? AND status = ?
-- both of those, plus an ordered or ranged read of created_at
CREATE INDEX idx_orders_tenant_status_created
ON orders (tenant_id, status, created_at);
The cheap check: pull your top few queries by total time — pg_stat_statements, or the slow query log — and write the WHERE and ORDER BY of each on a single page. Teams are usually surprised how few indexes cover the whole list once the columns are ordered deliberately.
Is it bad to have too many indexes?
Yes, and the cost lands where you are not looking. Every insert, delete, and update to an indexed column must maintain every index on the table. An index nobody reads is pure overhead on the write path, plus storage, plus cache pressure — it occupies memory that would otherwise hold pages you actually read.
The common form is redundancy: an index on (tenant_id) beside (tenant_id, status) rarely earns its place, because the first is a prefix of the second. Indexes accumulate because adding one feels easy and reversible, while removing one feels risky.
The cheap check: the usage counters your engine already keeps.
-- PostgreSQL: removal candidates sort to the top
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;
MySQL exposes the same idea through sys.schema_unused_indexes. Read either over a full business cycle rather than a quiet afternoon — a report that runs on the first of the month makes its index look unused for the other thirty days.
The fix: remove, but stage it. MySQL can mark an index invisible so the optimiser ignores it while the index stays on disk, which makes the test instantly reversible. Where that is not available, drop in a low-traffic window with the CREATE INDEX statement ready to paste back, and remember that rebuilding a large index is itself a schema change with a locking profile worth planning — the same care described in zero-downtime database migrations.
Why is my index fast in development and slow in production?
Because the planner's choice depends on statistics, and your development database has different ones. On a table of a few hundred rows a sequential scan genuinely is cheaper than descending an index and fetching rows one at a time, so the planner picks it — which means a dev EXPLAIN proves very little about production behaviour.
Selectivity is the other half. An index on a column where one value dominates — status = 'active' covering most of the table — will be skipped, correctly, because reading the index and then fetching most of the rows anyway is more work than reading the table once. Developers see the scan, conclude the index is broken, and add another one.
The cheap check: look at the distribution before you index, with SELECT status, count(*) FROM orders GROUP BY status, and run EXPLAIN (ANALYZE, BUFFERS) using the parameter values that are actually common rather than the convenient test value.
The fix: a partial index, which stores only the rows you query and stays small enough to remain cached.
CREATE INDEX idx_orders_pending_created
ON orders (created_at)
WHERE status = 'pending';
If a query regressed suddenly after a bulk load or a large delete, suspect stale statistics before you suspect the index, and re-analyse the table.
Do indexes help with ORDER BY and pagination?
They do, and this is the half that gets forgotten. An index is a sorted structure: when its order matches your ORDER BY, the engine reads rows already in order and stops as soon as it has enough, instead of collecting everything and sorting. When the order does not match — a different column, or mixed ascending and descending directions the index does not provide — an explicit sort appears in the plan, and it processes the whole result set before returning the first row.
The related mistake is OFFSET pagination on a large table. LIMIT 20 OFFSET 10000 has to produce and discard ten thousand rows before it returns anything, so every page costs more than the one before it, and page one benchmarks beautifully.
-- Keyset pagination: constant work per page, and it rides the index
SELECT * FROM orders
WHERE tenant_id = :tenant
AND (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;
Include a unique tie-breaker such as id, or rows sharing a timestamp will be skipped or repeated across pages. This changes the shape of your API — clients get a cursor rather than a page number — so decide it deliberately alongside the rest of your REST API design.
The mistake underneath all the others
Tuning by feel: an index gets added, the deploy goes out, the page seems snappier, and nobody measures. The next person inherits a table with a dozen indexes and no record of which query justified any of them.
- Capture the plan before you change anything, on production-shaped data.
- Change one index at a time.
- Re-run with realistic parameter values, not the ones that happen to be in your fixtures.
- Watch write latency as well as read latency afterwards.
- Remove the experiment if it did not pay, and note in the migration which query it was for.
One knock-on is worth knowing: a query holding a connection ten times longer than it should is occupying a pool slot for ten times longer, which is a common trigger for the stall described in database connection pool exhaustion. Indexing work often shows up as a stability improvement before a speed one.
FAQ
Does a foreign key column get an index automatically?
It depends on the engine. InnoDB requires an index on the referencing column and creates one if you did not, while PostgreSQL indexes the referenced key but not the referencing column — which is why cascading deletes and joins across foreign keys are a classic slow spot there.
Will creating an index lock the table?
A plain CREATE INDEX in PostgreSQL blocks writes for the duration; CREATE INDEX CONCURRENTLY avoids that at the cost of extra table passes and the risk of leaving an invalid index behind if it fails. MySQL performs many index additions online. Either way, treat it as a schema change with a rollout plan.
What is a covering index?
One that contains every column a query needs, so the engine can answer from the index alone without visiting the table. PostgreSQL supports INCLUDE columns for exactly this. It is one of the larger wins available on a hot read path, and it costs write throughput and disk in return.
Should I index a boolean or status column?
Usually not on its own, because there are too few distinct values for the index to narrow anything down. It becomes worthwhile when the distribution is heavily skewed and you always query the rare side — and a partial index on that condition is then the better tool.
Do indexes make COUNT(*) fast?
Only partly. A narrow index can be scanned instead of the table, which helps, but the engine still counts rows — the total is not stored. Counting an entire large table stays expensive, which is why filtered counts and approximate counts exist.
Index work rewards evidence over instinct: read the plan, change one thing, measure again. For more engineering guides on databases, APIs, and the operational side of shipping software, visit TheAppCode.
