[postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

[Lars Aksel Opsahl]

Hi

Increased work_mem from 30MB to 1000MB , but that did not help. Total memory on the server is around 98 GB.
If I run top on the server, there is 1 CPU running on 100% and there is no io-wait.

Here is the new plan for the test 1000 MB.


set work_mem TO '1000MB';

SET

sl=# show work_mem   ;

 work_mem

----------

 1000MB

(1 row)


EXPLAIN ANALYZE

select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_union(geo))).geom as geo

from sde_markslag.markslag_myrikilden_temp

group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;



                                                                      QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------

 Result  (cost=69528.63..120459.99 rows=192000 width=46) (actual time=944.712..3319935.347 rows=482885 loops=1)

   ->  ProjectSet  (cost=69528.63..70539.99 rows=192000 width=46) (actual time=944.709..3319888.367 rows=482885 loops=1)

         ->  HashAggregate  (cost=69528.63..69531.03 rows=192 width=46) (actual time=944.672..3316127.825 rows=166 loops=1)

               Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext

               ->  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)

 Planning Time: 0.190 ms

 Execution Time: 3319975.319 ms

(7 rows)



I also increased  shared_buffers from 128MB to 8GB, but that not help either.


show shared_buffers ;

 shared_buffers

----------------

 8GB

(1 row)

set work_mem TO '1000MB';



EXPLAIN ANALYZE

select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_union(geo))).geom as geo

from sde_markslag.markslag_myrikilden_temp

group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;

                                                                      QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------

 Result  (cost=69528.63..120459.99 rows=192000 width=46) (actual time=955.415..3328436.945 rows=482885 loops=1)

   ->  ProjectSet  (cost=69528.63..70539.99 rows=192000 width=46) (actual time=955.411..3328386.283 rows=482885 loops=1)

         ->  HashAggregate  (cost=69528.63..69531.03 rows=192 width=46) (actual time=955.366..3324619.645 rows=166 loops=1)

               Group Key: atil, myr, myrtype, myromdanning, myrtypetext, myromdanningtext

               ->  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)

 Planning Time: 0.109 ms

 Execution Time: 3328462.564 ms

(7 rows)

Thanks

Lars

________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Raúl Marín Rodríguez <rmrodriguez at carto.com>
Sent: Thursday, April 11, 2019 12:51 PM
To: PostGIS Users Discussion
Subject: Re: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11

Hi,

I'm talking from memory so I might be wrong, but I recall that
Postgres 11 introduced some improvements to respect the memory limits
under certain queries / plans, so one thing that could be happening is
that PG 9.5 was using more memory than what work_mem actually should
have allowed. I'd try increasing `work_mem` and see if that changes
plans and you regain back the old performance.


--
Raúl Marín Rodríguez
carto.com
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190411/9a15174d/attachment.html>