Postgres primary key sorted in the reverse order. Will it use the index efficiently?

Question

My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.

My list views show records in the reverse chronological order. So I just sort my result set in the reverse order of the primary key.

My question is: will the query use the primary key index? And if yes then as efficiently? How do I verify that?

Thanks in advance for your time.

Bharat

Answer 1

Indexes are typically unidirectional. This allows improvements in space and index scanning speed as back links don't need to be maintained. As @a_horse_with_no_name has noted, Postgres can and will be used to search in the reverse order to the index. (If this was not a unique index with no NULLs, positioning of NULLS FIRST/LAST could be a consideration.) Performance may not be as fast as using a non-standard index ordering, and the documentation notes significant speedups can be obtained with non-standard orderings.

For systems that don't support scanning indexes backwards, if your index is ascending, it would not be used for your query. There are two options in this case:

• add another index with DESC specified; or
• replace the primary key index with one which is has DESC specified. (I haven't tried this, and likely wouldn't as it breaks my expectations for primary keys.)

Answer 2

From Postgres documentation: Chapter 11. Indexes (note that the same holds even for really old versions like 8.3 chapter 11. Indexes):

By default, B-tree indexes store their entries in ascending order with nulls last. This means that a forward scan of an index on a column x produces output satisfying ORDER BY x (or more verbosely, ORDER BY x ASC NULLS LAST). The index can also be scanned backward, producing output satisfying ORDER BY x DESC (or more verbosely, ORDER BY x DESC NULLS FIRST, since NULLS FIRST is the default for ORDER BY DESC).

---

Now, whether this index will be used for a particular statement, it really depends on the statement. For a query that returns the whole table or a large part of it:

SELECT *
FROM tablex
ORDER BY tablexID DESC ;

it will have to scan the whole table anyway, so the optimizer may decide that it's cheaper to just read the whole table and then sort it in descending order (and not use this index).

If it's a query like this though, I would bet that it would use only the index (because it doesn't need any other data but the IDs that are stored in the index):

SELECT tablexID
FROM tablex
WHERE tablexID <= 5000
ORDER BY tablexID DESC ;

So, whether an index will be used or not, really depends on the statement that you are executing, the cache settings, the types of joins, the conditions you have, all the indices that are available/relative, the statistics of the tables and indices (sizes, distribution, cardinality, etc), the version of Postgres (different version means different optimizations), the phase of the moon and possibly a lot of other factors I'm forgetting.

---

To check which indices, if any, will be used for particular statement at a particular time, you can see its execution plan using EXPLAIN: