Postgresql – Looking for a possible multi-column index

indexperformancepostgresqlquery-performance

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

Best Answer

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.

Related Solutions

Postgresql – Index for column with two possible value

The optimizer evaluates the possible gains of using indexes vs doing a full scan (filtering unwanted rows on the fly). As the ratio of filtered and total rows gets closer to 1 the benefit of using index decreases. The exact tipping point is dependent on the actual data, query, etc. so it's hard to say an exact number when it becomes useless. Generally speaking a 50/50 split is not sufficient for a B+TREE index to work efficiently and most cases full scan will be preferred over using indexes. (Unless it can be used for index-only scans)

The index is still getting updated regardless of its usefulness in queries.

MySQL – Index Merge vs Multi-Column Index for Performance Optimization

Using intersect is almost always an indication that you should have a "composite" index on the columns mentioned.

In your case, the index can be either of these:

INDEX(user_id, ref_url)
INDEX(ref_url, user_id)

The order of clauses in the WHERE does not matter -- the Optimizer is free to reorder things. It does not matter to the optimization.

On the other hand, the order of columns in a composite index does matter. (But it does not happen to matter in this case.)

Further explanation: For the "intersect" technique, this happens:

Find all entires in INDEX(user_id) for 32366; collect the PRIMARY KEYs for those rows.
Find all entries in INDEX(ref_urs) for 'http...'; collect the PRIMARY KEYs for those rows.
Using those two lists, "intersect" them. That is, find out what entries are common to both.
Now, reach for the desired rows by using the PRIMARY KEYs.

With a composite index:

Drill down the BTree directly to the desired row(s).

Best Answer

Related Solutions

Postgresql – Index for column with two possible value

MySQL – Index Merge vs Multi-Column Index for Performance Optimization

Related Question