CREATE INDEX on column of type 'point'
От | Mario Splivalo |
---|---|
Тема | CREATE INDEX on column of type 'point' |
Дата | |
Msg-id | 4AB1483C.5030701@megafon.hr обсуждение исходный текст |
Список | pgsql-sql |
As I have discovered, there is no way to just create index on a column of type 'point' - postgres complains about not knowing the default operator class, no matter what index type I use. Now, my table looks like this: CREATE TABLE places (place_id integer primary key,coordinates point,value integer,owner_id integer ); owner_id is foreign-keyed to the owners table and there is an index on that column. Now, my queries would search for places that are of certain value, maybe owned by certain owner(s), in 'range' within specified circle. Something like this: SELECT* FROMplaces WHEREcoordinates <@ '<(320,200),200>'::circleAND value BETWEEN 27 AND 80; I get a sequential scan on that table. Reading trough the mailinglist archives I found suggestion Tom Lane made, saying that I should create functional index on table places create index ix_coords on places using gist (circle(coordinates, 0)); And then change the WHERE part of my query like this: WHERE circle(coordinates, 0) <@ '<(320,200),200'>::circle AND value BETWEEN 27 AND 80; Am I better of using 'circle' as data type for column 'coordinates'? Are there any other options? I know there is PostGIS, but that seems like a quite a big overhead. I'll only be checking if some point is in our out of some circle. Mario
В списке pgsql-sql по дате отправления: