<div dir="ltr">Hi,<br><div><br></div><div>After reading through Cascaded Union GEOS code I see why your benchmark might have been unexpectedly slower. Theory:</div><div><br>In serialfn you union what you can and that's good but may leave you with some multi-geometries, in deserialfn you stash things you got from workers into a collection without stripping top level collection. Cascaded Union seems to not strip two levels of geometry collections, just one, and thus not really being Cascaded. </div><div><br>What do you think?</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Mar 13, 2018 at 4:54 PM Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca">pramsey@cleverelephant.ca</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">My experience was that collecting took minute fractions of time. It's<br>
hard to see how doing it parallel would be a win, particularly since<br>
the serialize/deserialize step at the end before the finalfn would be<br>
relatively expensive.<br>
<br>
On Mon, Mar 12, 2018 at 9:52 PM, Regina Obe <<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>> wrote:<br>
> Paul,<br>
><br>
> Well thanks for giving it a shot. I feared that would be the conclusion until ORDER BY in aggregates for parallelization is supported, which I don't have high hopes for given when I asked on hackers about their reason for not implementing it for string_agg., array_agg<br>
><br>
> <a href="https://www.postgresql.org/message-id/flat/000101d2df41%246feda800%244fc8f800%24%40pcorp.us#000101d2df41$6feda800$4fc8f800$@pcorp.us" rel="noreferrer" target="_blank">https://www.postgresql.org/message-id/flat/000101d2df41%246feda800%244fc8f800%24%40pcorp.us#000101d2df41$6feda800$4fc8f800$@pcorp.us</a><br>
><br>
><br>
> That said, I do wonder if there is a benefit to having parallelization for ST_Collect. That often users don't care about having things ordered, then having collapsing 3 biggish geometry collections<br>
> May not save anything and I suppose you'd have to contend with ST_Collect (bunch_of_linestrings) is different from ST_Collect(bunch of multilinestrings) which would be the final.<br>
><br>
> ST_Union (rast) seems promising since I don't think it counts on an order anyway and is just building a big o'raster from smaller rasters in any order it gets.<br>
><br>
> Bborie, thoughts on if ST_Union(rast) for raster would be useful?<br>
><br>
> If I become brave enough I may attempt it for raster just to see how good or bad it is.<br>
><br>
> Thanks,<br>
> Regina<br>
><br>
> -----Original Message-----<br>
> From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org" target="_blank">postgis-devel-bounces@lists.osgeo.org</a>] On Behalf Of Paul Ramsey<br>
> Sent: Monday, March 12, 2018 6:48 PM<br>
> To: PostGIS Development Discussion <<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a>><br>
> Subject: [postgis-devel] ST_Union Parallel Experiment<br>
><br>
> Hey all,<br>
> I just wanted to record for posterity the results of my experiment in making a parallel version of ST_Union().<br>
><br>
> The basic theory was:<br>
><br>
> * add serialfn/deserialfn/combinefn to the aggregate<br>
> * in the serialfn, do an initial cascaded union of everything collected by the worker<br>
> * in the combinefn, do pairwise union of each set of partials<br>
><br>
> The obvious drawback is, particularly for inputs that are a "coverage"<br>
> (many polygons, covering an area, with no overlap) the workers won't be fed a neat contiguous area, so the main promise of cascaded union, that it eliminates the maximum number of vertices possible at each step, is broken.<br>
><br>
> In fact, that is more-or-less what I observed. The union was quite a bit slower, even when it was using up twice as much CPU (two core<br>
> laptop)<br>
><br>
> (The debug messages are the parallel-only functions<br>
> (serialfn/deserialfn/combinefn) being called in the parallel<br>
> execution.)<br>
><br>
> postgis25=# select st_area(st_union(geom)) from va_ply_17;<br>
> DEBUG: pgis_geometry_union_serialfn called<br>
> DEBUG: pgis_geometry_union_serialfn called<br>
> DEBUG: pgis_geometry_union_serialfn called<br>
> DEBUG: pgis_geometry_union_deserialfn called<br>
> DEBUG: pgis_geometry_union_deserialfn wkb size = 8526407<br>
> DEBUG: pgis_accum_combinefn called<br>
> DEBUG: pgis_geometry_union_deserialfn called<br>
> DEBUG: pgis_geometry_union_deserialfn wkb size = 4236637<br>
> DEBUG: pgis_accum_combinefn called<br>
> DEBUG: pgis_geometry_union_deserialfn called<br>
> DEBUG: pgis_geometry_union_deserialfn wkb size = 6526511<br>
> DEBUG: pgis_accum_combinefn called<br>
> st_area<br>
> -----------------<br>
> 1070123068374.1<br>
> (1 row)<br>
><br>
> Time: 106545.200 ms (01:46.545)<br>
><br>
> Force the plan to be single-threaded, and run again.<br>
><br>
> postgis25=# set max_parallel_workers_per_gather = 0; postgis25=# select st_area(st_union(geom)) from va_ply_17;<br>
> st_area<br>
> ------------------<br>
> 1070123068374.11<br>
> (1 row)<br>
><br>
> Time: 66527.914 ms (01:06.528)<br>
><br>
> Damn, it's faster.<br>
><br>
> It s possible that if the partials were fed inputs in a spatially correlated order the final merge might be no worse than the usual top-level merge in a cascaded union. However, forcing an ordering in the aggregate strips out the parallel plans.<br>
><br>
> postgis25=# set max_parallel_workers_per_gather = 2; postgis25=# explain select st_area(st_union(geom order by geom)) from va_ply_17;<br>
> QUERY PLAN<br>
> ------------------------------------------------------------------------<br>
> Aggregate (cost=15860.58..15860.62 rows=1 width=8)<br>
> -> Seq Scan on va_ply_17 (cost=0.00..1715.58 rows=5658 width=6181)<br>
><br>
> If the order by trick worked, I'd hope that the parallel execution might win, but since it doesn't it's best to just leave it "as is".<br>
><br>
> The branch is available here for anyone interested in perusing it.<br>
><br>
> <a href="https://github.com/pramsey/postgis/tree/svn-trunk-parallel-union" rel="noreferrer" target="_blank">https://github.com/pramsey/postgis/tree/svn-trunk-parallel-union</a><br>
><br>
> ATB,<br>
><br>
> P<br>
> _______________________________________________<br>
> postgis-devel mailing list<br>
> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
><br>
> _______________________________________________<br>
> postgis-devel mailing list<br>
> <a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a><br>
_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org" target="_blank">postgis-devel@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-devel" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-devel</a></blockquote></div><br clear="all"><div><br></div>-- <br><div dir="ltr" class="gmail_signature"><div dir="ltr"><div><div>Darafei Praliaskouski</div><div>Support me: <a href="http://patreon.com/komzpa" target="_blank">http://patreon.com/komzpa</a></div></div></div></div>