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 five 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.

What About Indexing created_at Instead?

A reader suggested a partial index on created_at, which fits better when you ask for something like the oldest pending orders. I tested it with a query that has an ORDER BY and a LIMIT:

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

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 20;

Before the new index, this was the best the planner could do:

Limit  (cost=2952.95..2953.00 rows=20 width=25) (actual time=140.350..140.357 rows=20 loops=1)
  Buffers: shared hit=3 read=7459
  ->  Sort  (...)  Sort Method: top-N heapsort  Memory: 27kB
        ->  Index Scan using idx_orders_pending on orders
              (actual time=1.269..132.395 rows=50180 loops=1)
Execution Time: 140.530 ms

It read all 50,180 pending rows and sorted them to return 20. After creating the index:

Limit  (cost=0.29..12.33 rows=20 width=25) (actual time=0.112..0.163 rows=20 loops=1)
  Buffers: shared hit=20 read=2
  ->  Index Scan using idx_orders_pending_created on orders
        (cost=0.29..30617.39 rows=50867 width=25)
        (actual time=0.109..0.153 rows=20 loops=1)
Execution Time: 0.200 ms

No sort, and only 20 rows read. Buffers dropped from about 7,462 to 22. One caveat on timing: the "before" run was a cold-cache run (note read=7459), so 140 ms exaggerates the gap. The row counts and buffers are the fairer comparison. The new index is the same size as the one on id, 1120 kB.

Without the LIMIT, the planner still picked the id index:

Index Scan using idx_orders_pending on orders  (cost=0.29..1699.30 rows=50867 width=25)
Execution Time: 17.112 ms

The estimated cost of reading every pending row through the created_at index was 30,617, against 1,699 for the id index (see the cost on the Index Scan line in the plan above). So the same partial index is a great fit for "oldest 20 pending orders" and the wrong one for "give me all pending orders." Which index helps depends on the query.

Case 5: Does Lowering random_page_cost Change Anything?

The same reader suggested checking random_page_cost. The default is 4, and on SSD storage that can make the planner too reluctant to use indexes. I tried 1.1 on the same table:

SHOW random_page_cost;   -- 4

SET random_page_cost = 1.1;
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'completed';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE status = 'pending';
RESET random_page_cost;

The plans didn't change:

• completed: still a Seq Scan, with the same estimated cost (19824.00) at both settings. A sequential scan doesn't depend on random_page_cost.
• pending: still the same Index Scan. Only its estimated cost moved, from 1699.30 to 1290.40.

The timings need a warning. The completed query took 191 ms at the default and 145 ms at 1.1, but it ran the identical plan both times, so that gap is run-to-run noise, not the setting. Don't read conclusions from a single run.

The other caveat is that this table fits entirely in cache (every Buffers line is hit), so this test says little about a real SSD system under load. On a production database, change random_page_cost only after comparing actual query times before and after.

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 single runs on my laptop, mostly with a warm cache (the one exception is noted in Case 4), so your timings will differ. The relative differences are what matter, so run the examples yourself.