Re: Index on a NULL-value

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruno Wolff III <bruno(at)wolff(dot)to>
Cc: Tobias Brox <tobias(at)nordicbet(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Index on a NULL-value
Date: 2005-05-31 04:18:33
Message-ID: 7923.1117513113@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Greg Stark 2005-05-31 04:21:25 Re: Index on a NULL-value
Previous Message Bruno Wolff III 2005-05-31 04:08:01 Re: Index on a NULL-value