Why Your PostgreSQL Index Is Not Making Your Query Faster

The important question is not “Does this column have an index?” The better question is “Does this index match the way PostgreSQL actually executes this query?”

PostgreSQL can choose among sequential scans, index scans, bitmap scans, index only scans, joins, sorts, and other strategies. The planner estimates the cost of those choices using the query shape, table statistics, selectivity, and expected work.

1. An index is not automatically faster

Imagine an orders table with millions of rows:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT,
    status VARCHAR(20),
    total_amount NUMERIC,
    created_at TIMESTAMP
);

Now create an index:

CREATE INDEX idx_orders_status
ON orders(status);

And run:

SELECT *
FROM orders
WHERE status = 'completed';

It is tempting to assume PostgreSQL must use the index. But if 95 percent of the rows are completed orders, an index lookup may still require a huge amount of table access. Reading the table sequentially can be cheaper.

The existence of an index does not determine the execution plan. The optimizer does.

2. Selectivity matters

Selectivity describes how effectively a condition narrows the result set.

WHERE id = 10025

is usually highly selective. A primary key normally points to one row.

WHERE status = 'completed'

may be far less selective because a large portion of the table can match.

When diagnosing performance, ask how many rows the predicate actually matches. Do not stop at asking whether the column is indexed.

3. The order of columns in a composite index matters

Suppose the application frequently runs:

SELECT *
FROM orders
WHERE customer_id = 123
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC;

A useful index for this access pattern may be:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

That is not equivalent to putting created_at first. A multicolumn B tree index is ordered, so the leading columns strongly influence how efficiently PostgreSQL can navigate the index.

Think about the index in terms of the real access path. If the application first identifies a customer and then searches that customer's orders by time, the index should reflect that pattern.

Design indexes around query patterns, not merely around table columns.

4. Do not blindly create one index per WHERE condition

You might create three indexes:

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at);

But the important query may actually be:

SELECT *
FROM orders
WHERE customer_id = 123
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 50;

PostgreSQL can combine indexes in some situations with bitmap scans, but multiple independent indexes do not automatically produce the best plan. A composite index can be better for a dominant access pattern, while being worse if it does not match the broader workload.

5. Indexes are not free

Every index consumes storage and has maintenance cost. Inserts, updates, and deletes can require PostgreSQL to maintain affected indexes.

This matters especially for write heavy systems such as payment platforms, order processing, inventory systems, POS systems, and event ingestion pipelines.

An index should exist because a real workload justifies its cost, not because the column appears in a SQL statement.

6. Use EXPLAIN before guessing

One of the best habits a backend engineer can develop is to inspect the execution plan before changing the schema.

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 123
  AND status = 'completed'
ORDER BY created_at DESC
LIMIT 50;

Now look for evidence:

• Did PostgreSQL use the index?
• How many rows did it estimate?
• How many rows did it actually process?
• Did sorting dominate the query?
• Did a join become the expensive operation?
• Did the query perform a large amount of heap access?

The plan is evidence. It is much better than repeatedly changing indexes based on intuition.

7. Estimated rows versus actual rows can expose planner problems

Imagine an execution plan that effectively says:

estimated rows: 100
actual rows:    500000

That difference is a major signal. PostgreSQL's planner made a decision based on an inaccurate estimate of the data distribution.

Before adding several indexes, check whether the planner has current statistics. Running ANALYZE on tables with changed data distributions can give the planner better information.

Database performance is not only about indexes. It is also about whether the optimizer understands the data it is operating on.

8. Sometimes the query needs a different kind of index

Expression indexes

Consider:

SELECT *
FROM users
WHERE LOWER(email) = 'john@example.com';

An index on raw email does not necessarily match the expression used by the query. An expression index can:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Partial indexes

If a table contains millions of orders but only a small percentage are pending, a partial index can target the subset that matters:

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

Partial indexes can reduce index size and focus maintenance on the rows relevant to a specific workload. They still need to be justified by actual query patterns.

9. Covering indexes and index only scans

Suppose the application frequently runs:

SELECT customer_id, created_at
FROM orders
WHERE customer_id = 123;

An index containing both requested columns can sometimes allow PostgreSQL to satisfy the query from the index itself:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at);

This can enable an index only scan when PostgreSQL's visibility requirements are satisfied. The benefit is potentially less heap access.

The tradeoff is a wider index. Wider indexes consume more storage and can increase write and maintenance cost. Performance work is almost always a tradeoff.

10. Optimize the workload, not just one query

A benchmark can look excellent while production gets worse.

Production is a workload, not a single query. A system may have customer searches, order listings, dashboards, transaction processing, reporting, and background jobs all competing for the same database resources.

An index that makes one query faster can add write overhead or change planner choices for other queries.

The right question is:

That question is much more useful than asking which index makes one benchmark look fastest.

11. A practical PostgreSQL indexing workflow

1. Reproduce the real query. Keep the actual filters, joins, ordering, selected columns, and limit.
2. Run EXPLAIN ANALYZE. Start with evidence rather than assumptions.
3. Compare estimated and actual rows. Large differences can indicate bad statistics or data distribution assumptions.
4. Find the expensive operation. It may be a scan, sort, join, aggregation, or heap access.
5. Measure selectivity. Understand how many rows each condition actually matches.
6. Design around the access pattern. Consider column order, ranges, sorting, partial indexes, expressions, and covering columns.
7. Measure again. Compare actual execution time and resource usage.
8. Test the broader workload. Make sure the optimization does not create a new bottleneck elsewhere.

The deeper lesson

Database optimization is not an index creation exercise. It is a reasoning exercise.

The strongest backend engineers do not respond to every slow query with:

CREATE INDEX ...

They ask:

• What is the application actually doing?
• What does the data distribution look like?
• What does the planner believe?
• What does the execution plan show?
• Which operation is actually expensive?
• What happens to writes if I add this index?
• Will this still behave well when the table grows ten times larger?

A query that runs in 30 milliseconds on 100,000 rows can behave very differently at 50 million rows. The goal is not simply to make today's query fast. The goal is to make the system behave predictably as the workload evolves.