Re: index question

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:

> Rick Gigger wrote:
> > I have heard that postgres will not use an index unless the
> > field has a not null constraint on it. Is that true?
>
> To be specific, we do not do index NULL values in a column, but we
> easily index non-null values in the column.

I don't think that's true. Postgres does index null values by default. Perhaps
you're thinking of Oracle which doesn't. You can get Oracle's behaviour in
Postgres by using a partial index "WHERE col IS NOT NULL".

The following would not be able to use the index scan plan that it does if
NULL values weren't indexed:

db=> create table test (i integer);
CREATE TABLE
db=> create index i on test(i);
CREATE INDEX
db=> set enable_seqscan = off;
SET
db=> explain select * from test order by i;
QUERY PLAN
------------------------------------------------------------------
Index Scan using i on test (cost=0.00..24.00 rows=1000 width=4)
(1 row)

Perhaps the poster is thinking of the fact that postgres doesn't consider "IS
NULL" and "IS NOT NULL" to be indexable operations. So for example things like
this cannot use an index:

db=> explain select * from test where i is not null;
QUERY PLAN
----------------------------------------------------------------------
Seq Scan on test (cost=100000000.00..100000020.00 rows=995 width=4)
Filter: (i IS NOT NULL)
(2 rows)

db=> explain select * from test where i is null;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on test (cost=100000000.00..100000020.00 rows=6 width=4)
Filter: (i IS NULL)
(2 rows)

That's a bit of a deficiency but that too can be addressed by using a partial
index:

db=> create index ii on test(i) where i is not null;
CREATE INDEX
db=> explain select * from test where i is not null;
QUERY PLAN
------------------------------------------------------------------
Index Scan using ii on test (cost=0.00..23.95 rows=995 width=4)
Filter: (i IS NOT NULL)
(2 rows)

Though the added cost of maintaining another index is not really a good
tradeoff. This is only really a good idea if the partial index covers a small
subset of the total number of records, or if it is indexing a column not
already indexed.

You might also reconsider whether using NULL in the data model is right,
usually it's worth avoiding except in the case of truly "unknown" values.

--
greg