Creating multicolumn indexes in PostgreSQL

Question

I'm researching now on creating indexes for our tables.

I found out about multicolumn indexes but I'm not sure about the impact. Example:

We have SQLs on findById, findByIdAndStatus, findByResult.

It says that the most used on WHERE should be listed first in the columns list. But I was wondering if it'll have a huge impact if I create an index on different combinations where clauses.

This: (creating one index for all)

CREATE INDEX CONCURRENTLY ON Students (id, status, result)

vs.

This: (creating different indexes on different queries)

CREATE INDEX CONCURRENTLY ON Students (id)

CREATE INDEX CONCURRENTLY ON Students (status)

CREATE INDEX CONCURRENTLY ON Students (result)

Thank you so much in advance!

Answer 1

Creating one index for all and creating different indexes will have completely different impact on the queries.

1. You can use EXPLAIN to see if indexes are getting used for the queries.

2. This video is really good to know about DB indexes.

3. Index CREATE INDEX CONCURRENTLY ON Students (id, status, result) will be used only and only if query uses id, (id,status) or (id, status and result) in WHERE clause. a query with status in Where will not use this index at all.

Indexes are basically balanced binary trees. A multicolumn index will index rows by id, then rows ordered by id's are further indexes by status and then with result and so on. You can see that in this index, the ordering via status is not present at all. It is only available on rows indexed by the id's first.

Do have the look at video, it explains all this pretty well.