With 7.1.3, large indexes with null values allowed in one or more of the
columns would cause crashes.  (I have definitely seen this happen).

Here is a project that mentions repairs:
http://postgis.refractions.net/news/index.php?file=20020425.data

Have repairs been effected in 7.2?  Are they delayed until 7.3?

Tue, 28 May 2002 12:06:49 -0700
"Dann Corbit" <DCorbit@connx.com>
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 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




29 May 2002 00:10:15 +0500
Hannu Krosing <hannu@tm.ee>
Re: Null values in indexes
"Dann Corbit" <DCorbit@connx.com> writes:
> With 7.1.3, large indexes with null values allowed in one or more of the
> columns would cause crashes.  (I have definitely seen this happen).
> Have repairs been effected in 7.2?

Submit a test case and we'll tell you ...

			regards, tom lane

Tue, 28 May 2002 15:24:32 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Null values in indexes 
Hannu Krosing <hannu@tm.ee> writes:
> How hard would it be to _not_ include nulls in indexes 
> as they are not used anyway. 

Seems to me that would be a step backwards.

What should someday happen is to make IS NULL an indexable operator.
The fact that we haven't got around to doing so is not a reason to
rip out the underpinnings for it.

			regards, tom lane

Tue, 28 May 2002 18:32:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Null values in indexes 
Tom Lane wrote:
> Hannu Krosing <hannu@tm.ee> writes:
> > How hard would it be to _not_ include nulls in indexes
> > as they are not used anyway.
>
> Seems to me that would be a step backwards.

    It  would cause multi-key indexes beeing unusable for partial
    key lookup. Imagine you have a key over (a, b, c)  and  query
    with  WHERE  a = 1 AND b = 2. This query cannot use the index
    if a NULL value in c  would  cause  the  index  entry  to  be
    suppressed.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



Wed, 29 May 2002 10:38:37 -0400 (EDT)
Jan Wieck <janwieck@yahoo.com>
Re: Null values in indexes
Jan Wieck <janwieck@yahoo.com> writes:
> Tom Lane wrote:
>> Hannu Krosing <hannu@tm.ee> writes:
> How hard would it be to _not_ include nulls in indexes
> as they are not used anyway.
>> 
>> Seems to me that would be a step backwards.

>     It  would cause multi-key indexes beeing unusable for partial
>     key lookup. Imagine you have a key over (a, b, c)  and  query
>     with  WHERE  a = 1 AND b = 2. This query cannot use the index
>     if a NULL value in c  would  cause  the  index  entry  to  be
>     suppressed.

Urgh ... that means GiST indexing is actually broken, because GiST
currently handles multicolumns but not nulls.  AFAIR the planner
will try to use partial qualification on any multicolumn index...
it had better avoid doing so for non-null-capable AMs.

Alternatively, we could fix GiST to support nulls.  Oleg, Teodor:
how far away might that be?

			regards, tom lane

Wed, 29 May 2002 12:07:21 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Null values in indexes 