Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: Greg StarkDate: 2005-05-31 04:21:25
Subject: Re: Index on a NULL-value
Previous:From: Bruno Wolff IIIDate: 2005-05-31 04:08:01
Subject: Re: Index on a NULL-value

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group