Postgres Slow Query on Indexed Column, Why

Having an index on a column doesn’t guarantee PostgreSQL will actually use it, or that using it will make your query fast. When a query stays slow despite an index existing on the exact column being filtered or joined on, the planner usually has a good — if non-obvious — reason for its decision.

The Problem

You add an index on a column used in a WHERE clause or JOIN, expecting a significant speedup, but the query still takes just as long as before:

CREATE INDEX idx_orders_customer_id ON orders(customer_id);

SELECT * FROM orders WHERE customer_id = 12345;
-- still slow, as if the index doesn't exist

Running \d orders in psql confirms the index exists and is correctly defined on the right column, which makes the continued slowness confusing.

Why It Happens

PostgreSQL’s query planner decides whether to use an index based on cost estimation, not simply “does an index exist on this column.” Several common reasons a query ignores an available index or stays slow anyway:

• The table statistics are outdated, so the planner’s estimate of how many rows match the filter is wrong, leading it to choose a sequential scan over an index scan even when the index would actually be faster
• The query returns a large enough portion of the table that a sequential scan genuinely is faster than jumping around with an index — for filters that match a large percentage of rows, this is often the correct choice, not a bug
• A function or type cast is applied to the indexed column in the query (e.g. WHERE customer_id::text = '12345'), which prevents PostgreSQL from using a standard index unless a matching expression index exists
• The index exists but is on the wrong combination of columns for a multi-column filter, so it can only partially help, or not at all, depending on column order in a composite index

The Fix

Start by looking at what the planner is actually doing, rather than guessing:

EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

Look for Seq Scan in the output when you expected Index Scan or Index Only Scan — this confirms whether the index is being skipped at all, versus being used but still slow for other reasons (like returning too many rows, or a slow subsequent join).

If statistics are stale, refresh them and re-run the query:

ANALYZE orders;

This alone resolves a surprising number of “index exists but isn’t used” cases, especially after large bulk inserts or deletes that weren’t followed by an automatic autovacuum analyze cycle.

If a function or cast is being applied to the column in your query, either rewrite the query to avoid it, or create an expression index that matches exactly what the query does:

CREATE INDEX idx_orders_customer_id_text ON orders((customer_id::text));

If you’re filtering on multiple columns together, check whether a composite index in the right column order would serve the query better than separate single-column indexes, since PostgreSQL can only use a composite index efficiently for the leftmost columns present in the query’s conditions:

CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

Still Not Working?

If EXPLAIN ANALYZE shows the index scan is being used but the query is still slow, the bottleneck may be elsewhere — a subsequent join, a large number of matching rows genuinely requiring that much I/O, or disk-level performance rather than query planning at all. Compare the estimated row count in the plan against the actual row count returned; a large gap between the two is a strong signal that outdated statistics, not the index itself, are steering the planner toward a suboptimal strategy.