Rtree; cannot create index on polygons with lots of points

From: Dave Blasby <dblasby(at)refractions(dot)net>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Rtree; cannot create index on polygons with lots of points
Date: 2001-05-24 00:19:03
Message-ID: 3B0C5377.1F567F68@refractions.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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.

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Christopher Kings-Lynne 2001-05-24 01:34:20 RE: DROP CONSTRAINT patch
Previous Message Peter Eisentraut 2001-05-24 00:12:53 Re: [COMMITTERS] pgsql/src/bin/scripts Makefile createlang.sh