Significant performance issues with PostGIS UnaryUnion

Question

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"

  UNION ALL

  SELECT * FROM "input_table_2"

  UNION ALL

  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

Any suggestions?

Other information:

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"

Answer 1

You're right that it's particularly odd that running an intermediate process is faster than a unitary process, since the cascaded union should shake out a deterministic ordering of operations automatically. So, you've found an interesting result, which might be worth analyzing for performance improvement. You've also probably hit on a solution to your proximate problem, which is to just manually re-order things.

(Incidentally, I tend to use garden variety ST_Union rather than wrapping up unary union with collect, so here's an alternative SQL)

SELECT ST_Union(geom) AS new_geom FROM ( 
  SELECT ST_Union(ST_Transform(geom, 4326)) AS geom FROM "input_table_1"
  UNION ALL
  SELECT ST_Union(ST_Transform(geom, 4326)) AS geom FROM "input_table_2"
  UNION ALL
  SELECT ST_Union(ST_Transform(geom, 4326)) AS geom FROM "input_table_3" 
) as input_geom;

Since you're unioning what are probably large multi-polygons, it's possible that breaking them into their component polygons before unioning will improve the performance of the operation (if this turns out to be so, it's a clear place we could improve the PostGIS code (it's possible we already do this, but I haven't broken open the code)).

SELECT ST_Union(geom) AS new_geom FROM ( 
  SELECT (ST_Dump(ST_Transform(geom, 4326))).geom AS geom FROM "input_table_1"
  UNION ALL
  SELECT (ST_Dump(ST_Transform(geom, 4326))).geom AS geom FROM "input_table_2"
  UNION ALL
  SELECT (ST_Dump(ST_Transform(geom, 4326))).geom AS geom FROM "input_table_3" 
) as input_geom;

Best of luck!