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

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
  • 185,527
  • 28
  • 463
  • 633
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