Skip to content

You Added an Index. Why Is the Query Still Slow?

3 min read3 views[Backend]
Cover image for "You Added an Index. Why Is the Query Still Slow?"

A query is slow. You add an index to the column in its WHERE clause. You run it again.

Still slow.

At this point it's tempting to assume that PostgreSQL is ignoring an obvious improvement. Sometimes the planner does make a poor estimate. But first, consider a less satisfying possibility: the index doesn't remove the expensive part of your query.

My B-tree article explains how an index organizes values for efficient lookup. This article is about the next question: whether that lookup actually helps the workload in front of you.

Start with the query that actually runs

Take a hypothetical admin screen:

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

An index on account_id might help locate the account's orders. But if that account has years of history, the database may still need to process a large matching set to find the newest fifty.

A candidate worth measuring is:

CREATE INDEX orders_account_recent_idx
ON orders (account_id, created_at DESC, id DESC);

That design reflects a particular access pattern: one account, newest records first, with a stable ordering for equal timestamps. It is not a universal “orders index.” A dashboard summarizing every account asks a different question.

For multicolumn B-tree indexes, leading-column constraints are especially useful for limiting the scanned range. Don't turn that into “later columns can never help”: PostgreSQL can also consider strategies such as skip scans in suitable cases. Measure the actual plan. PostgreSQL multicolumn indexes

Read the plan before changing more things

For a representative SELECT, use:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
ORDER BY created_at DESC, id DESC
LIMIT 50;

ANALYZE executes the statement. Be careful with expensive queries, and especially with statements that modify data.

Compare estimated and actual row counts. Inspect repeated loops, filtering, sorting, and buffer activity. Node times are not independent numbers you can simply add together. A sequential scan can be appropriate when the query needs a large share of a table; an index scan is not automatically a victory. PostgreSQL's EXPLAIN guide

My first question would be: where is the plan doing work that the application doesn't actually need?

You indexed the column, but queried an expression

Consider this lookup:

SELECT id
FROM users
WHERE lower(email) = lower('Reader@Example.com');

A plain index on email is not an index on lower(email). PostgreSQL supports expression indexes for this situation:

CREATE INDEX users_email_lower_idx
ON users (lower(email));

That comes with maintenance cost on writes. It also doesn't decide your product's email comparison policy for you. Define the behavior first, then choose storage and indexing that match it. PostgreSQL expression indexes

The same review habit applies beyond this example: compare what the query asks the database to compute with what the index actually stores.

Your measurements may describe the wrong workload

A local fixture containing twenty orders tells you very little about an account with two million orders.

For the example above, I would compare at least three accounts: one with almost no history, one with typical usage, and one unusually large account. I would also distinguish the first run from repeated runs, and measure the application endpoint as well as the SQL statement.

Suppose the database now answers quickly but the endpoint still takes a second. That is useful evidence. Look at connection-pool waiting, additional queries, serialization, response size, and network time before adding another index.

An improvement in one component doesn't guarantee an equal improvement in the entire request.

Every index needs a reason to exist

I would record the query pattern alongside the migration that adds this index:

Purpose: fetch the newest 50 orders for one account.
Compare: small, typical, and large account histories.
Check: read latency, write overhead, and index size.

That short note makes later maintenance easier. Someone changing the endpoint can understand why the index exists. Someone reviewing a new index can notice whether it duplicates an existing purpose.

The useful question isn't “Why won't the database use my index?” It's “Which work should this index eliminate, and does the measured plan show that happening?”

# Comments (0)

No comments yet. Be the first.

0/1000