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
