<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<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)">
<span style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">Increased work_mem from 30MB to</span><span style=""> 1000MB , but that did not help.
</span><span style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt">Total memory on the server is around 98 GB. </span></div>
<div><font color="#000000" face="Calibri, Arial, Helvetica, sans-serif" size="3">If I run top on the server, there is 1 CPU running on 100% </font><font color="#000000" face="Calibri, Arial, Helvetica, sans-serif">and</font><font color="#000000" face="Calibri, Arial, Helvetica, sans-serif" size="3"> there
is no io-wait. </font></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt"><span style="font-family:Calibri,Arial,Helvetica,sans-serif"><br>
</span></span></div>
<div><span style="color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt"><span style="font-family:Calibri,Arial,Helvetica,sans-serif">Here is the new plan for the test 1000 MB.</span></span></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)">
<div style="margin:0px; font-size:11px; line-height:normal; font-family:Menlo; min-height:13px">
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">set work_mem TO '1000MB';</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">sl=# show work_mem ;</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> work_mem </span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">----------</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> 1000MB</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">(1 row)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_union(geo))).geom as geo</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo; min-height:13px">
<br>
<span style="font-variant-ligatures:no-common-ligatures"></span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">-------------------------------------------------------------------------------------------------------------------------------------------------------</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Result (cost=69528.63..120459.99 rows=192000 width=46) (actual time=944.712..3319935.347 rows=482885 loops=1)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> ProjectSet (cost=69528.63..70539.99 rows=192000 width=46) (actual time=944.709..3319888.367 rows=482885 loops=1)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> -> HashAggregate (cost=69528.63..69531.03 rows=192 width=46) (actual time=944.672..3316127.825 rows=166 loops=1)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; 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..59601.91 rows=567241 width=1614) (actual time=0.005..338.589 rows=567241 loops=1)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Planning Time: 0.190 ms</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> Execution Time: 3319975.319 ms</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin-top:0px; margin-bottom:0px; margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">(7 rows)</span></p>
<div><span style="font-variant-ligatures:no-common-ligatures"><br>
</span></div>
<br>
<span style="font-variant-ligatures:no-common-ligatures"></span></div>
<br>
</div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
I also increased <span style="font-family:Menlo; font-size:11px; color:rgb(0,0,0); font-variant-ligatures:no-common-ligatures">shared_buffers </span><span style="font-size:11px; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif">from 128MB to
8GB, but that not help either.</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-size:11px; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif"><br>
</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-size:11px; color:rgb(0,0,0); font-family:Calibri,Arial,Helvetica,sans-serif">
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">show shared_buffers ;</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> shared_buffers </span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">----------------</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures"> 8GB</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">(1 row)</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;margin:0px; font-size:11px; line-height:normal; font-family:Menlo">
<span style="font-variant-ligatures:no-common-ligatures">set work_mem TO '1000MB';</span></p>
<br>
</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-family:Calibri,Arial,Helvetica,sans-serif"><span title="Search for suggestions" class=""><span title="Search for suggestions" class=""><br>
</span></span></span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-family:Calibri,Arial,Helvetica,sans-serif"><span title="Search for suggestions" class=""><span title="Search for suggestions" class="">
<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 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">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">group by 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"> 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=69528.63..120459.99 rows=192000 width=46) (actual time=955.415..3328436.945 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=69528.63..70539.99 rows=192000 width=46) (actual time=955.411..3328386.283 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"> -> HashAggregate (cost=69528.63..69531.03 rows=192 width=46) (actual time=955.366..3324619.645 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"> -> Seq Scan on markslag_myrikilden_temp (cost=0.00..59601.91 rows=567241 width=1614) (actual time=0.020..105.879 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.109 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: 3328462.564 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>
<div><span style="font-variant-ligatures: no-common-ligatures"><br>
</span></div>
</span></span></span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-family:Calibri,Arial,Helvetica,sans-serif"><span title="Search for suggestions" class="">Thanks </span></span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-family:Calibri,Arial,Helvetica,sans-serif"><br>
</span></div>
<div style="font-family:Calibri,Arial,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<span style="font-family:Calibri,Arial,Helvetica,sans-serif">Lars</span></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 Raúl Marín Rodríguez <rmrodriguez@carto.com><br>
<b>Sent:</b> Thursday, April 11, 2019 12:51 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 class="BodyFragment"><font size="2"><span style="font-size:11pt">
<div class="PlainText">Hi,<br>
<br>
I'm talking from memory so I might be wrong, but I recall that<br>
Postgres 11 introduced some improvements to respect the memory limits<br>
under certain queries / plans, so one thing that could be happening is<br>
that PG 9.5 was using more memory than what work_mem actually should<br>
have allowed. I'd try increasing `work_mem` and see if that changes<br>
plans and you regain back the old performance.<br>
<br>
<br>
--<br>
Raúl Marín Rodríguez<br>
carto.com<br>
_______________________________________________<br>
postgis-users mailing list<br>
postgis-users@lists.osgeo.org<br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></div>
</span></font></div>
</div>
</body>
</html>