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. ____________
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 |