3

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

Community
  • 1
Bart van Heukelom
  • 131
  • 3
  • 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. –  Nov 10 '12 at 17:41

2 Answers2

1

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.

Community
  • 1
Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
0

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.

Bart van Heukelom
  • 131
  • 3
  • 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