I Added an Index and Postgres Still Does a Seq Scan (Here's Why)

You have a slow query. You add an index. You run the query again, check the plan, and there it is: Seq Scan.

The usual reaction is "Postgres is ignoring my index." Sometimes that's true, and something is wrong. But a lot of the time, Postgres made the right call, and the real problem is somewhere else.

I reproduced four cases locally on Postgres 17 so we can look at real plans instead of guessing.

The Setup

A table of orders with a million rows. About 95% are completed and 5% are pending:

CREATE TABLE orders (
    id          serial PRIMARY KEY,
    customer_id int,
    status      text,
    created_at  timestamptz DEFAULT now()
);

INSERT INTO orders (customer_id, status, created_at)
SELECT (random() * 10000)::int,
       CASE WHEN random() < 0.95 THEN 'completed' ELSE 'pending' END,
       now() - random() * interval '365 days'
FROM generate_series(1, 1000000);

ANALYZE orders;

CREATE INDEX idx_orders_status ON orders (status);

I run every query with EXPLAIN (ANALYZE, BUFFERS). That runs the query for real and shows what actually happened.

What to Look At in a Plan

Plans look intimidating, but for this post you only need five things:

• The node type: Seq Scan, Index Scan, Bitmap Heap Scan
• rows= in the first parentheses (the estimate) vs the second (the actual). A big gap means the planner guessed wrong.
• cost=: the planner's estimate in arbitrary units. It picks the plan with the lowest cost.
• Buffers: how many 8 kB pages the query touched. shared hit means found in cache, read means fetched from outside shared buffers.
• Execution Time at the bottom

Case 1: The Index Exists, and Postgres Is Right to Skip It

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';

Seq Scan on orders  (cost=0.00..19824.00 rows=950933 width=25)
                    (actual time=0.026..134.500 rows=949820 loops=1)
  Filter: (status = 'completed'::text)
  Rows Removed by Filter: 50180
  Buffers: shared hit=7324
Execution Time: 170.498 ms

There is an index on status, and Postgres didn't use it. Why?

Because this query returns 95% of the table. Going through an index to fetch almost every row is more work than just reading the table from start to finish. Look at the estimate: 950,933 rows predicted, 949,820 actual. The planner knew exactly what it was doing.

To check, I told Postgres to avoid seq scans and ran it again:

SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'completed';
RESET enable_seqscan;

Bitmap Heap Scan on orders  (cost=10654.16..29864.82 rows=950933 width=25)
                            (actual time=53.171..159.778 rows=949820 loops=1)
  ...
Execution Time: 207.462 ms

With the index forced, it took 207 ms instead of 170 ms. The planner's cost numbers pointed the same way: 19,824 for the seq scan against 29,864 for the bitmap scan.

(enable_seqscan = off doesn't forbid seq scans. It makes them very expensive in the planner's eyes. It's a diagnostic tool, not something to leave on.)

Takeaway: when a query returns a large share of the table, a Seq Scan is often the fastest plan.

Case 2: The Same Index, a Selective Query

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending';

Index Scan using idx_orders_status on orders
  (cost=0.42..6573.73 rows=49067 width=25)
  (actual time=0.127..15.953 rows=50180 loops=1)
  Index Cond: (status = 'pending'::text)
  Buffers: shared hit=7320 read=46
Execution Time: 18.441 ms

Same table, same index, different value. Now Postgres uses the index and finishes in 18 ms instead of 170 ms.

Look at the Buffers line, though. It touched 7,366 buffers, about the same as the 7,324 the seq scan read. The pending rows are scattered randomly through the table, so even though only 5% of rows match, the query still visits almost every page.

My reading is that it's faster mainly because it processes and returns far fewer rows, not because it reads fewer pages. "Few matching rows" doesn't automatically mean "few pages touched."

Case 3: The Index Is Fine, the Query Isn't

This is the one that causes real production slowdowns. I added an index on customer_id:

CREATE INDEX idx_orders_customer ON orders (customer_id);

Now two queries that look like they do the same thing:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id::text = '42';

The first one:

Bitmap Heap Scan on orders  (cost=5.20..371.40 rows=100 width=25)
                            (actual time=0.140..0.363 rows=104 loops=1)
  Buffers: shared hit=104 read=3
Execution Time: 0.402 ms

The second one:

Gather  (cost=1000.00..16115.67 rows=5000 width=25)
        (actual time=2.043..53.119 rows=104 loops=1)
  Workers Planned: 2
  ->  Parallel Seq Scan on orders
        Filter: ((customer_id)::text = '42'::text)
        Rows Removed by Filter: 333299
        Buffers: shared hit=7324
Execution Time: 53.164 ms

Same 104 rows. 0.4 ms against 53 ms, about 130x slower, and the second query had to bring in parallel workers just to scan the whole table.

The cast on the column is the reason. The index stores customer_id values as integers, and customer_id::text is a different expression that the index knows nothing about, so Postgres can't use it.

Also look at the estimates. For the second query, the planner expected 5,000 rows and got 104. When you wrap a column in a function, Postgres loses its statistics for it and starts guessing.

The fix here is the obvious one: compare customer_id = 42 and let the integer index do its job. If you genuinely need to query on an expression, Postgres supports indexes on expressions, but I didn't test that here.

The same trap shows up with WHERE lower(email) = ..., WHERE date(created_at) = ... and similar.

Case 4: A Partial Index for the Rare Value

If you only ever query for the rare value, you don't need to index all 1,000,000 rows. You can index only the ones you care about:

CREATE INDEX idx_orders_pending ON orders (id) WHERE status = 'pending';

SELECT pg_size_pretty(pg_relation_size('idx_orders_status'))  AS full_index,
       pg_size_pretty(pg_relation_size('idx_orders_pending')) AS partial_index;

 full_index | partial_index
------------+---------------
 6904 kB    | 1120 kB

The partial index is about 6x smaller, because it only contains the 5% of rows where status = 'pending'. A smaller index means less to store, less to cache, and less to update on every write.

A Checklist for the Next Seq Scan You See

1. Is the query returning a big share of the table? Then a Seq Scan may be correct.
2. Compare the estimated rows to the actual rows. A big gap points to stale statistics (try ANALYZE) or a function or cast on the column.
3. Is the column wrapped in a function or cast? Rewrite the query, or use an expression index.
4. Do you only query for a small, specific slice of rows? Consider a partial index.
5. Not sure the planner chose well? Test with SET enable_seqscan = off in a session, compare the times, then RESET it.

The Takeaways

• A Seq Scan is not automatically a problem. For queries that return most of the table, it's often the best plan.
• The estimated and actual row counts are the fastest way to see if the planner is confused.
• A cast or function on an indexed column can make the index useless, and the damage here was about 130x.
• Few matching rows doesn't always mean few pages read.

These numbers come from one run on my laptop with a warm cache, so your timings will differ. The relative differences are what matter, so run the examples yourself.

Comments

[Lycore Development]

Case 1 vs case 2 is a really clear way to show that the planner is usually right. For the 95/5 split like this one, a partial index such as CREATE INDEX ON orders (created_at) WHERE status = 'pending' is often the better fit, since it is tiny and only covers the rows you actually query. We also check random_page_cost on SSD-backed setups, because the default of 4 can make the planner shy away from indexes that would win. Did you try either on the same table?

[Odinaka Robert Nnamani]

The buffers line being nearly equal for 5% of rows explains it, scattered pending rows still touch almost every heap page.