<div dir="ltr"><div dir="ltr">Yes, that's exactly the test needed. So it looks like ST_Union(agg) on the 11 instance is much slower than on 9.5:<div><br></div><div>11: 25335.136 ms<br>9.5: 4409.919 ms<br></div><div><br></div><div>The question is why... We'll have a look and see if there's any regression in GEOS (which is what is performing the union).</div><div><br></div><div>Are you able to share the dataset from the above test? (As a file of WKT or WKB)</div></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Thu, Apr 11, 2019 at 11:55 AM Lars Aksel Opsahl <<a href="mailto:Lars.Opsahl@nibio.no">Lars.Opsahl@nibio.no</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">
<div dir="ltr">
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Hi</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Do mean something like this ?</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Here is the result from postgres 11 -</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"></span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">EXPLAIN ANALYZE</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">select (ST_dump(st_union(geo))).geom as geo</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">from sde_markslag.markslag_myrikilden_temp </span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">where gid < 10000</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">;</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> QUERY PLAN </span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">---------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Result (cost=62634.26..62899.53 rows=1000 width=32) (actual time=25249.956..25331.325 rows=9205 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> ProjectSet (cost=62634.26..62639.53 rows=1000 width=32) (actual time=25249.953..25330.483 rows=9205 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> Aggregate (cost=62634.26..62634.28 rows=1 width=32) (actual time=25244.718..25244.719 rows=1 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> Seq Scan on markslag_myrikilden_temp (cost=0.00..62609.51 rows=9900 width=1593) (actual time=0.008..173.033 rows=9999 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Filter: (gid < 10000)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Rows Removed by Filter: 557242</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Planning Time: 0.064 ms</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Execution Time: 25335.136 ms</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">(8 rows)</span></p>
<div><br>
</div>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<div><br>
</div>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Here is the same postgres 9.5 -</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"></span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">EXPLAIN ANALYZE</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">[more] - > select (ST_dump(st_union(geo))).geom as geo</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">[more] - > from sde_markslag.markslag_myrikilden_temp </span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">[more] - > where gid < 10000</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">[more] - > ;</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> QUERY PLAN </span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">----------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Aggregate (cost=170462.27..170467.28 rows=1000 width=1604) (actual time=4311.911..4409.250 rows=9205 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> Seq Scan on markslag_myrikilden_temp (cost=0.00..170437.51 rows=9903 width=1604) (actual time=0.054..629.045 rows=9999 loops=1)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Filter: (gid < 10000)</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Rows Removed by Filter: 557242</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Planning time: 0.045 ms</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Execution time: 4409.919 ms</span></p>
<p style="margin:0px;font-size:11px;line-height:normal;font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">(6 rows)</span></p>
<div><br>
</div>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Thanks </div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Lars</div>
<div>
<div id="gmail-m_-5494934869227346710appendonsend"></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
<br>
</div>
<hr style="display:inline-block;width:98%">
<div id="gmail-m_-5494934869227346710divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>> on behalf of Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@gmail.com</a>><br>
<b>Sent:</b> Thursday, April 11, 2019 8:07 PM<br>
<b>To:</b> PostGIS Users Discussion<br>
<b>Subject:</b> Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11</font>
<div> </div>
</div>
<div>
<div dir="ltr">
<div dir="ltr"><br>
</div>
<br>
<div class="gmail-m_-5494934869227346710x_gmail_quote">
<div dir="ltr" class="gmail-m_-5494934869227346710x_gmail_attr">On Thu, Apr 11, 2019 at 3:27 AM Lars Aksel Opsahl <<a href="mailto:Lars.Opsahl@nibio.no" target="_blank">Lars.Opsahl@nibio.no</a>> wrote:</div>
<blockquote class="gmail-m_-5494934869227346710x_gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">
<div style="font-family:Calibri,Arial,Helvetica,sans-serif;font-size:12pt;color:rgb(0,0,0)">
Can it be problem related ST_Union and aggregate ?</div>
</div>
</blockquote>
<div><br>
</div>
<div>Can you test the performance of aggregate ST_Union on it's own by dropping the GROUP BY and instead using a WHERE clause to union a small-ish subset of the geometries? (But large enough to give a measurable query time.)</div>
</div>
</div>
</div>
</div>
</div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>