3

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?

3

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);
  • Thank you! I took some hours to recreate the new index but now it works! Thanks! – cesare Jun 4 '15 at 15:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.