You added the index and the query is exactly as slow as before because the database decided your index was the more expensive option — or, more often, because the query is written in a way that makes the index unusable no matter how good it is. An index is not a speed setting you flip on; it's a sorted data structure the planner uses only when the query's shape lines up with the index's shape.
Start from the slow query and its plan, never from a hunch about which column "should" be indexed. If EXPLAIN still shows a Seq Scan after you built the index, one of the mistakes below is the reason.
Build the index to match the whole query
A composite index is sorted by its columns left to right, like a phone book sorted by last name then first name. This query filters on account_id, then orders by created_at — so the index has to cover both, in that order:
SELECT id, created_at FROM orders
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;
CREATE INDEX orders_account_created_idx
ON orders (account_id, created_at DESC);With that index the planner can jump straight to account_id = $1 and walk rows already in created_at DESC order, so LIMIT 50 stops after 50 rows. Get the column order backwards — (created_at, account_id) — and it can't, because the rows for your account are scattered throughout.
| Plan symptom | What it usually means |
|---|---|
Seq Scan on a selective filter | Index missing, or unusable (see mistakes below) |
Huge Rows Removed by Filter | The filter isn't backed by the index |
Sort node after the filter | Index doesn't cover the ORDER BY |
| Index used but still slow | Fetching too many rows, or random I/O from a non-covering index |
The mistakes that quietly disable an index
Wrapping the indexed column in a function
WHERE lower(email) = 'a@b.com' cannot use an index on email, because the index stores email, not lower(email). The planner has to compute lower() for every row — a full scan. Either index the expression (CREATE INDEX ON users (lower(email))) or stop transforming the column in the predicate.
Type mismatches that force a cast
If account_id is a bigint and you pass a string, or compare a varchar column to a number, the database casts one side per row and the index sits idle. This is common when an ORM sends a parameter as text. The plan shows a Seq Scan with an implicit cast in the filter.
Ignoring the leftmost-prefix rule
An index on (a, b, c) serves queries filtering on a, or a, b, or a, b, c — but not a query filtering only on b. A query that filters on created_at alone gets nothing from (account_id, created_at). Reorder the columns, or add a separate index for the other access pattern.
Indexing everything "to be safe"
Every index is paid for on every INSERT, UPDATE, and DELETE, plus vacuum and storage. An index no query uses is pure write tax — check pg_stat_user_indexes for idx_scan = 0 and drop the dead ones.
Format the query first with a SQL formatter so the predicates are actually readable, run EXPLAIN ANALYZE on representative data, and keep only indexes tied to a query you can name. Plans end these arguments faster than index folklore does.
Cover photo by panumas nikhomkhai on Pexels.