title: PostgreSQL index on multiple columns, when is it too much?
body: <p>Using PostgreSQL 9.6</p>
  
  <p>I have a table with some values I want to filter on and order by time:</p>
  
  <ul>
  <li>a timestamp (may be range selected in the UI)</li>
  <li>status string (only a few known values for now, also selectable in the UI)</li>
  <li>context (scope of the data in the UI)</li>
  </ul>
  
  <p>I wonder if I should have:</p>
  
  <ol>
  <li>A btree index on (context, status) + separate index on time</li>
  <li>OR A btree index on (context, status, time)</li>
  <li>OR A btree index on each ?</li>
  <li>OR A btree index on (time, status, context), for small time ranges ?</li>
  </ol>
  
  <p>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 ?</p>
  
  <p>One of the queries looks more or less like:</p>
  
  <pre><code>select * from event
  where severity = 'WARNING' and 
  fk_context = 1359544
  order by timestamp LIMIT 30; // Other one has timestamp &gt; ...
  </code></pre>
  
  <p>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.</p>