PostgreSQL index on multiple columns, when is it too much?

Question

Using PostgreSQL 9.6

I have a table with some values I want to filter on and order by time:

• a timestamp (may be range selected in the UI)
• status string (only a few known values for now, also selectable in the UI)
• context (scope of the data in the UI)

I wonder if I should have:

1. A btree index on (context, status) + separate index on time
2. OR A btree index on (context, status, time)
3. OR A btree index on each ?
4. OR A btree index on (time, status, context), for small time ranges ?

I suspect number 1 is the best option, context + status will allow to filter out values and then it will scan the time index. I created number 1 concurrently on my data and saw some improvements but how do you decide between each approach, are there some guidelines ?

One of the queries looks more or less like:

select * from event
where severity = 'WARNING' and 
fk_context = 1359544
order by timestamp LIMIT 30; // Other one has timestamp > ...

Another one is looking for a timerange. I looks like postgres uses multiple indexes, one with (fk_context, severity, timestamp) and then uses (severity, time) index, but it also depends on the limit.

Answer 1

Your question is unclear. If you have three potential conditions:

where timestamp between @a and @b order by time
where status = @s order by time
where context = @c order by time

Then you want three indexes: (timestamp, time), (status, time), and (context, time).

If the conditions are:

where timestamp between @a and @b and
      status = @s and
      context = @c
order by time 

Then you want one index, (status, context, timestamp, time).

And there are other possibilities consistent with your description.