8

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!

  • Just to be clear, you want the union of the geometries for every combination of column1, column2 and column3? Can you define large, complex and slow and what is explain showing? – John Powell Jan 23 '15 at 16:51
  • 1
    John; yes, I want the union for every combination of column1,2 and 3. I'm not sure how to quantify 'large' - but it is a number of very complex (many vertices) polygons with messy overlaps and islands, etc. I'll have to do some research into 'explain' before I can answer your last question! – Darren Cope Jan 23 '15 at 16:56
  • Explain might not be very helpful in this case, as it mostly measures the disk seek time to actually read the rows, based on the table statistics, indexes, etc. It does not take into account the run time of a function like ST_Union, which depends on the complexity of the polygons, number of overlaps, etc.. – John Powell Jan 23 '15 at 17:10
  • 1
    Please edit the question to add details. – Vince Jan 23 '15 at 20:08
  • 1
    Depends also on your GEOS version. Better aggregation algorithms were introduced at version 3.1.0. – Scro Jan 23 '15 at 21:30
3

This kind of operation uses a lot of work memory as I recall, so you want to make sure you are not at default settings for this which is pretty low;

Try something like

SET work_mem=50000;
Then run your query

You might want to play around with that workmem setting

You'll also want to dump that into a table -- not output to screen. I assume you know that already

Other things you want to verify -- which I put in comments but will repeat here:

There are two things that improved union speed -- the cascade thing you pointed out, and for polygon count the faster array accum (which I think came in PostGIS 1.5 (might be 1.4 can't recall), PostgreSQL 8.4 (migth be 9.0 can't recall)). Also even a newer GEOS won't do good if you are running < PostGIS 1.4

So checking both postgis version and postgresql version are important

SELECT postgis_full_version() || ' ' || version();
  • There is also ST_MemUnion. Uses less memory, more processor: postgis.net/docs/ST_MemUnion.html – Scro Jan 27 '15 at 2:50
  • 3
    That function is pretty old. I think the new ST_Union implemntation actually conserves memory better than that function. – LR1234567 Jan 27 '15 at 3:15
2

Even before you execute ST_Union

ANALYZE your database to update query stats.

VACUUM your database to purge if you are not already running Autovacuum. Check your main settings to make sure that they are set to sensible values..

shared_buffers should be 10% to 25% of available RAM
effective_cache_size should be 75% of available RAM 

Test changing work_mem: increase it to 8MB, 32MB, 256MB, 1GB. Does it make a difference?

*32MB is default

source: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

  • Thanks. I just tried ANALYZE, VACUUM and increasing shared_buffers and effective_cache_size, and had the same issue. I'll keep tweaking as time allows. – Darren Cope Feb 3 '15 at 18:31
  • @DarrenCope any progress? I'm facing the same issue. – Michal Zimmermann Mar 17 '15 at 14:22
  • @zimmi; unfortunately not :( I'm still where I was before! Are you doing the exact same thing? Perhaps share an example and see if there are any similarities – Darren Cope Mar 17 '15 at 18:49
  • 1
    @DarrenCope ST_Buffer(St_Collect(wkb_geometry), 0) seems to be a lot faster and it fits my needs. It might help you as well. – Michal Zimmermann Mar 18 '15 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.