Looking for a possible multi-column index 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
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.