Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

2 Answers 2

up vote -1 down vote accepted

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 wound 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.)
share|improve this answer
3  
Postgres can use an ascending index to speed up an ORDER BY DESC. See postgresql.org/docs/current/static/indexes-ordering.html "The index can also be scanned backward, producing output satisfying ORDER BY x DESC" –  a_horse_with_no_name Jun 23 '12 at 20:56
    
Thank you all. To further clarify: I am on Heroku legacy shared database plan which uses Postgres 8.3, but am planning on upgrading to Heroku's latest offering to use 9.1.3 to take advantage of its features. –  Bharat Jun 24 '12 at 21:08

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:

share|improve this answer
    
Why don't you link to the current version of the manual? 8.3 is rather outdated. –  a_horse_with_no_name Jun 23 '12 at 20:57
    
Only becasue the OP did not provide the version he's using. To show that this is also true for older versions. –  ypercube Jun 23 '12 at 20:58
    
I see. Fair enough –  a_horse_with_no_name Jun 23 '12 at 20:59
    
While btree indexes in PostgreSQL can be searched in either direction, it is slightly slower to scan backward on the index because of details of how locking is done. It probably won't be that noticeable, and it is very unlikely to be worth the cost of maintaining a second index. –  kgrittn Jun 24 '12 at 15:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.