Descending index benefits | CYBERTEC PostgreSQL | Services & Support

src: Descending index benefits - 2025-10-21

PostgreSQL can scan B-tree indexes in both directions. That means that there is little need to create a index in descending order (one created with the DESC clause). However, there are some cases where you need a descending index. There are also some corner cases where a descending index performs better than an ascending one. Follow me to explore use cases for the DESC clause!

Mixed ORDER BY clauses

This is the obvious use case for a descending index. There is a reason why CREATE INDEX offers the same clauses as ORDER BY: if you want a B-tree index to support an ORDER BY clause, its sorting order must match the ORDER BY clause precisely. So, to support a query like:

SELECT col2 FROM tab
ORDER BY col1 DESC NULLS LAST;

you need to create an index like

CREATE INDEX tab_col1_desc_idx
ON tab (col1 DESC NULLS LAST);

But since PostgreSQL can scan indexes in both directions, the following index will also work:

CREATE INDEX tab_col1_idx
ON tab (col1 NULLS FIRST);

However, you cannot avoid the DESC clause if you want to support an ORDER BY clause with both ascending and descending columns:

SELECT col2 FROM tab
ORDER BY col1 DESC, col2 NULLS LAST;

In a previous article, I wrote some more about mixed ORDER BY clauses in the context of keyset pagination. There, you can find some more creative ideas for using descending indexes.

Descending indexes for space efficiency

This is a somewhat more exotic use case. I’ll demonstrate it with the following table:

CREATE UNLOGGED TABLE large(
   id bigint NOT NULL
);
 
CREATE INDEX large_id_idx ON large(id);
CREATE INDEX large_id_desc_idx ON large (id DESC);

One of these two indexes is clearly redundant. But look what happens if I INSERT rows in descending order:

INSERT INTO large (id)
SELECT * FROM generate_series(10000000, 1, -1);
 
SELECT ind AS index_name,
       pg_size_pretty(pg_relation_size(ind)) AS index_size
FROM (VALUES ('large_id_idx'::regclass),
             ('large_id_desc_idx'::regclass)) AS i(ind);
 
    index_name     | index_size 
-------------------+------------
 large_id_idx      | 385 MB
 large_id_desc_idx | 214 MB

Even though both indexes contain the same data (just in a different ordering), the size is vastly different! We can confirm that by using the tools I demonstrated in my article about rebuilding indexes:

CREATE EXTENSION IF NOT EXISTS pgstattuple;
 
SELECT i.ind index_name, s.avg_leaf_density
FROM (VALUES ('large_id_idx'::regclass), 
             ('large_id_desc_idx'::regclass)) AS i(ind)
   CROSS JOIN LATERAL pgstatindex(i.ind) AS s;
 
    index_name     | avg_leaf_density 
-------------------+------------------
 large_id_idx      |            50.29
 large_id_desc_idx |            90.04

The descending index is densely packed (B-tree indexes have a default fillfactor of 90 for leaf pages), but there is empty space in the ascending index.

The explanation is that B-tree indexes are specially optimized for insertion at the upper end. Normally, when PostgreSQL needs to split an index page, it distributes the existing entries so that both the old and the new page contain half of the entries and are half empty. But when it splits the last index page on a level, it puts most of the entries into the lower page, as this source comment in src/backend/access/nbtree/nbtsplitloc.c explains:

/*
 *  _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.

This is an optimization for the typical cases of sequence-generated keys or increasing timestamps. With our descending index, the “rightmost” page contains the lowest numbers, so the optimization works well when we insert ever-descending numbers. PostgreSQL splits the pages of the ascending index in the middle, leaving half of the space in the index pages empty.

Descending indexes for better performance with range scans

There is yet another case in which descending indexes will offer a performance benefit. To demonstrate, I drop the ascending index from the previous example and make sure that the optimizer statistics, the visibility map and the hint bits are in good shape:

DROP INDEX large_id_idx;
 
VACUUM (ANALYZE) large;

Next, I want to make sure that I can measure actual disk I/O rather than working on data cached in memory. For that, I restart my PostgreSQL v18 server and tell Linux to empty the kernel page cache (as operating system user root):

systemctl restart postgresql-18
sync; echo 3 > /proc/sys/vm/drop_caches

Then, I measure the speed of an index-only scan, both in forward and in backward direction. Between the runs, I restart PostgreSQL and drop the kernel page cache to ensure a fair comparison.

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT id FROM large ORDER BY id;
 
                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Index Only Scan Backward using large_id_desc_idx on large (actual rows=10000000.00 loops=1)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared read=27327 written=1
   I/O Timings: shared read=3560.353 write=0.024
 Planning:
   Buffers: shared hit=47 read=21 dirtied=1
   I/O Timings: shared read=3.296
 Planning Time: 6.213 ms
 Execution Time: 5508.029 ms
 
 
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT id FROM large ORDER BY id DESC;
 
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Only Scan using large_id_desc_idx on large (actual rows=10000000.00 loops=1)
   Heap Fetches: 0
   Index Searches: 1
   Buffers: shared read=27327
   I/O Timings: shared read=87.431
 Planning:
   Buffers: shared hit=46 read=21
   I/O Timings: shared read=2.824
 Planning Time: 4.392 ms
 Execution Time: 680.263 ms

Scanning the index backward on my NVMe disk takes almost ten times as long as scanning it in forward direction! You might suspect that this is somehow connected to the asynchronous I/O implementation new in PostgreSQL v18, but that is not the case: PostgreSQL doesn’t use asynchronous I/O for index scans (yet). What we see here is the effect of Linux’s “readahead”: when the kernel detects that you are reading a file sequentially, it will automatically issue read requests for future pages to reduce the time the reading process has to wait for I/O operations.

Usually, the effect is not as pronounced, because the pages of frequently used indexes tend to be in cache. Still, creating a descending index can give you an edge if you have to perform large index scans in descending order.

Conclusion

The well-known use case for the DESC clause in an index definition is support for a mixed ORDER BY clause. But there are corner cases in which PostgreSQL can populate a descending index more space-efficiently, and a forward scan on a descending index can perform better than a backward scan on an ascending index.