B-tree indexes in PostgreSQL are scannable in either direction, so a DESC index is rarely required. Three situations justify one anyway: mixed ORDER BY clauses, space-efficient index population, and forward range scans that avoid the cost of backward traversal.
Matching a mixed ORDER BY
CREATE INDEX accepts the same ordering clauses as ORDER BY because the index order must match the query order exactly. A query such as:
|
1 2 |
SELECT col2 FROM tab ORDER BY col1 DESC NULLS LAST; |
is served by an index like:
|
1 2 |
CREATE INDEX tab_col1_desc_idx ON tab (col1 DESC NULLS LAST); |
Because index scans run in both directions, the reverse definition works too:
|
1 2 |
CREATE INDEX tab_col1_idx ON tab (col1 NULLS FIRST); |
As soon as the requested order mixes directions, the DESC clause becomes unavoidable:
|
1 2 |
SELECT col2 FROM tab ORDER BY col1 DESC, col2 NULLS LAST; |
Keyset pagination is one context where such mixed orderings show up, and where a matching index can be put to more creative use.
When page splits waste space
Two indexes over the same column, one ascending and one descending, contain identical data — yet their sizes can differ dramatically once rows are inserted in descending order. An index-only inspection makes the divergence visible.
The reason lies in how B-tree page splits are handled. With the default fillfactor of 90, leaf pages leave room to spare, but the split logic matters more. When PostgreSQL splits any page other than the rightmost one on a level, it divides the entries so that both halves are about half full. When it splits the final page of a level, it keeps most entries in the lower page — an optimization for the common patterns of sequence-generated keys and rising timestamps, as documented in src/backend/access/nbtree/nbtsplitloc.c:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
/* * _bt_findsplitloc() -- find an appropriate place to split a page. * * The main goal here is to equalize the free space that will be on each * split page, *after accounting for the inserted tuple*. (If we fail to * account for it, we might find ourselves with too little room on the page * that it needs to go into!) * * If the page is the rightmost page on its level, we instead try to arrange * to leave the left split page fillfactor% full. In this way, when we are * inserting successively increasing keys (consider sequences, timestamps, * etc) we will end up with a tree whose pages are about fillfactor% full, * instead of the 50% full result that we'd get without this special case. * This is the same as nbtsort.c produces for a newly-created tree. Note * that leaf and nonleaf pages use different fillfactors. Note also that * there are a number of further special cases where fillfactor is not * applied in the standard way. |
The descending index stores its lowest values on the rightmost page, so ever-decreasing inserts hit that same optimization, and the index stays densely packed. In the ascending index, those inserts instead split middle pages in half, leaving roughly half of each page empty.
Readahead and backward scans
To isolate disk I/O, the ascending index is dropped and the optimizer statistics, visibility map and hint bits are brought up to date before measurement. Clearing the kernel page cache as root and restarting a PostgreSQL v18 server between runs ensures neither scan benefits from cached data.
An index-only scan measured in both directions shows a backward scan on the NVMe disk taking almost ten times as long as the forward scan. PostgreSQL v18's asynchronous I/O is not the cause — index scans do not use it yet. The gap comes from Linux readahead: on a sequential read the kernel issues requests for upcoming pages in advance, cutting the wait for I/O. In production the effect is usually milder, since hot index pages tend to sit in cache. For large index scans that must run in descending order, though, a descending index can still be the faster path.
Takeaway
A mixed ORDER BY clause remains the standard reason to add DESC to an index. Beyond that, descending indexes can be populated more densely when values arrive in descending order, and a forward scan over a descending index avoids the penalty a backward scan pays on sequential readahead.
