$Header: /cvsroot/pgsql/doc/src/sgml/gist.sgml,v 1.12 2003/09/29 18:18:35 momjian Exp $

<Chapter Id="gist">

<DocInfo>

<AuthorGroup>

<Author>

<FirstName>Gene</FirstName>

<Surname>Selkov</Surname>

</Author>

</AuthorGroup>

<Date>Transcribed 1998-02-19</Date>

</DocInfo>

<Title>GiST Indexes</Title>

<Para>

The information about GIST is at

<ULink url="http://GiST.CS.Berkeley.EDU:8000/gist/">http://GiST.CS.Berkeley.EDU:8000/gist/</ULink>

with more on different indexing and sorting schemes at

<ULink url="http://s2k-ftp.CS.Berkeley.EDU:8000/personal/jmh/">http://s2k-ftp.CS.Berkeley.EDU:8000/personal/jmh/</ULink>.

And there is more interesting reading at

<ULink url="http://epoch.cs.berkeley.edu:8000/">http://epoch.cs.berkeley.edu:8000/</ULink> and

<ULink url="http://www.sai.msu.su/~megera/postgres/gist/">http://www.sai.msu.su/~megera/postgres/gist/</ULink>.

</para>

<Para>

<Note>

<Title>Author</Title>

<Para>

This extraction from an email sent by

Eugene Selkov, Jr. (<email>selkovjr@mcs.anl.gov</email>)

contains good information

on GiST. Hopefully we will learn more in the future and update this information.

- thomas 1998-03-01

</Para>

</Note>

</para>

<Para>

Well, I can't say I quite understand what's going on, but at least

I (almost) succeeded in porting GiST examples to linux. The GiST access

method is already in the postgres tree (<FileName>src/backend/access/gist</FileName>).

</para>

<Para>

<ULink url="ftp://s2k-ftp.cs.berkeley.edu/pub/gist/pggist/pggist.tgz">Examples at Berkeley</ULink>

come with an overview of the methods and demonstrate spatial index

mechanisms for 2D boxes, polygons, integer intervals and text

(see also <ULink url="http://gist.cs.berkeley.edu:8000/gist/">GiST at Berkeley</ULink>).

In the box example, we

are supposed to see a performance gain when using the GiST index; it did

work for me but I do not have a reasonably large collection of boxes

to check that. Other examples also worked, except polygons: I got an

error doing

<ProgramListing>

test=> CREATE INDEX pix ON polytmp

test-> USING GIST (p:box gist_poly_ops) WITH (ISLOSSY);

ERROR: cannot open pix

(PostgreSQL 6.3 Sun Feb 1 14:57:30 EST 1998)

</ProgramListing>

</para>

<Para>

I could not get sense of this error message; it appears to be something

we'd rather ask the developers about (see also Note 4 below). What I

would suggest here is that someone of you linux guys (linux==gcc?) fetch the

original sources quoted above and apply my patch (see attachment) and

tell us what you feel about it. Looks cool to me, but I would not like

to hold it up while there are so many competent people around.

</para>

<Para>

A few notes on the sources:

</para>

<Para>

1. I failed to make use of the original (HP-UX) Makefile and rearranged

the Makefile from the ancient postgres95 tutorial to do the job. I tried

to keep it generic, but I am a very poor makefile writer -- just did

some monkey work. Sorry about that, but I guess it is now a little

more portable that the original makefile.

</para>

<Para>

2. I built the example sources right under pgsql/src (just extracted the

tar file there). The aforementioned Makefile assumes it is one level

below pgsql/src (in our case, in pgsql/src/pggist).

</para>

<Para>

3. The changes I made to the *.c files were all about #include's,

function prototypes and typecasting. Other than that, I just threw

away a bunch of unused vars and added a couple parentheses to please

gcc. I hope I did not screw up too much :)

</para>

<Para>

4. There is a comment in polyproc.sql:

<ProgramListing>

-- -- there's a memory leak in rtree poly_ops!!

-- -- CREATE INDEX pix2 ON polytmp USING RTREE (p poly_ops);

</ProgramListing>

Roger that!! I thought it could be related to a number of

<ProductName>PostgreSQL</ProductName> versions

back and tried the query. My system went nuts and I had to shoot down

the postmaster in about ten minutes.

</para>

<Para>

I will continue to look into GiST for a while, but I would also

appreciate

more examples of R-tree usage.

</para>

</Chapter>