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.

I have a table highscore containing the columns:

  • game (text)
  • date (timestamp)
  • score (integer)
  • more irrelevant ones...

The query most often run on it is:

SELECT *
FROM highscore
WHERE game = :gamename
AND date BETWEEN :start AND :end
ORDER BY score DESC
LIMIT 10

I currently have three B-tree indexes on this table, one for each column above.

I'm thinking that I could further optimize performance by adding some sort of multi-column index, which starts with game. However, when thinking through the various options for the next column(s), I get stuck.

Can I use a multi-column index for further optimization here?

I'm using PostgreSQL 9.1

share|improve this question
3  
I'd try an index on (game, date, score). Although the score column probably doesn't help very much to speed up the ordering though (if it's used at all). Probably an index on (game, date) will be just as good. Btw: it's a bad habit to use column names that are also reserved words like date - and it actually doesn't tell anyone what the column contains. Something like happened_on or scheduled_for is much more descriptive. –  a_horse_with_no_name Nov 10 '12 at 17:41
add comment

2 Answers

No wonder you get stuck. You are facing contradictory requirements, which a b-tree index cannot easily reconcile. You can hardly optimize the search for date and score at the same time. If you order by date, the scores are spread out arbitrarily - or vice versa.

There is a way though. You can make use of a set of partial indexes.
Consider the high-end solutions under this closely related question.

share|improve this answer
add comment

After thinking about it some more, I found these two options:

  • (game, date) for when I'm selecting the scores of a certain time period (usually a week). The conditions on game and date should select a relatively small set of records, which can then easily be sorted on score.
  • (game, score) for selecting all-time highscores. Here the index should work 100%.

Shouldn't be a problem to just add both.

share|improve this answer
    
This doesn't really look like an answer, given the question at the end. You can edit your question (there's a link right under the tags for that) to put additional information. –  Mat Nov 10 '12 at 18:02
add comment

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.