Why Database Indexes Didn’t Fix Your Slow Query

Database indexes do not automatically fix slow queries. An index can exist, match part of the WHERE clause, and still leave the query slow because the planner expects the index to return too many rows, require too many heap fetches, miss the sort order, or filter most of the result after the index scan.

That is the frustrating version of SQL performance work: the fix looks obvious, the migration succeeds, the index appears in the schema, and production latency barely moves.

For the broader SQL performance and data-correctness cluster, see the SQL And Data Correctness hub.

The Slow Query Already Had An Index

Consider an orders endpoint that lists recent completed orders for an account:

SELECT id, account_id, status, created_at, total_cents
FROM orders
WHERE account_id = $1
  AND status = 'completed'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

The table already has an index:

CREATE INDEX orders_account_id_idx
  ON orders (account_id);

That sounds reasonable. The query filters by account_id, so the database should use the index, find that account's rows, filter recent completed orders, sort them, and return 50 rows.

In a small dataset, that may be fine. In production, the account may have hundreds of thousands of orders, most of them completed. The account_id index narrows the table, but not enough.

A simplified plan might look like this:

Limit  (actual time=418.201..418.216 rows=50 loops=1)
  ->  Sort  (actual time=418.199..418.205 rows=50 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 37kB
        ->  Bitmap Heap Scan on orders
              Recheck Cond: (account_id = 'acct_123'::uuid)
              Filter: ((status = 'completed') AND (created_at >= (now() - '30 days'::interval)))
              Rows Removed by Filter: 164972
              Heap Blocks: exact=18421
              ->  Bitmap Index Scan on orders_account_id_idx
                    Index Cond: (account_id = 'acct_123'::uuid)

The database used the index. The query is still slow.

The important detail is not "index used" versus "index ignored." The important detail is how much useful work the index avoided.

PostgreSQL's documentation describes indexes as a way to retrieve specific rows faster, but also warns that indexes add overhead and should be used sensibly. See PostgreSQL: Indexes.

Index Usage Is Not The Same As Selectivity

An index helps most when it narrows the search to a small, useful part of the table.

If an index returns 200 candidate rows and the query needs 50, the index probably helps. If it returns 170,000 candidate rows and the query still filters, sorts, and fetches heap pages for most of them, the index may not change latency much.

That is selectivity: how much the predicate reduces the result set.

The mistake is treating every indexed predicate as equally selective.

An index on status might be perfect when only 1% of rows are failed. It may be nearly useless when 94% of rows are completed.

PostgreSQL's partial-index documentation says one reason to use a partial index is to avoid indexing common values because a query searching for a common value may not use the index anyway. See PostgreSQL: Partial Indexes.

That guidance is a useful mental model even when you are not creating a partial index: common values are often bad index drivers.

The Planner Optimizes Cost, Not Developer Intent

The planner does not ask, "Did an engineer add an index for this query?"

It estimates the cost of possible plans:

• sequential scan plus filter
• index scan
• bitmap index scan plus heap scan
• index scan that also satisfies ordering
• explicit sort after scanning candidate rows
• join orders and join algorithms when multiple tables are involved

The selected plan is the one the planner estimates to be cheapest.

PostgreSQL's Using EXPLAIN guide is explicit that the planner chooses a plan based on query structure and data properties, and EXPLAIN shows the plan it creates. The EXPLAIN reference also explains that costs and row counts are estimates, while EXPLAIN (ANALYZE) runs the statement and shows actual runtime statistics.

That difference matters. When an index does not help, compare estimated rows with actual rows:

Bitmap Heap Scan on orders
  (cost=3120.44..78122.88 rows=5120 width=48)
  (actual time=31.402..409.772 rows=18123 loops=1)
  Recheck Cond: (account_id = 'acct_123'::uuid)
  Filter: ((status = 'completed') AND (created_at >= (now() - '30 days'::interval)))
  Rows Removed by Filter: 164972

The planner expected around 5,120 rows after filtering. It got 18,123 rows and removed 164,972 after the index condition.

That is not a "PostgreSQL ignored my index" story. It is a "the index matched only part of the query, and the row estimates did not describe production reality well enough" story.

Composite Indexes Must Match The Access Pattern

A better index is usually not "one index per filtered column."

For the query above, this is more aligned:

CREATE INDEX CONCURRENTLY orders_recent_completed_by_account_idx
  ON orders (account_id, status, created_at DESC);

Now the index can support:

• the stable account filter
• the status filter
• the desired created_at DESC order
• early return for LIMIT 50

The improved plan might look like this:

Limit  (actual time=1.186..2.041 rows=50 loops=1)
  ->  Index Scan using orders_recent_completed_by_account_idx on orders
        Index Cond: (
          (account_id = 'acct_123'::uuid)
          AND (status = 'completed'::text)
          AND (created_at >= (now() - '30 days'::interval))
        )

There is no large bitmap heap scan, no large post-index filter, and no separate sort over thousands of candidate rows.

Column order matters because indexes are ordered data structures, not magic lookup maps. In a B-tree composite index, leading columns are usually most useful when they match equality predicates, and later columns can support range filtering or ordering once earlier columns have narrowed the search.

This is not a universal recipe. If the query often asks for all statuses, (account_id, created_at DESC) may be better. If it filters by status across all accounts, a different index may be needed. If the route supports several optional filters, one composite index may not serve every query shape.

PostgreSQL's multicolumn indexes documentation is a good reminder that multicolumn indexes should be used carefully; they encode a specific access pattern and add maintenance cost.

ORDER BY LIMIT Only Helps If The Index Can Provide The Order

ORDER BY created_at DESC LIMIT 50 looks like it should stop early.

It only stops early when the database can read rows in the right order while applying useful predicates. If the index returns a large unordered candidate set, the database may still have to filter and sort before it knows which 50 rows are first.

Compare these two cases:

PostgreSQL's Indexes and ORDER BY page explains this trade-off: an index can satisfy an order, but when a query scans a large fraction of the table, an explicit sort can be cheaper; ORDER BY plus LIMIT is especially powerful when a matching index can return the first rows directly.

That is why adding an index on the filtered column alone may not fix a query whose cost is dominated by sorting and post-index filtering.

Post-Index Filters Are Often The Hidden Cost

In an EXPLAIN (ANALYZE, BUFFERS) plan, look for the difference between Index Cond and Filter.

Example:

Bitmap Heap Scan on orders
  Recheck Cond: (account_id = 'acct_123'::uuid)
  Filter: ((status = 'completed') AND (created_at >= (now() - '30 days'::interval)))
  Rows Removed by Filter: 164972

The index handled account_id. The database still had to fetch candidate rows and apply status and created_at afterward.

That is often where the time went.

An index can be present and technically used while still leaving most of the expensive work in the plan.

This is why the broader workflow in How to Find and Fix Slow SQL Queries in Production starts with the plan, not with the migration.

Partial Indexes Help When The Common Case Is Not Useful

Sometimes the best index is not a broader composite index.

If a route only lists recent completed orders that are not archived, and archived or incomplete rows dominate the table, a partial index can keep the hot access path small:

CREATE INDEX CONCURRENTLY orders_completed_recent_active_idx
  ON orders (account_id, created_at DESC)
  WHERE status = 'completed'
    AND archived_at IS NULL;

Then the query must match the predicate clearly:

SELECT id, account_id, created_at, total_cents
FROM orders
WHERE account_id = $1
  AND status = 'completed'
  AND archived_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

Partial indexes are useful when:

• the indexed subset is much smaller than the table
• the query predicates are stable and explicit
• the route repeatedly uses that same subset
• write overhead matters and indexing every row is wasteful

They are risky when:

• query predicates vary too much
• the application sometimes omits the predicate
• prepared or generated SQL changes the expression shape
• data distribution changes enough that the subset is no longer small

PostgreSQL's partial-index documentation notes that the planner can use a partial index only when it can recognize that the query condition implies the index predicate. That is a practical production gotcha: two predicates that look equivalent to a developer may not be equivalent to the planner.

Covering Indexes Do Not Fix The Wrong Predicate

Another tempting fix is to add more columns to the index so the database can avoid heap fetches:

CREATE INDEX CONCURRENTLY orders_completed_covering_idx
  ON orders (account_id, status, created_at DESC)
  INCLUDE (total_cents);

That can help when the query already narrows to a small set and only needs a few columns.

It does not fix a weak predicate.

If the index still returns 170,000 candidate rows, adding INCLUDE columns may reduce some heap reads but still leave too much scanning. It also makes the index larger and increases write cost.

Covering indexes are a finishing tool, not a substitute for selectivity.

Statistics Can Be The Reason The Planner Chooses Poorly

Sometimes the index is appropriate, but the planner's estimates are wrong.

Common causes:

• stale table statistics after large data changes
• skewed tenant distributions
• correlated columns that ordinary statistics do not model well
• status values that changed meaning over time
• test data that does not resemble production
• parameterized queries whose generic plan is not good for every tenant

Start with the simple checks:

ANALYZE orders;

Then inspect whether estimated rows and actual rows still diverge. If one tenant has 500 orders and another has 500,000, a plan that is fine for the average tenant may be poor for the largest one.

In PostgreSQL, EXPLAIN (ANALYZE, BUFFERS) is usually the clearest way to see the mismatch, because it shows both the planner's estimates and actual execution behavior. It can execute the query, so use it carefully on write statements or expensive production queries.

When The Fix Is Not Another Index

If the index exists and the query remains slow, do not keep adding indexes blindly.

Use the plan to choose the next move:

That last point matters. A useful index can still be dangerous to add casually on a hot table. For rollout sequencing, see Safe Database Migrations in Production.

If the slow query is part of a correctness-sensitive transaction, also consider how long the query holds locks or keeps the transaction open. The surrounding transaction boundary may matter as much as the plan; see Database Transaction Boundaries in Backend APIs.

A Practical Index Review Checklist

Before adding another index, answer these questions from a real plan:

1. Which predicate is actually in Index Cond?
2. Which predicates are still in Filter?
3. How many rows did the index return?
4. How many rows were removed after filtering?
5. Did the query need a separate Sort?
6. Did the index help the ORDER BY LIMIT path stop early?
7. Are estimated rows close to actual rows?
8. Are the slow tenants or accounts different from the average tenant?
9. Would a partial index match the stable query predicate?
10. Would a covering index reduce heap fetches, or just make a weak index larger?
11. What write overhead does the new index add?
12. How will the index be deployed, measured, and possibly removed?

The answer should be visible in evidence: plans, row counts, buffers, production query frequency, and write overhead.

The Short Version

Indexes fail to fix slow queries when they do not match the real access pattern.

The index may be too broad. The filtered value may be too common. The column order may not match the query. The ORDER BY LIMIT may still require sorting. The planner's row estimates may be wrong. The index may return many rows and leave the expensive work to filters and heap fetches.

The fix is not "add more indexes."

The fix is to read the plan, identify which work remains expensive, and choose an index, query rewrite, statistics fix, or rollout plan that matches that evidence.