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?
أكثر...
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?
أكثر...