Why does PostgreSQL sometimes ignore an index you created?

Why does PostgreSQL sometimes ignore an index you created?

A common assumption is:

“If a column has an index, PostgreSQL should use it.”

But PostgreSQL does not work that way.

An index is only one possible access path.

Before executing a query, PostgreSQL's query planner estimates the cost of different plans and chooses the one it believes will be cheapest.

That might be:

• Sequential Scan
• Index Scan
• Index Only Scan
• Bitmap Index Scan
• Parallel Sequential Scan

So sometimes PostgreSQL sees your perfectly valid index and deliberately decides:

“Scanning the table is cheaper.”

Consider this example:

CREATE INDEX idx_users_status
ON users(status);

And imagine the table contains 10 million users:

active   = 7,000,000
inactive = 2,900,000
banned   =   100,000

Now compare these queries:

SELECT *
FROM users
WHERE status = 'banned';

and:

SELECT *
FROM users
WHERE status = 'active';

Both use the indexed status column.

But PostgreSQL may choose very different execution plans.

For banned, only around 1% of rows match.

Using the index can make sense because PostgreSQL can locate a relatively small set of rows instead of scanning millions of unrelated rows.

For active, around 70% of the table matches.

Now using the index may mean:

1. Traverse the index.
2. Find millions of matching row locations.
3. Visit millions of table pages to retrieve those rows.

At that point, reading the table sequentially may simply cost less.

This is where selectivity becomes important.

A highly selective condition returns a small percentage of the table.

WHERE email = '[e-mail]'

That is usually a great candidate for an index.

A low-selectivity condition might match most of the table.

WHERE status = 'active'

An index may provide little advantage.

But how does PostgreSQL know how many rows are likely to match?

Statistics.

PostgreSQL collects information about the data distribution in a table.

The planner uses those statistics to estimate things such as:

• how many rows a condition will match
• common values
• value distribution
• number of distinct values
• relationships that affect selectivity

That is why stale statistics can result in poor plans.

You can refresh them with:

ANALYZE users;

And this brings us to one of the most useful tools for PostgreSQL performance work:

EXPLAIN ANALYZE
SELECT *
FROM users
WHERE status = 'banned';

EXPLAIN shows the plan PostgreSQL intends to use.

EXPLAIN ANALYZE actually executes the query and shows what really happened.

Two numbers I pay particular attention to are:

Estimated rows

What PostgreSQL thought would happen.

Actual rows

What really happened.

If the planner estimates:

rows = 100

but the query actually returns:

rows = 100,000

that difference is a clue.

The planner may be making decisions using an inaccurate picture of the data.

There is another important misconception:

More indexes do not automatically mean faster databases.

Every index has a cost.

When you:

INSERT
UPDATE
DELETE

PostgreSQL may also need to update the relevant indexes.

Indexes consume storage, add write overhead, and create additional structures the planner has to consider.

So the goal is not:

“Index every column used in a WHERE clause.”

The better approach is:

1. Identify a slow query.
2. Run EXPLAIN ANALYZE.
3. Understand the execution plan.
4. Check row estimates and actual rows.
5. Look at selectivity and data distribution.
6. Decide whether the query or index should change.
7. Measure again.

Composite indexes introduce another layer.

For example:

CREATE INDEX idx_orders_customer_status
ON orders(customer_id, status);

The order of columns matters because the index structure is organized around those columns.

And sometimes PostgreSQL can combine multiple indexes using bitmap scans rather than using one composite index.

There are also partial indexes:

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

Instead of indexing every order, you can index only the subset important to a particular workload.

And covering indexes can sometimes allow PostgreSQL to answer a query without visiting the table heap at all.

The deeper I go into PostgreSQL performance, the more one idea stands out:

An index does not tell PostgreSQL what to do.

It gives the planner another option.

The planner still has to decide whether that option is actually cheaper.

So when PostgreSQL ignores an index, I would not immediately ask:

“Why isn't PostgreSQL using my index?”

I would first ask:

“What does PostgreSQL know about my data that makes another plan look cheaper?”

That question usually leads to a much more interesting investigation.