title: How to create an index for elements of an array in PostgreSQL?
body: <p>With this schema:</p>
  <pre><code>create table object (
     obj_id      serial      primary key,
     name        varchar(80) not null unique,
     description text,
     tag_arr     int[]
  );
  
  create table tag (
     tag_id      serial      primary key,
     label       varchar(20) not null unique
  );
  </code></pre>
  <p>An object may have any number of tags attached. Instead of an <code>object X tag</code> table, I wish to keep <code>tag_id</code>s in an array so they can be easily fetched with the object record.</p>
  <p>How do I create an index on <code>object</code> so that each element of <code>tar_arr</code> is an index?</p>
  <p>That said, are there better ways to solve this problem?</p>
  <h3>Discussion</h3>
  <p>This could be achieved with:</p>
  <pre><code>create table obj_x_tag(
     obj_id    references object,
     tag_id    references tag,
     constraint obj_x_tag_pk primary key( obj_id, tag_id )
  );
  
  select obj_id, name, description, array_agg( tag_id )
  from object o
  join obj_x_tag x using( obj_id )
  group by 1, 2;
  </code></pre>
  <p>But to me it makes more sense to simply keep the array of <code>tag_id</code>s in a column and dispense with the cross table and <code>array_agg()</code></p>
  <p>It was suggested to use <a href="https://stackoverflow.com/questions/5723851/postgresql-sql-converting-results-to-array">PostgresQL SQL: Converting results to array</a>. The problem, as noted, is that &quot;this doesn't actually index individual array values, but instead indexes the entire array&quot;</p>
  <p>It was also suggested to use pg's <code>intarr</code> and <code>gist</code> (or <code>gin</code>) index. The problem - to me - seems that the index is for the standard pg set-based array operators, <em>not</em> necessarily optimized for finding <em>one</em> element of an array, but rather where one array <em>contains</em> another, <em>intersects</em> with another - for me it's counter-intuitive that, size-wise and speed-wise, such a wide solution is correct for such a narrow problem. Also, the <code>intarr</code> extension seems limited to <code>int</code>, not covering <code>int64</code> or <code>char</code>, limiting its usefulness.</p>