Diagnosing and Fixing a Production Seq Scan

1. Diagnosis

Reading bottom-up: Seq Scan on payments reads all 38 million rows and applies merchant_id = 9821 AND status = 'settled' as a post-read Filter, discarding 37,152,779 of them (Rows Removed by Filter) to keep 847,221. That scan alone accounts for essentially all the wall-clock time (3862ms of the 3991ms total). On top of it, a Sort node orders all 847,221 surviving rows by created_at DESC before the Limit takes the top 25 — even though only 25 rows are ultimately needed, the plan currently has to materialize and sort the full filtered set first, because nothing handed it the rows pre-sorted.

The estimated rows=849380 is suspicious independent of the slowness because it implies the planner thinks roughly 2.2% of a 38-million-row table matches merchant_id = 9821 AND status = 'settled' — a very large fraction for what's presumably a single-merchant equality filter combined with one status value out of presumably several. If that estimate is close to actual (and here actual rows=847221 confirms it's not a stale-statistics problem — estimated and actual are close), it tells us status = 'settled' is not very selective on its own (a large share of payments are settled) and merchant_id = 9821 may be a fairly high-volume merchant — worth knowing because it affects whether a plain B-tree index on (merchant_id, status, created_at) will be selective enough to be worth using here, versus one where an index scan fetching 847k rows individually could itself become expensive if it weren't for the LIMIT 25 letting the engine stop early once sorted order is available.

2. Index design

CREATE INDEX idx_payments_merchant_status_created
  ON payments (merchant_id, status, created_at DESC);

Column order follows the composite-index rule directly: merchant_id and status are both tested with equality, so they come first (in either order relative to each other, but both before the range/sort column) to narrow the search as much as possible; created_at is the column used for ORDER BY, not for an equality/range filter here, so it goes last, sorted DESC to match the query's ORDER BY created_at DESC exactly. Putting created_at before status would have wasted status's ability to narrow further, since once the index applies a non-equality dimension it can no longer use a trailing column to narrow within it the same way.

3. Why the Sort node disappears

Because the index is built with created_at DESC as its trailing key, rows matching a given (merchant_id, status) prefix are already stored in exactly the order the query's ORDER BY wants — the database can walk the index in its natural stored order and stop after 25 rows, with no separate sort step needed at all. The resulting plan should look roughly like:

Limit
  ->  Index Scan using idx_payments_merchant_status_created on payments
        Index Cond: (merchant_id = 9821 AND status = 'settled'::text)

No Sort node, because the index already produced sorted output; no full materialization of 847k rows, because the index scan can stop as soon as 25 rows satisfying the LIMIT have been produced in the correct order. This is the same mechanism as the keyset-pagination technique: a composite index whose trailing column matches ORDER BY turns an expensive materialize-then-sort into a scan that's already in the right order.