index on a box

Поиск
Список
Период
Сортировка
Искать
От
Dustin Sallings
Тема
index on a box
Дата
Msg-id
Pine.OSX.4.33.0106200150320.1274-100000@dustinmobile
Список
Дерево обсуждения
index on a box Dustin Sallings <dustin@spy.net>
Re: index on a box Tom Lane <tgl@sss.pgh.pa.us>
Re: index on a box Dustin Sallings <dustin+postgres@spy.net>
Re: index on a box Tom Lane <tgl@sss.pgh.pa.us>
Re: index on a box Dustin Sallings <dustin+postgres@spy.net>
Re: index on a box Dustin Sallings <dustin+postgres@spy.net>
Re: index on a box Tom Lane <tgl@sss.pgh.pa.us>
Re: index on a box Dustin Sallings <dustin+postgres@spy.net>

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

В списке pgsql-general по дате отправления
От: David M. Richter
Дата:
Сообщение: IRIX AND POSTGRES 7.1.2
От: Dave Cramer
Дата:
FAQ