I'm in the process of migrating a number of processes from an ArcGIS environment to PostGIS and have run into a signicant performance issue when using ST_UnaryUnion to emulate the ArcGIS dissolve function.
The process so far is:
Upload 3 datasets, each to a separate table (these are related data, but as most calculations are on each individual set it seems to make sense to keep them separate - note that the table structure is the same). These have been prepared in ArcGIS (including running repair geometry) and uploaded using QGIS.
Run ST_MakeValid on each table (missing out this part results in a GEOS exception, it seems PostGIS is pickier about self-intersections than Arc)
Run the following query (intended to be equivalent to merge/dissolve in Arc):
SELECT ST_UnaryUnion(ST_Collect(input_geom.geom_4326)) AS new_geom FROM
( SELECT * FROM "input_table_1"
SELECT * FROM "input_table_2"
SELECT * FROM "input_table_3" ) as input_geom;
(input table 1 is 60 rows totalling 85K vertices, table 2 is 77 rows totalling 1.6M vertices and table 3 is 56 rows totalling 1.2M vertices, all WGS84)
The above query works, in that it provides the correct area (once projected and the area calculated), but it takes close to 2 hours to run - the equivalent in Arc is 2 minutes! PostGIS is running on a much slower machine, but spatial queries (including a Unary Union of the 3 tables individually) normally only take 1.5-3x longer.
If I perform the above with 2 tables, saving the output as an intermediate and then unioning that with the 3rd table it takes around 5 minutes (this is a possible solution, though it seems like it should be unnecessary with the cascaded union implementation).
I'm aware there are several similar questions to this, and I've tried many of the suggestions, including:
- Upping the available memory for PostGres (currently - shared_buffers: 768MB, work_mem: 768MB, wal_buffers: 128mb - server has total of 2gb ram, around 3/4 is free for postgres. I've tried various memory settings, these are just the largest I've experimented with. Running top suggests that postgres doesn't need more than c. 500MB memory for the query)
- Each table has a spatial index, and I've tried reindexing
- I've run VACUUM ANALYZE on each table prior to running the query
Server is Ubuntu 14.04
Postgres is 9.3.13
PostGIS version data : "POSTGIS="2.1.2 r12389" GEOS="3.4.2-CAPI-1.8.2 r3921" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.1, released 2013/08/26" LIBXML="2.9.1" LIBJSON="UNKNOWN" RASTER"