<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body 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);">
Yes then it switched GroupAggregate, but it stil used around 1 hour.</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">sl=# SET enable_hashagg=false;</span></p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo">
<span style="font-variant-ligatures: no-common-ligatures">SET</span></p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo">
<span style="font-variant-ligatures: no-common-ligatures">sl=# 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">sl-# select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (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">sl-# 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">sl-# group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;</span></p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo; min-height: 13px">
<span style="font-variant-ligatures: no-common-ligatures"></span><br>
</p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo; min-height: 13px">
<span style="font-variant-ligatures: no-common-ligatures"></span><br>
</p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo; min-height: 13px">
<span style="font-variant-ligatures: no-common-ligatures"></span><br>
</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=0.42..173072.99 rows=192000 width=46) (actual time=4574.289..3348175.809 rows=482885 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=0.42..123152.99 rows=192000 width=46) (actual time=4574.285..3348126.192 rows=482885 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"> -> GroupAggregate (cost=0.42..122144.03 rows=192 width=46) (actual time=4572.051..3344371.117 rows=166 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"> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext</span></p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo">
<span style="font-variant-ligatures: no-common-ligatures"> -> Index Scan using test_idx on markslag_myrikilden_temp (cost=0.42..112214.91 rows=567241 width=1614) (actual time=0.087..509.296 rows=567241 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"> Planning Time: 0.255 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: 3348188.751 ms</span></p>
<p style="margin: 0px; font-size: 11px; line-height: normal; font-family: Menlo">
<span style="font-variant-ligatures: no-common-ligatures">(7 rows)</span></p>
<br>
</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="appendonsend"></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<hr tabindex="-1" style="display:inline-block; width:98%">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" color="#000000" style="font-size:11pt"><b>From:</b> postgis-users <postgis-users-bounces@lists.osgeo.org> on behalf of Regina Obe <lr@pcorp.us><br>
<b>Sent:</b> Thursday, April 11, 2019 4:42 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 lang="EN-US">
<div class="x_WordSection1">
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D">Can you try to force them to use the same plan.</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D">I vaguely recall running into this issue with HashAggregate or GroupAggregate misbehaving but can't recall the exact fix.</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D">At anyrate to check if that's the issue on your PostgreSQL 11, try doing</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D">SET enable_hashagg=false;</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D"> </span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D">And then rerun your query – that should prevent it from using hashagg and presumably would force it to use groupagg</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:11.0pt; font-family:"Calibri",sans-serif; color:#1F497D"> </span></p>
<div style="border:none; border-left:solid blue 1.5pt; padding:0in 0in 0in 4.0pt">
<div>
<div style="border:none; border-top:solid #E1E1E1 1.0pt; padding:3.0pt 0in 0in 0in">
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<b><span style="font-size:11.0pt; font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt; font-family:"Calibri",sans-serif"> postgis-users [mailto:postgis-users-bounces@lists.osgeo.org]
<b>On Behalf Of </b>Lars Aksel Opsahl<br>
<b>Sent:</b> Thursday, April 11, 2019 6:27 AM<br>
<b>To:</b> PostGIS Users Discussion <postgis-users@lists.osgeo.org><br>
<b>Subject:</b> [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11</span></p>
</div>
</div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
</p>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Hi</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif">We are upgrading from Postgres 9.5 to Postgres 11 and have a problem with ST_Union query. </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif">It used around 5 minutts on Postgres 9.5 but on the Postgres 11 it takes around a hour. </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif">This is two different physical servers.</span></p>
</div>
<div>
<ul type="disc" style="margin-bottom: 0in;">
<li class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif">Postgres 11 server (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz), is running Redhat 7.0 </span></li><li class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif">Postgres 9.5 server (</span><span style="font-size:8.5pt; font-family:"Menlo",serif"> Intel(R) Xeon(R) <span style="color:white; background:black">CPU</span> E5-2667 v2 @ 3.30GHz) </span><span style="font-family:"Calibri",sans-serif">is
running Cent 6.9 .</span></li></ul>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
</p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">The only index on table in the geo column </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<ul type="disc" style="margin-bottom: 0in;">
<li class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;color:black">
<span style="font-family:"Calibri",sans-serif">Sql and query plan for from Postgres 9.5</span></li></ul>
</div>
<div>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Menlo",serif; color:black">POSTGIS="2.2.2 r14797" GEOS="3.5.0-CAPI-1.9.0 r4084" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.9.2, released 2012/10/08" LIBXML="2.7.6" LIBJSON="0.11" TOPOLOGY RASTER</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">sl=# EXPLAIN ANALYZE</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">sl-# </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">select</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">sl-# , st_union(geo)
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">as</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> geo</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">sl-# </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">from</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> sde_markslag.markslag_myrikilden_temp </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">sl-# </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">group</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">by</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> QUERY PLAN </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#4F9192">---------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> QUERY PLAN </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#4F9192">----------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> GroupAggregate (cost=796867.85..1683469.04
<span style="color:#931A68">rows</span>=170173000 width=1668) (actual <span style="color:#931A68">
time</span>=20548.994..386236.175 <span style="color:#931A68">rows</span>=482885 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> -> Sort (cost=796867.85..801122.16
<span style="color:#931A68">rows</span>=1701723 width=1668) (actual <span style="color:#931A68">
time</span>=19215.245..28191.175 <span style="color:#931A68">rows</span>=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> Sort Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> Sort Method: external merge Disk: 834880kB</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> -> Seq Scan <span style="color:#931A68">
on</span> markslag_myrikilden_temp (cost=0.00..180364.23 <span style="color:#931A68">
rows</span>=1701723 width=1668) (actual <span style="color:#931A68">time</span>=0.115..2625.401
<span style="color:#931A68">rows</span>=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> Planning <span style="color:#931A68">
time</span>: 1.942 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif"> Execution <span style="color:#931A68">
time</span>: 386479.469 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif">(8 <span style="color:#931A68">
rows</span>)</span></p>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
</p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<ul type="disc" style="margin-bottom: 0in;">
<li class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Sql and query plan from Postgres 11</span></li></ul>
</div>
<div>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Menlo",serif; color:black">POSTGIS="2.5.2 r17328" [EXTENSION] PGSQL="110" GEOS="3.7.1-CAPI-1.11.1 27a5e771" SFCGAL="1.2.2" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 1.11.4, released 2016/01/25" LIBXML="2.9.1" LIBJSON="0.11"
TOPOLOGY RASTER</span></p>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">EXPLAIN ANALYZE</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">select</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> , st_union(geo)
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">as</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> geo</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">from</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> sde_markslag.markslag_myrikilden_temp </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">group</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">by</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;min-height:15px">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> QUERY PLAN </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#4F9192">-------------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Result (cost=69528.63..120459.99
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192000 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=962.266..3272057.295
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=482885 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> ProjectSet (cost=69528.63..70539.99
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192000 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=962.262..3272011.001
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=482885 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> HashAggregate (cost=69528.63..69531.03
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=962.214..3268248.140
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=166 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> Seq Scan
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">on</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> markslag_myrikilden_temp (cost=0.00..59601.91
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 width=1614) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=0.012..336.203
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Planning Time: 0.163 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Execution Time: 3272080.299 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">(7 </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">)</span></p>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">So on Postgres 9.5 we see groupAggregate but not on postgres 11. </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">If I on postgres 11 tests with ST_collect it's very fast, bu we can not use that result.</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">EXPLAIN ANALYZE</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">select</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_Collect(geo))).geom
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">as</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> geo</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">from</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> sde_markslag.markslag_myrikilden_temp </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">group</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">by</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;min-height:15px">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> QUERY PLAN </span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#4F9192">-------------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Result (cost=69528.63..120459.99
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192000 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=934.723..5611.094
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> ProjectSet (cost=69528.63..70539.99
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192000 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=934.720..5556.601
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> HashAggregate (cost=69528.63..69531.03
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=192 width=46) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=934.684..1554.725
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=166 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> -> Seq Scan
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">on</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> markslag_myrikilden_temp (cost=0.00..59601.91
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 width=1614) (actual
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">time</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=0.012..335.069
</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">=567241 loops=1)</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Planning Time: 0.179 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black"> Execution Time: 5629.207 ms</span></p>
<p style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">(7 </span>
<span style="font-size:8.5pt; font-family:"Monaco",serif; color:#931A68">rows</span><span style="font-size:8.5pt; font-family:"Monaco",serif; color:black">)</span></p>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Can it be problem related ST_Union and aggregate ?</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">I have tested with and with out indexes on
</span><span style="font-size:9.0pt; font-family:"Monaco",serif; color:black">atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext </span><span style="font-family:"Calibri",sans-serif; color:black">and it does not make any difference .</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Lars</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Thanks </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black">Lars</span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
<div>
<p class="x_MsoNormal" style="margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;margin: 0in 0in 0.0001pt; font-size: 12pt; font-family: "Times New Roman", serif;">
<span style="font-family:"Calibri",sans-serif; color:black"> </span></p>
</div>
</div>
</div>
</div>
</div>
</body>
</html>