From: | Hannu Krosing <hannu(at)tm(dot)ee> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Dann Corbit <DCorbit(at)connx(dot)com>, pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Null values in indexes |
Date: | 2002-05-28 19:10:15 |
Message-ID: | 1022613027.1901.3.camel@rh72.home.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 it
was 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannu Krosing | 2002-05-28 19:16:07 | Re: wierd AND condition evaluation for plpgsql |
Previous Message | Dann Corbit | 2002-05-28 19:06:49 | Null values in indexes |