Re: Null values in indexes

Hmm... I think there is some confusion here.

Oleg and Teodor updated the GiST indexing to be null safe for postgresql 7.2.
The changes we made to PostGIS were just to allow our spacial indexing
support functions to work with the changes made in the actual GiST indexing
code (the GiST interface changed somewhat from postgresql 7.1 -> 7.2).

And for the record, I'm confident that we would submit a patch for postgresql
if something like this did come up.

Chris Hodgson

Dann Corbit <DCorbit(at)connx(dot)com> said:

> > -----Original Message-----
> > From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> > Sent: Wednesday, May 29, 2002 9:07 AM
> > To: Jan Wieck
> > Cc: Oleg Bartunov; Teodor Sigaev; pgsql-hackers(at)postgresql(dot)org
> > Subject: Re: [HACKERS] Null values in indexes
> >
> >
> > Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> > > Tom Lane wrote:
> > >> Hannu Krosing <hannu(at)tm(dot)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?
>
> The PostGIS people have already fixed it. However, they may not be
> willing to contribute the patch. On the other hand, I think it would be
> in their interest, since the source code trees will fork if they don't
> and they will have trouble staying in synch with PostgreSQL
> developments. (See the 7.2 index project here:
> http://postgis.refractions.net/
> http://postgis.refractions.net/news/index.php?file=20020425.data
> )
>
> If they are not willing to commit a patch, I suspect that they will at
> least tell you what they had to do to fix it and it could be performed
> internally.
>
>

--