Re: Index on a NULL-value

Bruno Wolff III <bruno(at)wolff(dot)to> writes:
> Looked back at your first example and saw that you didn't use a partial
> index which is why you had to contort things to make it possible to
> use an indexed search.

FWIW, there is code in CVS tip that recognizes the connection between
an index on a boolean expression and a WHERE clause testing that
expression. It's not quite perfect --- using Tobias' example I see

regression=# explain select * from mock where b is NULL;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using b_is_null on mock (cost=0.00..51.67 rows=10 width=8)
Index Cond: ((b IS NULL) = true)
Filter: (b IS NULL)
(3 rows)

so there's a useless filter condition still being generated. But it
gets the job done as far as using the index, anyway.

> You want something like this:
> CREATE INDEX b_is_null ON mock(b) WHERE b IS NULL;

I think best practice for something like this is to make the partial
index's columns be something different from what the partial condition
tests. Done as above, every actual index entry will be a null, so the
entry contents are just dead weight. Instead do, say,

CREATE INDEX b_is_null ON mock(a) WHERE b IS NULL;

where a is chosen as a column that you frequently also test in
conjunction with "b IS NULL". That is, the above index can efficiently
handle queries like

... WHERE a = 42 AND b IS NULL ...

regards, tom lane