title: Postgres multi-column index (integer, boolean, and array)
body: <p>I have a Postgres 9.4 database with a table like this:</p>
  
  <pre><code>| id | other_id | current | dn_ids                                | rank |
  |----|----------|---------|---------------------------------------|------|
  | 1  | 5        | F       | {123,234,345,456,111,222,333,444,555} | 1    |
  | 2  | 7        | F       | {123,100,200,900,800,700,600,400,323} | 2    |
  </code></pre>
  
  <p>(update) I already have a couple indexes defined. Here is the <code>CREATE TABLE</code> syntax:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE mytable (
      id integer NOT NULL,
      other_id integer,
      rank integer,
      current boolean DEFAULT false,
      dn_ids integer[] DEFAULT '{}'::integer[]
  );
  
  CREATE SEQUENCE mytable_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
  
  ALTER TABLE ONLY mytable ALTER COLUMN id SET DEFAULT nextval('mytable_id_seq'::regclass);
  ALTER TABLE ONLY mytable ADD CONSTRAINT mytable_pkey PRIMARY KEY (id);
  
  CREATE INDEX ind_dn_ids ON mytable USING gin (dn_ids);
  CREATE INDEX index_mytable_on_current ON mytable USING btree (current);
  CREATE INDEX index_mytable_on_other_id ON mytable USING btree (other_id);
  CREATE INDEX index_mytable_on_other_id_and_current ON mytable USING btree (other_id, current);
  </code></pre>
  
  <p>I need to optimize queries like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT id, dn_ids
  FROM mytable
  WHERE other_id = 5 AND current = F AND NOT (ARRAY[100,200] &amp;&amp; dn_ids)
  ORDER BY rank ASC
  LIMIT 500 OFFSET 1000
  </code></pre>
  
  <p>This query works fine, but I'm sure it could be much faster with smart indexing. There are about 250,000 rows in the table and I always have <code>current = F</code> as a predicate. The input array I'm comparing to the stored array will have 1-9 integers, as well. The <code>other_id</code> can vary. But generally, before limiting, the scan will match between 0-25,000 rows.</p>
  
  <p>Here's an example <code>EXPLAIN</code>:</p>
  
  <pre><code>Limit  (cost=36944.53..36945.78 rows=500 width=65)
    -&gt;  Sort  (cost=36942.03..37007.42 rows=26156 width=65)
          Sort Key: rank
          -&gt;  Seq Scan on mytable  (cost=0.00..35431.42 rows=26156 width=65)
                Filter: ((NOT current) AND (NOT ('{-1,35257,35314}'::integer[] &amp;&amp; dn_ids)) AND (other_id = 193))
  </code></pre>
  
  <p>Other answers on this site and the <a href="http://www.postgresql.org/docs/current/interactive/indexes-multicolumn.html" rel="nofollow">Postgres docs</a> suggest it's possible to add a compound index to improve performance. I already have one on <code>[other_id, current]</code>. I've also read in various places that indexing can improve the performance of the <code>ORDER BY</code> in addition to the <code>WHERE</code> clause.</p>
  
  <ol>
  <li><p>What's the right type of compound index to use for this query? I don't care about space at all.</p></li>
  <li><p>Does it matter much how I order the terms in the <code>WHERE</code> clause?</p></li>
  </ol>