[postgis-users] ST_ConcaveHull performance issue

Fri Aug 2 10:40:43 PDT 2019

There is was an issue with ST_Union as applied to invalid polygons introduced in 3.7.1 and 3.6.2 which would make ST_Union run indefinitely.  It appears to still be an open bug.  This smells like that issue.

https://trac.osgeo.org/geos/ticket/867

https://lists.osgeo.org/pipermail/postgis-users/2018-April/042710.html

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Trevor Wiens
Sent: Friday, August 2, 2019 1:16 PM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: Re: [postgis-users] ST_ConcaveHull performance issue

Thanks for your reply. It was mix of points, lines and polygons, but when I broke it down it was the lines causing the problem.

I tried commenting out the line in the ST_ConcaveHull function you suggested but that doesn't make a difference. I suspect there is some underlying library that has changed.

I will open a ticket as you suggest.

Thanks

TSW

On Thu, Aug 1, 2019 at 11:55 PM Darafei "Komяpa" Praliaskouski <me at komzpa.net <mailto:me at komzpa.net> > wrote:

Hi,

There were changes ST_ConcaveHull that fixed robustness of it in 2.2 -> 2.5 chain.
To point where the penalty comes from, try to run a query and on a side console server-side run `sudo perf top`. Function names will get you a rough idea where the execution process lives now.

To further debug, go to blame view in github and try updating st_concavehull (it's in SQL) change by change. Most recent adds an union here, try commenting it out and hot reloading on your db. https://github.com/postgis/postgis/blame/svn-trunk/postgis/postgis.sql.in#L6155

Can you share the data and ticket this on http://trac.osgeo.org/postgis/? 

What is the data structurally? Are these 6000 objects points, or polygons?

On Fri, Aug 2, 2019 at 3:43 AM Trevor Wiens <tsw.web at gmail.com <mailto:tsw.web at gmail.com> > wrote:

I am having difficulty determining why I'm seeing significant differences in performance between two database configurations with the same data.

One one machine (centos) I have the following software:

geos 3.5.0

sfcgal 1.3.1

cgal 4.7.1

postgis 2.2

postgresql 9.4

On a second machine (debian 10) I have the following:

geos 3.7.1

sfcgal 1.3.6

cgal 4.13

postgis 2.5.2

postgresql 9.6

In terms of hardware there is no significant difference, if anything the second machine is more capable, but that is not reflected in my performance results.

On the first machine when I run a ST_ConcaveHull with about 6000 features, I get result a second or two. On the second machine, it won't finish within 30 minutes. Both are using geos as the postgis.backend. I don't understand why the one is so much faster than the other with the identical data and query.

Any suggestions as to what to what the cause may be or how I might diagnose the cause?

Any clues would be greatly appreciated.

TSW

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 

Darafei Praliaskouski

Support me: http://patreon.com/komzpa

_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org> 
https://lists.osgeo.org/mailman/listinfo/postgis-users

-- 

Trevor Wiens
Apropos Information Systems
aproposinfosystems.com <http://aproposinfosystems.com> 
Calgary, Alberta

Ph. 403-973-5901

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190802/b987b6d4/attachment.html>