Re: Null values in indexes

Not really a followup,but this has been on my mind for some time :


How hard would it be to _not_ include nulls in indexes 
as they are not used anyway. 

(IIRC postgres initially did not include nulls, but itwas added for multi-key btree indexes)

This would be a rough approximation of partial indexes 
if used together with functions, i.e. the optimiser 
would immediately realize that

WHERE my_ifunc(partfield) = 'header'

can use index on my_ifunc(partfield)

but my_ifunc() has an easy way of skipping indexing 
overhaed for non-interesting fields by returning NULL for them.

The following seems to prove thet there is currently 
no use of putting NULLS in a single-field index:

--------------------------

hannu=# create table itest (i int, n int);
CREATE
hannu=# create index itest_n_idx on itest(n);
CREATE

then I inserted 16k tuples

hannu=# insert into itest(i) select i+2 from itest;
INSERT 0 2
hannu=# insert into itest(i) select i+4 from itest;
INSERT 0 4
hannu=# insert into itest(i) select i+8 from itest;
INSERT 0 1024
...
hannu=# insert into itest(i) select i+2048 from itest;
INSERT 0 2048
hannu=# insert into itest(i) select i+4096 from itest;
INSERT 0 4096
hannu=# insert into itest(i) select i+8192 from itest;
UPDATE 16380

set most of n's to is but left 4 as NULLs

hannu=# update itest set n=1 where i>1;
UPDATE 16383

and vacuumed just in case 

hannu=# vacuum analyze itest;
VACUUM

now selects for real value do use index

hannu=# explain select * from itest where n = 7;
NOTICE:  QUERY PLAN:

Index Scan using itest_n_idx on itest  (cost=0.00..2.01 rows=1 width=8)

but IS NULL does not.

hannu=# explain select * from itest where n is null;
NOTICE:  QUERY PLAN:

Seq Scan on itest  (cost=0.00..341.84 rows=16 width=8)

EXPLAIN

------------------------
Hannu