PostGIS - table with 3d points with fast ordering by distance

Question

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!

1. Is there a possibility to have better performance here if I used some other column type, some other index etc?
2. 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?

Answer 1

<-> is not supported for 3D indexes (though it looks like you have a 2-d index so it should be doing a 2D check).

Anyway use ST_3DDwithin to use an index

SELECT * FROM (select * from db_testpoints WHERE ST_3dDwithin(point,ST_geomfromewkt('POINT(0 0 0)'),somevalue_that_will_guarantee_100_records) ) As foo 
ORDER BY ST_3DDistance(point,ST_geomfromewkt('POINT(0 0 0)'));