Thread: Rtree; cannot create index on polygons with lots of points

I'm trying to form an rtree index on a custom datatype, and I've come
across a problem.  The problem also affects the standard geometric
datatypes.

Here's a simple example:

> create table test_geom (poly polygon);

> insert into test_geom values ( '<LOTS OF POINTS>');  (...)

So far, so good, but when you try to create an rtree index, you get;

> create index quick on test_geom using rtree (poly);
ERROR:  index_formtuple: data takes 20040 bytes, max is 8191

This will happen if the size of the polygon object (after compression)
is greater than the page size.  Everything works fine if all the
polygons (after compression) are < 8k in size.


The polygon type is actually creating the rtree index on a small portion
of the actual polygon data (its boundingbox, NOT the actual points). 
Why does the index need to store the entire geometry?  Is that some type
of by-product of how the index works?  Or is it because the "~="
(is_same) operator actually needs to know the entire geometry?
If its because of the "~=" operator, could we solve this by making "~="
just look at the bounding box?  Or will that have bad side-effects?

I noticed that the GiST indexing has compress and decompress functions -
could this type of index be used?  {I first tryed making a GiST index,
but it didnt work for me.  I'm using the rtree index because it worked
fine.}

My understanding of the actual mechanics of postgresql indexing is
pretty much nil.

Thanks for your help,
dave
ps. I'm using 7.1.1 on Solaris.

On Wed, 23 May 2001, Dave Blasby wrote:

>
> I noticed that the GiST indexing has compress and decompress functions -
> could this type of index be used?  {I first tryed making a GiST index,
> but it didnt work for me.  I'm using the rtree index because it worked
> fine.}

What're the problem with GiST ? Did you try Rtree implementation using
GiST (http://www.sai.msu.su/~megera/postgres/gist) ?
We didn't implemented polygon datatype but it's rather straightforward.

Regards,    Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Dave Blasby <dblasby@refractions.net> writes:
> So far, so good, but when you try to create an rtree index, you get;
>> create index quick on test_geom using rtree (poly);
> ERROR:  index_formtuple: data takes 20040 bytes, max is 8191

Yup.  We don't yet have a solution that allows index entries to be moved
into a TOAST table (and even if we did, it'd doubtless be slower than
one would like for an index).

> The polygon type is actually creating the rtree index on a small portion
> of the actual polygon data (its boundingbox, NOT the actual points). 
> Why does the index need to store the entire geometry?

rtree doesn't have any notion of compression or lossy storage of data.
GIST does, so I'd recommend that you take a hard look at moving over
to GIST.  Over the long run I think we are going to abandon rtree in
favor of GIST --- the latter probably has more bugs at present, but once
those are flushed out I see no real reason to keep supporting rtree.
        regards, tom lane