<div dir="ltr">ST_Union(rast) would be useful and I think the performance should be better assuming all input raster to the aggregate function are in the same grid.<div><br></div><div>-bborie</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Mar 12, 2018 at 9:52 PM, Regina Obe <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">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/<wbr>message-id/flat/000101d2df41%<wbr>246feda800%244fc8f800%24%<wbr>40pcorp.us#000101d2df41$<wbr>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>
<div class="HOEnZb"><div class="h5"><br>
-----Original Message-----<br>
From: postgis-devel [mailto:<a href="mailto:postgis-devel-bounces@lists.osgeo.org">postgis-devel-bounces@<wbr>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">postgis-devel@lists.osgeo.org</a><wbr>><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/<wbr>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_<wbr>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_<wbr>gather = 2; postgis25=# explain select st_area(st_union(geom order by geom)) from va_ply_17;<br>
QUERY PLAN<br>
------------------------------<wbr>------------------------------<wbr>------------<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/<wbr>postgis/tree/svn-trunk-<wbr>parallel-union</a><br>
<br>
ATB,<br>
<br>
P<br>
______________________________<wbr>_________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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/<wbr>mailman/listinfo/postgis-devel</a><br>
<br>
______________________________<wbr>_________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@lists.osgeo.org">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/<wbr>mailman/listinfo/postgis-devel</a></div></div></blockquote></div><br></div>