Indexing Null Values in PostgreSQL

Question

I have a query of the form:

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 

The query returns a few hundred records, although the tables have millions of rows, and it takes forever to run (around an hour).

When I check my index statistics using:

select * from pg_stat_all_indexes where schemaname <> 'pg_catalog' and (indexrelname like 'othertable_%' or indexrelname like 'mytable_%') 

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?

I've read in a few places 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?

Answer 1

You could try a partial index:

CREATE INDEX idx_partial ON othertable (m_id) WHERE (col1 is not null and col2 is not null and col3 is not null); 

From the docs: http://www.postgresql.org/docs/current/interactive/indexes-partial.html