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