POSTGIS Optimize query

Question

I have a table with a column "multip" type GEOMETRY which contains a multipolygon. Each multipolygon contains several polygons. My table is very large (12 millions records).

I need a fast query on my table which extracts all lines of my table which has polygons which are in a map visible frame (latmin, latmax, lngmin, lngmax).

I currently use the ST_Dwithin operator with the following query:

SELECT id, mydata FROM mytable WHERE ST_Dwithin(multip, ST_TRANSFORM(ST_MakeEnvelope(" + myframe.join(',') + ", 4326), 2154),0) LIMIT 100

but this request is too slow.

Is there a way to speed up the query using a simplified version of the multipolygon ? For example by building a new column with polygon centroids instead?

Thanks for your suggestions!

Answer 1

It looks like you need a spatial index on your table.

Try this

CREATE INDEX multip ON my table USING GIST (geom);

It might work, and might not, but it's worth a try.

Answer 2

ST_DWithin is a swiss army knife, even to mock the functionity of ST_Intersects - but its prime dependency for peformance is the product of the vertex counts of the geometries in consideration.

The spatial index efficiency is generally being defeated by arbitrarily large and widespread multi-part geometries - in fact, it is not only recommended to dump multi-part geometries into their components and index those, but also good practice to ST_SubDivide areal geometries even further in order to reduce per-component vertex count!

---

With that being said, you're still in luck: since all you want is bounding box overlaps between your geometries and a viewport rectangle, just use the appropriate, index driven && operator instead:

SELECT
  *
FROM
  mytable
WHERE
  multip && ST_Transform(ST_MakeEnvelope(" + myframe.join(',') + ", 4326), 2154)
LIMIT
  100
;

Answer 3

Thanks for taking the time to answer! I already had an index on my column. Using "&&" or ST_INTERSECTS did not improve performance.

But I finally found a solution : I created a new column containing only the centroids of the polygons instead of the multipolygon itself and it appears to be way more effective than intersecting with polygons.