title: Postgresql: multicolumn indexes vs single column index
body: <p>I have a table that will grow by 10M of rows every year.</p>
  
  <p>The table have 10 columns, call them c1, c2, c3, ..., c10.</p>
  
  <p>I will use the WHERE clause, potentially on 8 of them.</p>
  
  <p>To be more specific: every time I will query the table, there will ALWAYS be a WHERE clause on column c10 (it's a date, I could search for equality or range). </p>
  
  <p>The other 7 possible searchable columns, will not follow any schema.
  I could search for:</p>
  
  <ul>
  <li>c10, c1, c2, c5</li>
  <li>c10, c5</li>
  <li>c10, c3</li>
  <li>c10, c2, c6</li>
  <li>c10, c2, c3, c5, c6</li>
  </ul>
  
  <p>... and all other possible combinations.</p>
  
  <p>So, in a WHERE clause, c10 will always be present and the others could be present in any combination (even not present at all).</p>
  
  <p><strong>What indexing strategy could improve performance in this scenario?
  I think that the right thing to do, is to create a single index for every column. There could be any performance improvements using a multicolumn index?</strong> </p>
  
  <p>From what I know, you will gain performance with a multicolumn index on (c1, c2, c3), only for queries that use c1, c2, c3 or c1, c2, or c1, in that order. But like I said, the only thing I can assume in my scenario, is that c10 will always be present in a WHERE clause (it can also be the first clause, if that helps)</p>