Database Indexes: Why Your Query Is Still Slow

Adding an index feels like the obvious answer to a slow query. Sometimes it is. Sometimes the query still scans a large part of the table, sorts far more rows than expected, or spends most of its time somewhere else entirely.

An index is a tool for a particular access pattern. To choose one well, start with the query the application actually sends and the data it actually stores.

Capture the real query first

An endpoint called recent orders might sound simple, but its query can include an account filter, status condition, descending timestamp, and pagination limit. An index on an unrelated column will not solve that workload.

Consider this PostgreSQL example:

SELECT id, created_at, total_cents
FROM orders
WHERE account_id = $1
ORDER BY created_at DESC, id DESC
LIMIT 25;

Before changing anything, record the latency and test representative accounts. A tiny development database can hide the cost that appears when one account owns hundreds of thousands of records.

Also separate database time from connection waiting, network transfer, and application serialization. A faster query cannot eliminate a delay that happens before the query starts.

Read the plan instead of guessing

Use EXPLAIN to inspect the planned work. In a safe environment, EXPLAIN (ANALYZE, BUFFERS) adds execution observations. Remember that ANALYZE runs the statement; it is not a harmless preview for a write.

PostgreSQL's EXPLAIN documentation describes the plan output and measurement options.

Look for the number of rows read compared with the number returned. Check expensive sorts, repeated inner operations, and large differences between estimated and actual row counts. A plan is more useful when connected to a question: why does this endpoint inspect so much data to return twenty-five orders?

Do not judge an index only by whether its name appears in the plan. The goal is less useful work and better observed latency, not an index scan badge.

Match filtering and ordering together

For the example query, a candidate B-tree index is:

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

The account is an equality filter, followed by the fields used to order results. Including id gives the ordering a stable tie-breaker when timestamps are equal.

Column order affects which queries benefit. A multicolumn index should be designed around its intended access patterns rather than treated as a collection of independently indexed fields. PostgreSQL explains the details in multicolumn indexes and indexes with ordering.

Measure the candidate on realistic data before declaring it the answer. A different query shape, distribution, or filter can need a different design.

A sequential scan can be reasonable

If a query needs most of a table, reading it sequentially may be cheaper than visiting many index entries and table rows. Small tables can also be fast enough without the access path you expected.

That is a reason to examine selectivity rather than forcing an index. Ask what percentage of rows satisfy the filter and whether the query returns more data than the caller needs.

For a dashboard, reducing the selected columns or narrowing the date range may help more than adding another broad index. For repeated aggregates, an explicitly maintained summary can be a better design, provided its freshness requirements are clear.

Watch how expressions change the query

An application can unintentionally change an indexed lookup into a different operation by wrapping a column in a function or comparing incompatible types.

Inspect the actual SQL and parameter types. If case-insensitive lookup is required, choose a consistent data model and an appropriate index strategy for that operation. Do not scatter ad hoc normalization through queries and expect every existing index to remain useful.

Keep statistics and maintenance in the investigation as well. An optimizer works from estimates, and a major change in the data deserves another look at the plan.

Indexes have an ongoing cost

Each additional index consumes storage and adds work to writes. Keep a reason for each one and review overlaps before adding another near-duplicate.

Test the migration strategy on a realistic environment, observe writes as well as reads, and keep a rollback path. After deployment, compare the same workload with the earlier baseline.

A good index change has an explanation you can verify: this query used to inspect too many rows, this access path reduces that work, and the measurements show the improvement.