Why is my query still slow when the column has an index?

"I added an index on the column and the query is still slow." It's one of the most common questions on every database forum. The index exists, but the planner either can't use it or has decided that using it isn't worth it. Here are the usual reasons, with PostgreSQL examples (the same rules apply to MySQL, SQL Server and Oracle).

Always start with the plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE lower(email) = 'ana@example.com';

If you see Seq Scan where you expected Index Scan, one of the following is why.

1. You wrapped the column in a function

-- Index on email can't help:
WHERE lower(email) = 'ana@example.com'
WHERE created_at::date = '2026-09-26'
WHERE amount + 0 > 100

The index stores email, not lower(email). Either rewrite the predicate so the column stands alone:

WHERE created_at >= '2026-09-26' AND created_at < '2026-09-27'

…or index the expression you actually query:

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

2. The types don't match

Comparing a varchar column to a number, or a bigint column to a numeric parameter, can force a conversion on the column side, which works like #1. In SQL Server, comparing a varchar column to an nvarchar parameter is the classic case (it often turns a seek into a scan). Check the parameter types your ORM or driver actually sends.

3. Leading wildcards

WHERE name LIKE '%smith'

A B-tree is sorted from the first character, so it can't search for a suffix. LIKE 'smith%' can use it (in PostgreSQL, with the C collation or a text_pattern_ops index). For contains and suffix searches, use a trigram index (pg_trgm) or full-text search.

4. Wrong column order in a composite index

CREATE INDEX ON orders (customer_id, created_at);

WHERE customer_id = 7 AND created_at > now() - interval '7 days'  -- great
WHERE created_at > now() - interval '7 days'                      -- can't seek

An index on (a, b) works like a phone book sorted by last name, then first name. Searching by first name alone means reading the whole book. Put equality columns first, range columns last.

5. The query matches too many rows

If a predicate returns 30% of the table, reading those rows through the index means hundreds of thousands of random page fetches. A single sequential scan is cheaper, and the planner is right to skip your index.

WHERE status = 'active'   -- 92% of rows are active

Indexes help selective predicates. For skewed columns, a partial index on the rare values works well:

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

6. Statistics are stale

The planner chooses based on estimated row counts. After a bulk load or a big delete, the statistics describe a table that no longer exists. Compare rows= (estimated) with actual rows= in EXPLAIN ANALYZE. When they're off by 100× or more, refresh the statistics:

ANALYZE orders;

7. The index is used, but each row needs a table lookup

The plan shows Index Scan, and the query is still slow because it fetches 50,000 rows and every one is a random heap read. Make the index covering so the query never touches the table:

CREATE INDEX ON orders (customer_id, created_at) INCLUDE (total, status);

Look for Index Only Scan (PostgreSQL) or no Key Lookup (SQL Server). In PostgreSQL, index-only scans also need a recently vacuumed visibility map. Heap Fetches: 0 in the plan tells you it's working.

8. OR and NOT IN

WHERE a = 1 OR b = 2 can't use a single index on a. It needs indexes on both columns (combined with a bitmap OR), or a UNION ALL rewrite. NOT IN (subquery) is hard to optimize and behaves surprisingly with NULLs; NOT EXISTS is almost always better.

9. The query isn't the slow part

EXPLAIN ANALYZE says 3 ms, but the app says 3 seconds. Then the time is going somewhere else: waiting on locks, a connection pool that's run out, the N+1 query pattern (1 query plus 500 small ones), or sending 40 MB of rows over the network. Measure at the application boundary before tuning further.

Checklist

1. Run EXPLAIN (ANALYZE, BUFFERS), and compare estimated with actual rows.
2. Keep indexed columns bare in the predicate, with matching types.
3. Order composite indexes with equality columns first and range columns last.
4. Accept a scan when the predicate isn't selective, or use a partial index.
5. Cover the hot query with INCLUDE to avoid table lookups.