How to improve PostGIS ST_Union performance?

Question

I need to dissolve some large datasets using PostGIS but it takes a lot of time.

What I have already tried:

Lower the precision of the dataset to 6 digits.
Simplify the features
Cluster the table based on spatial index
Adjusted PostgreSQL based on this article: https://postgis.net/docs/performance_tips.html

My datasets are some RF coverage with 5M of features and 2 ~ 5GB of data.

My postgis version is

POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.6.2-CAPI-1.10.2 4d2925d6" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11" TOPOLOGY RASTER

And my PostgreSQL version is

PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit

Is there a way to improve st_union performance?

Large, complex features take time to assemble. I doubt there's much room for optimization. — Vince, Aug 30 '18 at 12:57
If you need the result fast but it does not need to be extremely accurate you could try the rasterize-polygonize route like in https://gis.stackexchange.com/questions/222976/cleaning-a-large-shapefile-in-order-to-dissolve-features — user30184, Aug 30 '18 at 13:13

score 2 · Answer 1 · answered Aug 30 '18 at 14:44

Said like that, it is indeed complicated to help you, I don't think there is much room to big improvement in with st_union by itself (maybe what user30184 said would be simpler).

But if you give us much infos on the exact problem and the part of the request that is slow, we might see something?

By experience, the ST_Union seems to be a good way at first, but as soon as the data gets bigger it becomes useless. It is usually better to work locally, instead of doing one big structure.

For example, I recently had to change some code: I wanted to test if some roads were inside at least one of multiple overlapping polygon (buffer around some other lines), and I ended changing my request from st_union to something like:

SELECT rt.*
FROM roads rt
LEFT JOIN LATERAL
        (
            SELECT *
                FROM buffer as buf
                WHERE ST_Intersect(rt.geom, buf.geometry)
                ORDER BY ST_MaxDistance(rt.geom, buf.geom_line)
                LIMIT 1
        ) as buf
    ON TRUE
    WHERE buf.id IS NOT NULL;

Which was way better in term of computational time, instead of doing the union, then doing the ST_Intersect request). So it may be a clue.

Maybe what you can use this kind of approach to dissolve only intersecting polygons between them, to form distinct polygones that would be easier for postgres to compute?

How to improve PostGIS ST_Union performance?

1 Answers1