Creating Descending Indexes

April 18, 2017

You might have noticed that create index allows you to specify asc or desc for each column being indexed, and you might have wondered why. Asc is the default, so there is no need to use it. Desc actually does have a use, but it isn't obvious.

Postgres can access indexes forward or backward, so there is no need to specify ascending/descending if all columns are ascending or descending. The use-case for specifying the index order is for multi-column indexes with mixed ordering, assuming queries also use the same mixed ordering. For example, let's create a two-column table with 1000 rows, and an index in all-ascending order:

CREATE TABLE test(x INTEGER, y INTEGER);
INSERT INTO test
        SELECT x, x FROM generate_series(1, 1000) AS f(x);

CREATE INDEX i_test ON test (x, y);

Postgres has no problem using the index if the query is all ascending or all descending, but it can't be used with mixed-column ordered queries:

EXPLAIN (COSTS off) SELECT * FROM test ORDER BY x, y;
              QUERY PLAN
--------------------------------------
 Index Only Scan using i_test on test

 

EXPLAIN (COSTS off) SELECT * FROM test ORDER BY x DESC, y DESC;
                  QUERY PLAN
-----------------------------------------------
 Index Only Scan Backward using i_test on test

 

EXPLAIN (COSTS off) SELECT * FROM test ORDER BY x, y DESC;
       QUERY PLAN
------------------------

 Sort
   Sort Key: x, y DESC
   ->  Seq Scan on test

Creating an index that matches the mixed ordering of the query allows the index to be used:

CREATE INDEX i_test2 ON test (x, y DESC);
 

EXPLAIN (COSTS off) SELECT * FROM test ORDER BY x, y DESC;
              QUERY PLAN
---------------------------------------
 Index Only Scan using i_test2 on test

The mixed-ordered index can even be used for backward scans:

EXPLAIN (COSTS off) SELECT * FROM test ORDER BY x DESC, y;
                   QUERY PLAN
------------------------------------------------
 Index Only Scan Backward using i_test2 on test

I am not sure how many people use mixed-ordered queries that would benefit from indexes, but I wanted to show how this feature can be useful.

Bruce Momjian is Senior Database Architect at EnterpriseDB. 

This post originally appeared on Bruce's personal blog.

Share this

Relevant Blogs

Finding memory leaks in Postgres C code

I spent the last week looking for a memory leak in Postgres’s WAL Sender process. I spent a few days getting more acquainted with Valgrind and gcc/clang sanitizers, but ultimately...
March 27, 2024

More Blogs

Let's Workshop an Unplanned Postgres Outage

It’s not a controversial statement to say that no database maintenance is without risk. Postgres and its community provides several useful tools to minimize impact of even major overhauls such...
July 07, 2023