Why your PostgreSQL index isn't making your query faster

Knowing that a column has an index tells you little. What matters is whether that index fits the way PostgreSQL runs 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's tempting to assume PostgreSQL will use the index. But if 95 percent of the rows are completed orders, an index lookup still has to visit most of the table, and reading it start to finish can be cheaper.

So the index existing doesn't decide the plan. The planner does, based on what it expects each option to cost.

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 a query is slow, find out how many rows the condition really matches before worrying about 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.

Build the index for the way the query reads the data. Which columns happen to appear in it is a weaker guide.

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 earn its keep against a real workload. A column showing up in a WHERE clause isn't reason enough.

6. Use EXPLAIN before guessing

Before you touch the schema, look at the execution plan.

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

Then check:

• 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?

That beats adding and dropping indexes on a hunch.

7. Estimated rows versus actual rows can expose planner problems

Imagine an execution plan that effectively says:

estimated rows: 100
actual rows:    500000

A gap like that means the planner picked its plan from a bad estimate of the data.

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.

So the fix isn't always an index. Sometimes the planner needs fresher statistics about your data.

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 cost is a wider index, which takes more storage and adds to the work done on every write.

10. Think about the whole workload

A benchmark can look excellent while production gets worse.

In production, that one query shares the database with customer searches, order lists, dashboards, payments, reports and background jobs.

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

A better question to ask:

It tells you far more than which index makes one benchmark look fastest.

11. A workflow that works

1. Reproduce the real query. Keep the actual filters, joins, ordering, selected columns, and limit.
2. Run EXPLAIN ANALYZE. Get the plan before you guess.
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. Count how many rows each condition 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 wider workload. Make sure the fix didn't create a new bottleneck somewhere else.

Before you add the next index

It's tempting to answer every slow query with CREATE INDEX. Ask these first:

• What is the application doing when this query runs?
• What does the data distribution look like?
• What does the planner believe, and what does the execution plan show?
• Which operation is the expensive one?
• What happens to writes if I add this index?
• Will this still hold up when the table is ten times bigger?

A query that takes 30 milliseconds on 100,000 rows can behave very differently at 50 million.