Postgis query on 3D cube does not use index

Question

I am testing Postgis on the follow query:

SELECT * FROM particles 
WHERE position &&& 
'LINESTRING(0.08537 0.0738 0.0145,0.0953 0.0837 0.0345)';

that returns about 100k rows from a data set of about 100M. The schema of the table is

Table "public.particles"
 Column | Type | Modifiers 
----------+------------------+-----------
 partid | integer | not null
 time | double precision | not null
 position | geometry | 
Indexes:
 "particles_pkey" PRIMARY KEY, btree (partid, "time")
 "particles_the_geom_gist" gist ("position")
Check constraints:
 "enforce_dims_the_geom" CHECK (st_ndims("position") = 3)

Even tought I have an index on the attribute position, the query is always executed with a full table scan and so it is particularly slow.

I also tried

set enable_seqscan = off;

but it did not change anything.

Using different query like,

SELECT count(*) FROM particles WHERE ST_3DDWithin(position, 'SRID=4326;POINT(0.08537 0.0738 0.0145)',0.01);

which retrieves objects within a give distance from a point, Postgres uses the index, why it doesn't for the other query?

Answer 1

You probably created a 2d index, not an n-d index, which is what the &&& operator uses.

CREATE INDEX particles_gix ON particles USING GIST (position gist_geometry_ops_nd);

• cesare: Thank you! I took some hours to recreate the new index but now it works! Thanks!