title: Postgresql - performance of using array in big database
body: <p>Let say we have a table with 6 million records. There are 16 integer columns and few text column. It is read-only table so every integer column have an index.
  Every record is around 50-60 bytes.</p>
  
  <p>The table name is "Item"<br/>
  The server is: 12 GB RAM, 1,5 TB SATA, 4 CORES. All server for postgres.<br/>
  There are many more tables in this database so RAM do not cover all database.<br/></p>
  
  <p>I want to add to table "Item" a column "a_elements" (array type of big integers)
  Every record would have not more than 50-60 elements in this column.</p>
  
  <p>After that i would create index GIN on this column and typical query should look like this:</p>
  
  <pre><code>select * from item where ...... and '{5}' &lt;@ a_elements;
  </code></pre>
  
  <p>I have also second, more classical, option.</p>
  
  <p>Do not add column a_elements to table item but create table elements with two columns:</p>
  
  <ul>
  <li>id_item</li>
  <li>id_element</li>
  </ul>
  
  <p>This table would have around 200 mln records.</p>
  
  <p>I am able to do partitioning on this tables so number of records would reduce to 20 mln in table elements and 500 K in table item.</p>
  
  <p>The second option query looks like this:</p>
  
  <pre><code>select item.* 
  from item 
      left join elements on (item.id_item=elements.id_item) 
  where .... 
  and 5 = elements.id_element
  </code></pre>
  
  <p>I wonder what option would be better at performance point of view.
  Is postgres able to use many different indexes with index GIN (option 1) in a single query ?</p>
  
  <p>I need to make a good decision because import of this data will take me a 20 days.</p>