The EDB Blog
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.

bruce.momjian's picture

Bruce Momjian is a co-founder of the PostgreSQL Global Development Group, and has worked on PostgreSQL since 1996 as a committer and community leader. He is a frequent speaker and Postgres evangelist and travels worldwide appearing at conferences to help educate the community on the business...