I'm trying to execute a 'dissolve' operation in PostGIS using the ST_Union command.
The input layer is admittedly quite large and complex. By 'large' I mean 57,771 features, with number of vertices ranging from 4 to 758,018 per feature, averaging around 86 vertices per feature. Only about 10 of the features have >10,000 vertices. By 'complex' I mean that the polygons have lots of holes, messy overlaps, islands, etc. and that the large polygons tend to have a bounding box that covers many of the smaller polygons, perhaps rending indexes less useful.
The problem is that the the query is extremely slow to the point of being unusable. I read Paul's 2009 post here that lead me to believe that my query should still be fairly fast. I'm using the following command; am I doing something blatantly wrong or inefficient?
SELECT ST_Union(f.geom) as geom, column1,column2,column3 FROM "inputlayer" As f GROUP BY column1,column2,column3
Edit: I am using:
POSTGIS="2.1.4 r12966" GEOS="3.3.3-CAPI-1.7.4" PROJ="Rel. 4.7.1, 23 September 2009" GDAL="GDAL 2.0.0dev, released 2014/04/16" LIBXML="2.7.8" LIBJSON="UNKNOWN" TOPOLOGY RASTER PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
The machine I'm running the db server on is a virtual machine without a lot of power. I'll try the SET work_mem=50000 idea and see how things go!