Index Made Queries Slower? Here’s Why and How to Fix It

Quick Fix (30 seconds) — Check If the Index Is Actually Being Used

The most common reason an index makes things worse: the query planner ignores it, or uses it in a way that’s slower than a full table scan. You won’t know until you check.

-- PostgreSQL: see if the index is used
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'shipped';

-- MySQL / MariaDB
EXPLAIN SELECT * FROM orders WHERE status = 'shipped';

Look at the output. If you see Seq Scan (PostgreSQL) or Using where without Using index (MySQL), the index isn’t being used. That means every write still pays the cost of maintaining the index, but reads get zero benefit. Drop it and move on.

But what if the index is used, and queries are still slower? That’s the real puzzle. Stay here.

Moderate Fix (5 minutes) — Check Index Selectivity

Here’s the thing: an index on a column with low selectivity (like a boolean status with only two values) is almost never worth it. If a query filters on status = 'shipped' and 40% of the table is shipped, the planner might still use the index. But index scans have overhead — reading index pages, then fetching heap pages. For large row sets, a sequential scan reads pages in order and is faster.

What’s actually happening here is a cost misjudgment. The planner’s cost model thinks the index is cheaper than it is, especially if statistics are stale. Run:

-- PostgreSQL
ANALYZE orders;

-- MySQL
ANALYZE TABLE orders;

Then re-run your EXPLAIN. If the plan switches to a sequential scan and performance improves, the problem was stale stats — not the index itself. But if it still uses the index and runs slow, the index is just too broad. Drop it and consider a partial index.

Partial Index — The Underrated Fix

Instead of indexing all values, index only the rows you care about. For example, if you almost always query for status = 'pending' (which is 2% of the table), do this:

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

This index is tiny, fast to scan, and won’t degrade performance for other queries. It’s a specific fix that solves the real problem: indexing the wrong data subset.

Advanced Fix (15+ minutes) — The Index Is Causing a Bad Plan Change Across the Workload

This is the nasty one. You add an index on user_id to speed up a specific reporting query. Suddenly, a completely unrelated query — say, SELECT * FROM logs ORDER BY created_at — goes from 50ms to 10 seconds. How?

The reason step 3 works is: the new index changes the planner’s statistics or cost estimates for user_id. PostgreSQL and MySQL store per-column statistics. Adding an index triggers a stats update on that column. If the old stats for user_id were uniform, and the new stats show a highly skewed distribution, the planner might choose a nested loop join where a hash join was faster, or vice versa.

Here’s how to diagnose:

1. Capture the bad query’s plan — run EXPLAIN (ANALYZE, BUFFERS) on the slow query.
2. Compare to the plan before the index — if you don’t have it, recreate by dropping the index temporarily in a test environment.
3. Look for plan changes — e.g., a seq scan replaced by an index scan that touches 80% of the table, or a hash join swapped for a nested loop.

Fixing Plan Regression

You have three options, ordered by invasiveness:

Option A: Block the Index from That Query (Least Invasive)

In PostgreSQL, you can disable index scans for a specific query (not globally — don’t do that). Use:

SET enable_indexscan = off;
-- run your query
SET enable_indexscan = on;

This is a debugging trick more than a fix. Use it to confirm the index is the culprit, then move to a real solution.

Option B: Force a Different Join Order or Method

In PostgreSQL, you can use join_collapse_limit or from_collapse_limit in the session. In MySQL, use STRAIGHT_JOIN or /*+ JOIN_FIXED_ORDER() */ hints. But these are band-aids. The real fix is better statistics or a different index design.

Option C: Drop the Offending Index and Redesign

This is the honest fix. Ask: what does the workload actually need? If you need fast lookups by user_id for a small subset of users, use a partial index with a condition that matches your query’s WHERE clause. Don’t index every user.

-- Instead of:
CREATE INDEX idx_logs_user_id ON logs(user_id);

-- Do:
CREATE INDEX idx_logs_user_id_active ON logs(user_id)
WHERE user_id IN (SELECT id FROM users WHERE active = true);

This keeps the index small and targeted. It won’t pollute the planner’s statistics for the full column.

When to Just Accept the Index

Sometimes the index does speed up your critical query by 100x, and the secondary queries degrade by 20%. In that case, measure the business impact. If the critical query runs every second and the degraded one runs once a day at 3 AM, keep the index. Tune the slow query separately with a different join method or an additional compound index.

Bottom line: Indexes are tools, not magic. Adding one without checking selectivity, stats, and workload interaction is how you get performance regression. Always verify with EXPLAIN before and after.