<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:Wingdings;
panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Menlo;
panose-1:0 0 0 0 0 0 0 0 0 0;}
@font-face
{font-family:Monaco;
panose-1:0 0 0 0 0 0 0 0 0 0;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p
{mso-style-priority:99;
margin:0in;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:601642955;
mso-list-template-ids:-1443450592;}
@list l0:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l0:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l0:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level4
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level5
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level7
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level8
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l0:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1
{mso-list-id:1195341447;
mso-list-template-ids:-669468752;}
@list l1:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l1:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l1:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level4
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level5
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level7
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level8
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l1:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2
{mso-list-id:1428502270;
mso-list-template-ids:-1163215530;}
@list l2:level1
{mso-level-number-format:bullet;
mso-level-text:\F0B7;
mso-level-tab-stop:.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Symbol;}
@list l2:level2
{mso-level-number-format:bullet;
mso-level-text:o;
mso-level-tab-stop:1.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:"Courier New";
mso-bidi-font-family:"Times New Roman";}
@list l2:level3
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:1.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level4
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level5
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:2.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level6
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level7
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:3.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level8
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.0in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
@list l2:level9
{mso-level-number-format:bullet;
mso-level-text:\F0A7;
mso-level-tab-stop:4.5in;
mso-level-number-position:left;
text-indent:-.25in;
mso-ansi-font-size:10.0pt;
font-family:Wingdings;}
ol
{margin-bottom:0in;}
ul
{margin-bottom:0in;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>Can you try to force them to use the same plan.<o:p></o:p></span></p><p class=MsoNormal><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.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'>SET enable_hashagg=false;<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></span></p><p class=MsoNormal><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<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D'><o:p> </o:p></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=MsoNormal><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<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Hi<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif'><o:p> </o:p></span></p></div><div><p class=MsoNormal><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. <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif'><o:p> </o:p></span></p></div><div><p class=MsoNormal><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. <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif'>This is two different physical servers.<o:p></o:p></span></p></div><div><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'><span style='font-family:"Calibri",sans-serif'>Postgres 11 server (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz), is running Redhat 7.0 <o:p></o:p></span></li><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l0 level1 lfo1'><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 .<o:p></o:p></span></li></ul></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>The only index on table in the geo column <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><ul type=disc><li class=MsoNormal style='color:black;mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l1 level1 lfo2'><span style='font-family:"Calibri",sans-serif'>Sql and query plan for from Postgres 9.5<o:p></o:p></span></li></ul></div><div><p><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<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'>sl=# EXPLAIN ANALYZE<o:p></o:p></span></p><p><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 <o:p></o:p></span></p><p><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<o:p></o:p></span></p><p><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 <o:p></o:p></span></p><p><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 ;<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> QUERY PLAN <o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:#4F9192'>---------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> QUERY PLAN <o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:#4F9192'>----------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> Sort Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> Sort Method: external merge Disk: 834880kB<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> Planning <span style='color:#931A68'>time</span>: 1.942 ms<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'> Execution <span style='color:#931A68'>time</span>: 386479.469 ms<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif'>(8 <span style='color:#931A68'>rows</span>)<o:p></o:p></span></p><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><ul type=disc><li class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto;mso-list:l2 level1 lfo3'><span style='font-family:"Calibri",sans-serif;color:black'>Sql and query plan from Postgres 11</span><o:p></o:p></li></ul></div><div><p><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<o:p></o:p></span></p><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'>EXPLAIN ANALYZE<o:p></o:p></span></p><p><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 <o:p></o:p></span></p><p><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<o:p></o:p></span></p><p><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 <o:p></o:p></span></p><p><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 ;<o:p></o:p></span></p><p style='min-height:15px'><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'><o:p> </o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> QUERY PLAN <o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:#4F9192'>-------------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Planning Time: 0.163 ms<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Execution Time: 3272080.299 ms<o:p></o:p></span></p><p><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'>)<o:p></o:p></span></p><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>So on Postgres 9.5 we see groupAggregate but not on postgres 11. <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><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.<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'>EXPLAIN ANALYZE<o:p></o:p></span></p><p><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<o:p></o:p></span></p><p><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 <o:p></o:p></span></p><p><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 ;<o:p></o:p></span></p><p style='min-height:15px'><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'><o:p> </o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> QUERY PLAN <o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:#4F9192'>-------------------------------------------------------------------------------------------------------------------------------------------------------<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext<o:p></o:p></span></p><p><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)<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Planning Time: 0.179 ms<o:p></o:p></span></p><p><span style='font-size:8.5pt;font-family:"Monaco",serif;color:black'> Execution Time: 5629.207 ms<o:p></o:p></span></p><p><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'>)<o:p></o:p></span></p><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Can it be problem related ST_Union and aggregate ?<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><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 .<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Lars<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Thanks <o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'>Lars<o:p></o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div><div><p class=MsoNormal><span style='font-family:"Calibri",sans-serif;color:black'><o:p> </o:p></span></p></div></div></div></body></html>