I am pretty new to PostGIS and I am trying to solve the following problem.
I need a table with 3D points (x,y,z) that will contain around 1-10million points. I want to be able to efficiently ask the table for 100-1000 points that are closest to some given point - parameter that is going to be different for each query.
Now, I started playling around this with following table:
CREATE TABLE aa ( id serial NOT NULL, point point, s integer ) WITH ( OIDS=FALSE );
CREATE INDEX aa_p_s_idx ON aa USING gist (point );
Queries like this:
select * from aa order by point <-> '0,0' limit 100;
are lightning fast (between 10-100msec).
But the problems started when I wanted to move to 3D. It seems I cannot use the point
type anymore - I had to use geometry
. And the <->
operator works on 2D distance. I ended up with:
CREATE TABLE db_testpoints ( id serial NOT NULL, point geometry NOT NULL, CONSTRAINT db_testpoints_pkey PRIMARY KEY (id ),
CONSTRAINT enforce_dims_point CHECK (st_ndims(point) = 3),
CONSTRAINT enforce_geotype_point CHECK (geometrytype(point) = 'POINT'::text OR point IS NULL), CONSTRAINT enforce_srid_point CHECK (st_srid(point) = 4326) ) WITH ( OIDS=FALSE );CREATE INDEX db_testpoints_point_id ON db_testpoints USING gist
(point );
and even queries:
select * from db_testpoints order by point <-> ST_geomfromewkt('POINT(0 0 0)') limit 100;
take around 2000msec!
- Is there a possibility to have better performance here if I used some other column type, some other index etc?
- Is there a similar operator to
<->
that works on 3D? I could probably use some distance function, but I heard then the index would not be used then...
Any pointers appreciated, thanks in advance!
Update
EXPLAIN ANALYZE for both queries shed some light:
"1. Limit (cost=0.00..1.28 rows=10 width=24)"
" -> Index Scan using aa_p_s_idx on aa (cost=0.00..985278.95 rows=7691709 width=24)"
" Order By: (point <-> '(0,0)'::point)"
"2. Limit (cost=46786.64..46786.66 rows=10 width=136)"
" -> Sort (cost=46786.64..49389.14 rows=1041000 width=136)"
" Sort Key: (((point)::box <-> '(0,0),(0,0)'::box))"
" -> Seq Scan on db_testpoints (cost=0.00..24291.00 rows=1041000 width=136)"
So it looks like in the second query, the index is not used at all...how can I use it and is it going to work with 3D?