Postgres primary key sorted in the reverse order. Will it use the index efficiently? 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
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 &lt;= 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: