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!

For query performance related question always post the query plan: `explain (analyse, buffers) ...` — Jim Jones, May 22 '23 at 08:25

score 1 · Answer 1 · answered May 10 '23 at 10:50

1

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.

answered May 10 '23 at 10:50

SG92

21
3

If using `&&` here does not help, your index is not properly set up - wrong CRS or type, or stale statistics. – geozelot May 14 '23 at 06:44
Although it seems a reasonable approach you might take into account that a centroid of a multipolygon might be very misleading, e.g. the centroid of UK if you consider the Falkland Islands (close to Argentina) ;) Not to mention that operation like touch won't be possible anymore. I would rather invest more time in analysing the query plan instead of simplifying the data set so drastically. cheers – Jim Jones May 22 '23 at 08:30

score 0 · Answer 2 · answered May 07 '23 at 22:00

0

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.

answered May 07 '23 at 22:00

O. Jones

103,626
17
118
172

Hello. Thanks, but spatial index is already in place. – SG92 May 08 '23 at 18:28

geozelot · Answer 3 · 2023-05-09T12:01:42.293

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
;

POSTGIS Optimize query

3 Answers3