title: Postgres: How to use arrays as stored procedure parameters efficiently?
body: <p>I need to create a Postgres 9.1 PL/pgSQL stored procedure that, among other parameters, takes a sequence of values that directly reference values in one of my database columns. As far as I can tell, the canonical way to do this in Postgres is an <a href="http://www.postgresql.org/docs/current/interactive/arrays.html" rel="nofollow">array</a>.</p>
  
  <p>This is a rather basic task, of course. My problem is scalability: My code basically works, but performs badly once the sequences passed in get large (as in a few hundreds or thousands of values):</p>
  
  <p>Even rather simple SELECT statements within my stored procedure using the array in the form</p>
  
  <pre><code>SELECT &lt;some columns&gt;
  FROM   &lt;some tables&gt;
  WHERE  &lt;some other select criteria&gt;
  AND    &lt;column with values selected by array parameter&gt;
           IN (SELECT * FROM unnest(&lt;array parameter&gt;))
  </code></pre>
  
  <p>take several seconds to execute even though the database is not very large yet and there are only tens of values in the array.</p>
  
  <p>My first suspicion was that <code>unnest(...)</code> is the problem, but selecting only from the table with the column referenced in the array parameter is really fast:</p>
  
  <pre><code>SELECT &lt;some columns&gt;
  FROM   &lt;table with column ref'd in array parameter&gt;
  WHERE  &lt;column with values selected by array parameter&gt;
           IN (SELECT * FROM unnest(&lt;array parameter&gt;))
  </code></pre>
  
  <p>only takes a few milliseconds.</p>
  
  <p><strong>My questions</strong>:</p>
  
  <ol>
  <li>Is there an alternative to using an array as parameter ?</li>
  <li>How can I make my queries perform better ?</li>
  </ol>