Skip site navigation (1) Skip section navigation (2)

Re: Null values in indexes

From: Paul Ramsey <pramsey(at)refractions(dot)net>
To: Dann Corbit <DCorbit(at)connx(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org,dblasby(at)refractions(dot)net, chodgson(at)refractions(dot)net,jeff(at)refractions(dot)net
Subject: Re: Null values in indexes
Date: 2002-05-29 17:16:06
Message-ID: 3CF50CD6.46FB45FD@refractions.net (view raw or flat)
Thread:
Lists: pgsql-hackers
Just to clarify:

- With respect to null safety. My understanding is the Oleg and Teodor
put support for nulls into the GiST indexing prior to the 7.2 release,
so 7.2 GiST should already be null-safe. Our project was just to take
our GiST bindings in PostGIS and update them to the new 7.2 GiST API, we
did no work on null-safety, null-safety was just one of the side
benefits we received as a result of updating our code to the 7.2 GiST
indexes.

- With respect to code contribution. If we find ourselves making changes
to the mainline PgSQL distribution we will always submit back. End of
story. All our changes have been to PostGIS itself, with the aim of
supporting 7.2. 7.2 rocks, we love it. :)

- There is one outstanding bug which we identified and Oleg and Teodor
fixed, but it is to the code in contrib/rtree, not in the mainline, and
Oleg and Teodor have already submitted that patch to Bruce. I believe
there was some unresolved discussion regarding whether to cut a 7.2.2
release including that patch and a few other housekeeping items.

Paul


Dann Corbit wrote:
> 
> > -----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.

-- 
      __
     /
     | Paul Ramsey
     | Refractions Research
     | Email: pramsey(at)refractions(dot)net
     | Phone: (250) 885-0632
     \_

In response to

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-05-29 17:21:37
Subject: Re: Null values in indexes
Previous:From: Dann CorbitDate: 2002-05-29 17:00:03
Subject: Re: Null values in indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group