On Mon, 2005-10-31 at 13:13, Merlin Moncure wrote:
> > > if that index is causing the problem, you may want to consider
> > > up partial index to exclude null values.
> > This is a single column index. I assumed that null column values were
> > not indexed. Is my assumption incorrect?
> > -K
> It turns out it is, or it certainly seems to be. I didn't know that :).
> So partial index will probably not help for null exclusion...
> would be interesting to see if you are getting swaps (check pg_tmp) when
> performance breaks down. That is an easy fix, bump work_mem.
OK, here's the issue in a nutshell.
NULLS, like everything else, are indexed. HOWEVER, there's no way for
them to be used by a normal query, since =NULL is not a legal
construct. So, you can't do something like:
select * from sometable where somefield = NULL
because you won't get any answers, since nothing can equal NULL and
select * from sometable where somefield IS NULL won't work because IS is
not a nomally indexible operator.
Which is why you can create two indexes on a table to get around this
create index iname1 on table (field) where field IS NULL
create index iname2 on table (field) where field IS NOT NULL
And then the nulls are indexable by IS / IS NOT NULL.
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings