We added an index. Why is the full table scan still faster?

An index tells you where rows are, not that reading them will be cheap

Suppose a library has 80 million cards. A catalog locates ten cards without checking every shelf. But consulting it to retrieve 62 million cards may add work.

The database faces the same choice. With a B-tree index on status, it can locate index entries for PAID. But SELECT * needs columns that are stored in the heap. The index entries identify table rows, and those rows may be scattered across the file. A scan that follows many index pointers can revisit heap pages in an inconvenient order, with considerable per-row and page-access overhead.

A sequential scan reads heap pages in storage order, checks visibility and status, and returns matches. It examines rows that will not be returned, which sounds wasteful. Yet scanning many pages in a predictable order can be cheaper than finding millions of locations through an index and then visiting their table pages. PostgreSQL explicitly compares these access patterns in its EXPLAIN guide.

The useful question is not whether an index exists. It is how much work the query avoids by using it.

Change the question, change the plan

For the original dashboard query, about 62.4 million of 80 million rows match. There is little filtering benefit. Now ask for an unusual state such as REQUIRES_REVIEW that appears on only 0.05 percent of orders. That is 40,000 candidates. If the heap fetches are reasonably localized or the required pages are already cached, the index may be substantially better. The direction is plausible, not a guarantee from selectivity alone.

Or ask for only ten recent rows:

SELECT id, created_at
FROM orders
WHERE status = 'PAID'
ORDER BY created_at DESC, id DESC
LIMIT 10;

Now an index ordered by the relevant fields might stop after finding ten qualifying rows rather than reading millions. Whether it does depends on the key order and predicates, which is the subject of the next lesson. The important change here is the amount of data the request actually needs.

Even returning only id instead of SELECT * changes the cost model. Narrower results save network and row materialization. A carefully designed covering index might avoid most heap visits on sufficiently stable pages. We will examine that claim in the fifth lesson rather than treating an index as automatically covering.

The number to look for is not an index hit count

Start with the actual production query, not a simplified one whose predicate is different. Use a safe replica or bounded staging dataset if executing the full query would be disruptive:

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

EXPLAIN without ANALYZE shows the proposed plan. ANALYZE executes the query and adds observed timings and row counts, so it is not a harmless dry run for an expensive statement. BUFFERS helps distinguish cached block activity from reads. The planner's estimated cost is not a millisecond figure. Compare the estimated and actual rows, actual work, and total execution time rather than reading one cost number as a latency guarantee. See the PostgreSQL execution-plan documentation.

For this example I would compare an ordinary plan with a diagnostic plan that discourages sequential scans in an isolated session. That is an investigation technique, not a recommendation to leave enable_seqscan disabled in production. Measure both with realistic cache warmth and concurrency. If you fetch 62 million rows over the application network, sending and decoding the result can dominate both database scan strategies. Measure that separately.

There may also be a middle strategy. PostgreSQL can combine an index lookup with a bitmap heap scan, organizing heap page visits rather than fetching each tuple in raw index order. A bitmap scan still reads heap pages. It is not an index-only scan, and because bitmap-driven access does not preserve index order it may require a later sort. This option matters when a meaningful but not tiny fraction of rows qualifies.

Would you keep the index?

Not on the strength of this query alone. An index has write and storage costs. Inserts and updates may need to maintain it, and its pages compete for cache. Removing it without examining other consumers could also hurt a selective operational query that uses the same field.

I would collect the important query shapes, their frequencies, rows returned, actual access paths, and index write burden. With mostly broad status queries, a single-column B-tree may not repay its maintenance cost. A partial index for a rare operational state, a composite index for a bounded ordered lookup, or a precomputed report could make more sense, depending on the workload.

The Senior-level answer is not “indexes are good for reads.” It is that an index is another route to data, and that route has its own cost. A Staff-level answer adds measurement under the real query mix and avoids adding structures whose operational expense exceeds their benefit.

We know why an index can lose to a scan. But the dashboard now asks for the latest fifty paid or refunded orders for one merchant. There is an index with all the columns. Why does the database still sort?