index on a box

From: Dustin Sallings <dustin(at)spy(dot)net>
To: <pgsql-general(at)postgresql(dot)org>
Subject: index on a box
Date: 2001-06-20 10:25:01
Message-ID: Pine.OSX.4.33.0106200150320.1274-100000@dustinmobile
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


I've got a site with a ton of geometric data and I'm using a
little of postgresql's geometrical types. I've got very large polygons,
up to 12kilopoints or so, in individual rows with floats for my x and y
values. I'm calculating a box that contains all of my points and am using
the @ operator to find my polygons by a point.

I was wondering, however, if there's a way I can use an index to
avoid table scanning for this. The relevant parts of my sample table look
like this:

create table tmp (
id integer,
name text,
b box
)

and I added the following index:

create index tmp_bybox on tmp using rtree(b);

I've got 33,507 rows currently (still loading data).

Here are the problems I'm having:

explain select name from tmp where point(-121,37) @ b order by area(b);

Sort (cost=2428.02..2428.02 rows=16754 width=44)
-> Seq Scan on tmp (cost=0.00..969.84 rows=16754 width=44)

Any ideas that might help me speed things up?

--
SPY My girlfriend asked me which one I like better.
pub 1024/3CAE01D5 1994/11/03 Dustin Sallings <dustin(at)spy(dot)net>
| Key fingerprint = 87 02 57 08 02 D0 DA D6 C8 0F 3E 65 51 98 D8 BE
L_______________________ I hope the answer won't upset her. ____________

Responses

Browse pgsql-general by date

  From Date Subject
Next Message tillea 2001-06-20 11:23:26 Postgres permissions for www-data
Previous Message Yasuo Ohgaki 2001-06-20 06:53:00 Re: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE on 7.0