title: Index a postgresql array column for greater than / less than comparisons?
body: <h2>Can I index array columns in Postgresq 9.1 to support:</h2>
  
  <p><code>
  select * from t where  100 &lt; any(a) and 100 &gt; any(a)
  </code></p>
  
  <p>Where <code>a</code> is an integer array (with rows that have some values like <code>{90,110}</code>, so the query isn't returning empty.)</p>
  
  <hr>
  
  <h3>Details</h3>
  
  <pre><code>create table t ( a integer[] )
  insert into t values ('{90,110}'::integer[])
  </code></pre>
  
  <p>Running <code>explain analyze</code> on the query above yields:</p>
  
  <pre><code>Seq Scan on t  (cost=0.00..1.07 rows=2 width=32) (actual time=0.009..0.009 rows=1 loops=1)
    Filter: ((100 &lt; ANY (a)) AND (100 &gt; ANY (a)))
  Total runtime: 0.023 ms
  </code></pre>
  
  <hr>
  
  <h3>Background</h3>
  
  <p>The following question describes an approach but doesn't seem to work for non-fixed-length arrays:</p>
  
  <p><a href="https://stackoverflow.com/questions/4058731/can-postgresql-index-array-columns">Can PostgreSQL index array columns?</a></p>
  
  <p>The <a href="http://www.postgresql.org/docs/9.2/static/indexes-types.html" rel="nofollow noreferrer">Postgres docs</a> describe the built-in GIN operators, but they don't appear to support greater than/less than operations:</p>
  
  <blockquote>
    <p>As an example, the standard distribution of PostgreSQL includes GIN
    operator classes for one-dimensional arrays, which support indexed
    queries using these operators: &lt;@, @>, =, &amp;&amp;</p>
  </blockquote>