Practice — SQL Query Optimization & Indexing (6 questions)

A reporting endpoint runs this query against a payments table with 38 million rows, and it has gone from ~60ms to over 4 seconds as the table grew:

SELECT id, amount_cents, created_at
FROM payments
WHERE merchant_id = 9821
  AND status = 'settled'
ORDER BY created_at DESC
LIMIT 25;

EXPLAIN ANALYZE output:

Limit  (cost=812004.10..812004.17 rows=25 width=24)
       (actual time=3991.203..3991.209 rows=25 loops=1)
  ->  Sort  (cost=812004.10..814127.55 rows=849380 width=24)
             (actual time=3991.201..3991.204 rows=25 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 27kB
        ->  Seq Scan on payments  (cost=0.00..798411.00 rows=849380 width=24)
                                  (actual time=0.018..3862.442 rows=847221 loops=1)
              Filter: (merchant_id = 9821 AND status = 'settled'::text)
              Rows Removed by Filter: 37152779
Planning Time: 0.201 ms
Execution Time: 3991.298 ms

There is currently no index on payments other than the primary key on id.

1. Diagnose exactly what the plan shows, node by node, and explain why the estimated rows=849380 for the seq scan is suspicious on its own, independent of the slowness.
2. Design a specific index (name the columns and their order) that fixes this query, and justify the column order using the rules for composite indexes.
3. Explain what specifically about your index also eliminates the separate Sort node in the plan, and what the resulting plan should look like at a high level.

Your team builds a composite index specifically to enable an index-only scan:

CREATE INDEX idx_orders_covering
  ON orders (customer_id, status, total_cents);

The query selects only customer_id, status, and total_cents, filtered on customer_id — every column it needs is in the index. The table was bulk-loaded overnight (2 million new rows) and has never been vacuumed since. EXPLAIN ANALYZE shows Index Only Scan in the plan, but also reports Heap Fetches: 1,842,000 — nearly one heap fetch per row the scan touches, even though the index alone contains every column the query needs.

Why does Postgres still visit the heap here, despite a fully covering index existing?