Descending and NULLS Ordering in B-Tree Indexes #

CREATE INDEX … (created_at DESC) appears in a great many schemas, and in most of them it changes nothing. A PostgreSQL B-tree can be walked in either direction at the same cost, so an ascending index already serves a descending ORDER BY. There is a case where direction genuinely matters — and a related one involving NULLS FIRST that catches people far more often.

This page separates the two. The general column-ordering rules are in b-tree index optimization.

What a Backward Scan Covers, and What It Does Not #

An index stores keys in one physical order. Reading it backwards yields the exact reverse of that order, including the nulls placement. So for a single-column index:

index definition serves ASC serves DESC
(created_at) forward scan backward scan
(created_at DESC) backward scan forward scan

Both definitions serve both directions. The DESC keyword buys nothing here.

Multi-column indexes are where it stops being symmetric. An index on (a, b) provides the order a ASC, b ASC, and reading it backwards provides a DESC, b DESC. Neither is a ASC, b DESC. A query asking for mixed directions therefore needs an index whose declared directions match — or are the exact reverse of — the requested order.

Nulls placement follows the same logic. PostgreSQL’s default is NULLS LAST for ascending and NULLS FIRST for descending, chosen so a plain index read forward gives ascending-nulls-last and read backward gives descending-nulls-first. Ask for ORDER BY col ASC NULLS FIRST and no ordinary index can supply it.

What an index on (a, b) can and cannot order Four ORDER BY combinations against an index on a ascending b ascending. a ascending b ascending is served by a forward scan. a descending b descending is served by a backward scan. The two mixed-direction combinations require an explicit sort. index: (a, b) — both ascending ORDER BY a, b Index Scan — forward no Sort node ORDER BY a DESC, b DESC Index Scan Backward no Sort node ORDER BY a, b DESC Sort required needs an index on (a, b DESC) ORDER BY a, b NULLS FIRST Sort required needs (a, b NULLS FIRST) a backward scan reverses every column at once — it cannot reverse just one

Annotated Evidence #

The common case, where DESC in the index is unnecessary:

CREATE INDEX orders_created_idx ON orders (created_at);

EXPLAIN (ANALYZE)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;

Limit  (actual time=0.04..0.06 rows=20 loops=1)
  ->  Index Scan Backward using orders_created_idx on orders  (actual rows=20 loops=1)
Execution Time: 0.09 ms

No sort, and the LIMIT stopped after 20 index entries. An index declared DESC would produce Index Scan instead of Index Scan Backward and identical timing.

The mixed-direction case, where it does matter:

EXPLAIN (ANALYZE)
SELECT * FROM orders ORDER BY customer_id ASC, created_at DESC LIMIT 20;

Limit  (actual time=1842.60..1842.62 rows=20 loops=1)
  ->  Sort  (actual time=1842.59..1842.60 rows=20 loops=1)
        Sort Key: customer_id, created_at DESC
        Sort Method: top-N heapsort  Memory: 28kB
        ->  Seq Scan on orders  (actual rows=8000000 loops=1)
Execution Time: 1842.7 ms

Eight million rows read and sorted to return twenty. Build the matching index and the sort disappears:

CREATE INDEX orders_customer_created_desc_idx ON orders (customer_id, created_at DESC);

Limit  (actual time=0.05..0.08 rows=20 loops=1)
  ->  Index Scan using orders_customer_created_desc_idx on orders  (actual rows=20 loops=1)
Execution Time: 0.11 ms

Four orders of magnitude, from one keyword in the index definition.

Step-by-Step Workflow #

  1. Look for a Sort node above an index scan. That is the entire diagnosis — an index that supplies the ordering leaves no sort behind.

  2. Write out the full requested ordering, including implicit nulls placement:

    ORDER BY customer_id ASC, created_at DESC
  → customer_id ASC NULLS LAST, created_at DESC NULLS FIRST

  3. Create an index matching that ordering exactly, or its exact reverse. Both are usable, one forward and one backward:

    CREATE INDEX CONCURRENTLY orders_customer_created_desc_idx
  ON orders (customer_id ASC, created_at DESC);

  4. Handle non-default nulls placement explicitly when the query needs it:

    CREATE INDEX CONCURRENTLY orders_shipped_nulls_first_idx
  ON orders (shipped_at ASC NULLS FIRST);

  5. Re-check the plan for Index Scan or Index Scan Backward with no Sort, and confirm a LIMIT now terminates the scan early — that early stop is the real prize.

  6. Do not create both directions of the same index. They are redundant; one serves both readings.

Top-N with and without a direction-matched index Without a matching index, a sequential scan of eight million rows feeds a top-N sort that yields twenty rows. With the matching index, an index scan yields twenty rows directly and stops. index directions do not match Seq Scan · 8,000,000 rows top-N heapsort 20 rows 1842 ms index (customer_id, created_at DESC) Index Scan · stops after 20 entries 20 rows 0.11 ms

Before and After #

-- BEFORE
Sort  Sort Key: customer_id, created_at DESC   Sort Method: top-N heapsort
  ->  Seq Scan on orders  (actual rows=8000000)
Execution Time: 1842.7 ms

-- AFTER
Index Scan using orders_customer_created_desc_idx  (actual rows=20)
Execution Time: 0.11 ms

The metric that moved is rows read: 8,000,000 down to 20. The sort was never the real cost — reading everything to feed it was.

Common Pitfalls #

Creating DESC indexes reflexively. For single-column ordering they are redundant with the ascending version. Diagnostic signal: two indexes on the same column differing only in direction. Fix: keep one.

Forgetting nulls placement in the index definition. An ORDER BY col ASC NULLS FIRST is not served by a default ascending index. Diagnostic signal: a Sort node whose key includes NULLS FIRST. Fix: declare the placement in the index.

Assuming a backward scan is slower. It is not meaningfully so; both directions follow the same leaf-page chain. Diagnostic signal: a DESC index created to “avoid backward scans”. Fix: remove it.

Overlooking the interaction with column order. Direction only helps if the leading columns already match the ordering prefix — the rules in multicolumn index column ordering come first.

Adding a direction to a column that is only ever filtered. Direction affects ordering, not searchability, so declaring DESC on a column used solely in equality predicates changes nothing except the index definition’s readability. Diagnostic signal: a DESC modifier on a column that never appears in an ORDER BY. Fix: drop the modifier and keep the definition honest about intent.

Assuming the ordering survives an added filter. An index on (customer_id, created_at DESC) supplies ORDER BY created_at DESC only within a single customer_id. A query ordering by created_at across all customers gets no ordering guarantee from it and needs a sort. Diagnostic signal: a Sort node appearing when the leading column is not constrained to one value. Fix: either constrain the leading column or build a separate index on the ordering column alone.

Mixing directions across a pagination key. Keyset pagination compares a tuple of columns, and the comparison must line up with the index’s declared directions to remain a single range scan. Diagnostic signal: a keyset query degrading into a filter plus sort as the offset grows. Fix: declare the index directions to match the pagination comparison exactly, including the tiebreaker column.

One leaf chain, two reading directions A single chain of leaf pages holding ascending keys. A forward arrow beneath it yields ascending order with nulls last; a backward arrow above it yields descending order with nulls first. 1–99100–199200–299300–399NULLs forward → ASC, NULLS LAST backward → DESC, NULLS FIRST

Frequently Asked Questions #

Do I need a DESC index for ORDER BY column DESC? Normally no. B-trees are scannable in both directions, so an ascending index serves a descending order at the same cost, shown as Index Scan Backward. Direction in the definition matters only for mixed-direction sorts.

When does NULLS FIRST or NULLS LAST matter? When the requested placement does not match what the index gives in the direction being read. An ascending index places nulls last forward and first backward, so ORDER BY col ASC NULLS FIRST needs an index that declares it.

How do I know the index supplied the ordering? No Sort node appears above the scan and the node reads Index Scan or Index Scan Backward. A Sort in the presence of a seemingly suitable index means the direction or nulls placement does not match.