Covering Indexes for ORDER BY and LIMIT #

A feed query — filter by account_id, order by created_at DESC, return 20 rows with four columns — runs as a bitmap scan of 90,000 rows, a top-N heapsort and 20 heap fetches, in 140 ms. One index later it is an index-only scan reading five pages in 0.05 ms, with no sort node at all. The index has to satisfy three requirements at once, and getting any of them wrong leaves a Sort or a heap access in the plan.

Index-only access is covered in covering index design; bounded sorts in top-N heapsort and LIMIT plans. This page combines them.

The Index Condition #

For WHERE a = $1 ORDER BY b DESC LIMIT n the index must:

  1. Lead with the equality columns (a), so the scan is bounded to the matching prefix.
  2. Continue with the ordering columns (b DESC) in an order the scan can follow. A B-tree can be read forwards or backwards, so (a, b) serves both ORDER BY b and ORDER BY b DESC; explicit directions matter only when different columns need different directions, such as ORDER BY b DESC, c ASC, which needs (a, b DESC, c ASC).
  3. Carry every other column the query touches, as key or INCLUDE columns, to make the scan index-only.

NULLS placement follows the direction: ASC implies NULLS LAST, DESC implies NULLS FIRST. A query that overrides it — ORDER BY b DESC NULLS LAST — needs a matching index, (a, b DESC NULLS LAST), or the planner adds a sort. The details are in descending and NULLS ordering in B-tree indexes.

When the index provides order, the Limit stops the scan after n rows: work is proportional to the page size, not to the number of matching rows. Add a unique tiebreaker column at the end of the ordering so pages are stable and keyset pagination can use a row comparison.

One index for filter, order and payload Four parts of the index left to right. Leading equality columns bound the scan. Ordering columns in the required direction supply the sort order. A unique tiebreaker makes pagination stable. INCLUDE columns carry the payload so no heap access is needed. account_id equality: bounds scan created_at DESC supplies ORDER BY id DESC stable tiebreaker INCLUDE (title, kind) payload: no heap miss any part and a Sort or heap fetch reappears

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, title, kind
FROM feed_items
WHERE account_id = 8812
ORDER BY created_at DESC, id DESC
LIMIT 20;

With only (account_id) indexed:

Limit  (actual time=139.2..139.2 rows=20 loops=1)
  ->  Sort  (actual time=139.2..139.2 rows=20 loops=1)
        Sort Key: created_at DESC, id DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Bitmap Heap Scan on feed_items  (actual rows=90412 loops=1)
              Recheck Cond: (account_id = 8812)
              Heap Blocks: exact=41204
              ->  Bitmap Index Scan on feed_items_account_idx  (actual rows=90412 loops=1)
        Buffers: shared hit=1204 read=40210
Execution Time: 140.1 ms
-- all 90,412 matching rows fetched and sorted to return 20

With the full covering index:

CREATE INDEX CONCURRENTLY feed_items_account_created_idx
  ON feed_items (account_id, created_at DESC, id DESC) INCLUDE (title, kind);
Limit  (actual time=0.03..0.04 rows=20 loops=1)
  ->  Index Only Scan using feed_items_account_created_idx on feed_items
        (actual time=0.03..0.03 rows=20 loops=1)
        Index Cond: (account_id = 8812)
        Heap Fetches: 0
        Buffers: shared hit=5
Execution Time: 0.06 ms
-- no Sort; the scan stops after 20 entries
What each part of the index removed Four lines are annotated. The Sort node disappears once the index supplies the order. The bitmap heap scan of 90 thousand rows disappears once the scan is bounded and stops at the limit. Heap Fetches zero shows the INCLUDE columns worked. Buffers fall from 41 thousand to five. Sort Method: top-N heapsort removed by index order Bitmap Heap Scan (rows=90412) removed by bounded scan + LIMIT Heap Fetches: 0 payload from INCLUDE columns Buffers: 41414 → 5 the whole point each plan element maps to one part of the index definition

Step-by-Step Resolution #

  1. Write down the query’s four parts: equality filters, range filters, ordering columns with directions, and returned columns.

  2. Compose the index as equality columns, then the range column if any, then ordering columns with directions, with the rest as INCLUDE. When a range filter and a different ordering column both exist, only one can be satisfied by the index; choose based on which reduces more rows, as discussed in range conditions and Index Cond boundaries.

  3. Match NULLS placement to the query’s ORDER BY, or omit the override so defaults line up.

  4. Add a unique tiebreaker at the end of the ordering columns.

  5. Create it concurrently and analyze:

    CREATE INDEX CONCURRENTLY feed_items_account_created_idx
  ON feed_items (account_id, created_at DESC, id DESC) INCLUDE (title, kind);
ANALYZE feed_items;

  6. Verify there is no Sort, no Heap Fetches, and buffers in single digits for one page.

  7. Switch pagination to keyset conditions so deep pages keep the same plan:

    WHERE account_id = 8812 AND (created_at, id) < ($last_created_at, $last_id)
ORDER BY created_at DESC, id DESC LIMIT 20;
Buffers per 20-row page With only account_id indexed, a page costs 41,414 buffers. Adding created_at to the index removes the sort and costs 28 buffers. Adding the payload as INCLUDE columns costs 5 buffers. Deep page 500 with OFFSET costs 9,204 buffers even with the covering index, while keyset pagination stays at 5. (account_id) only 41,414 buffers + created_at DESC in key 28 buffers + INCLUDE payload 5 buffers page 500 with OFFSET 9,204 buffers keyset pagination keeps every page at 5 buffers

Before and After #

-- BEFORE: index on (account_id) only
Limit → Sort (top-N heapsort) → Bitmap Heap Scan (90,412 rows)   Buffers: 41414    Execution Time: 140.1 ms

-- AFTER: (account_id, created_at DESC, id DESC) INCLUDE (title, kind)
Limit → Index Only Scan  Heap Fetches: 0                          Buffers: 5        Execution Time: 0.06 ms

When the filter is a range, not an equality #

Feeds filtered by a time window rather than a key — WHERE created_at >= now() - interval '7 days' ORDER BY score DESC LIMIT 20 — cannot have both the filter and the order served by one B-tree, because the range column must come first to bound the scan and then the second column is unordered within it. Three options, in rough order of preference: sort on the range column instead (ORDER BY created_at DESC), so one index serves both; precompute a bucket column (day) that turns the range into equality, giving (day, score DESC); or accept a bounded top-N sort over the filtered set, which is fine when the window is small.

Partial indexes help when the filter is a constant predicate rather than a parameter: WHERE archived = false is better expressed as a partial index WHERE archived = false than as a leading key column, keeping the index small and its order intact — the rules in partial index predicate matching rules.

Common Pitfalls #

Mismatched sort directions. (a, b) cannot serve ORDER BY b DESC, c ASC without a sort. Diagnostic signal: Sort above an index scan. Fix: declare directions in the index.

Missing tiebreaker. Ties shuffle between pages. Diagnostic signal: duplicate or missing rows across pages. Fix: end the ordering with a unique column.

INCLUDE columns used in conditions. They cannot bound or order a scan. Diagnostic signal: filter applied after the index scan. Fix: move them into the key.

Deep OFFSET. The scan still walks past the skipped rows. Diagnostic signal: buffers growing with page number. Fix: keyset pagination.

Frequently Asked Questions #

How do I remove the Sort node from an ORDER BY LIMIT query? #

Create an index whose leading columns match the equality filters and whose following columns match the ORDER BY columns and their directions. The scan then returns rows already ordered and the Limit stops it early.

Do I need DESC in the index for ORDER BY … DESC? #

Not for a single ordering column: a B-tree can be scanned backwards. You need explicit directions when different columns require different directions, or when the query overrides NULLS placement.

Should the returned columns be key columns or INCLUDE columns? #

Columns used in filters or ordering must be key columns. Columns that are only returned belong in INCLUDE, which keeps the key narrow while still allowing an index-only scan.

Up: Covering Index Design