cancel
Showing results for 
Search instead for 
Did you mean: 

Creating Descending Indexes

PostgreSQL Core Team - EDB

4/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.