title: Indexing Null Values in PostgreSQL
body: <p>I have a query of the form:</p>
  
  <pre><code>select m.id from mytable m
  left outer join othertable o on o.m_id = m.id
      and o.col1 is not null and o.col2 is not null and o.col3 is not null
  where o.id is null
  </code></pre>
  
  <p>The query returns a few hundred records, although the tables have millions of rows, and it takes forever to run (around an hour).</p>
  
  <p>When I check my index statistics using:</p>
  
  <pre><code>select * from pg_stat_all_indexes
  where schemaname &lt;&gt; 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%')
  </code></pre>
  
  <p>I see that only the index for othertable.m_id is being used, and that the indexes for col1..3 are not being used at all. Why is this?</p>
  
  <p>I've read in a <a href="http://archives.postgresql.org/pgsql-performance/2005-05/msg00455.php" rel="noreferrer">few</a> <a href="http://postgresql.1045698.n5.nabble.com/Using-index-for-IS-NULL-query-td2070691.html" rel="noreferrer">places</a> that PG has traditionally not been able to index NULL values. However, I've read this has supposedly changed since PG 8.3? I'm currently using PostgreSQL 8.4 on Ubuntu 10.04. Do I need to make a "partial" or "functional" index specifically to speed up IS NOT NULL queries, or is it already indexing NULLs and I'm just misunderstanding the problem?</p>