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

Lars Aksel Opsahl Lars.Opsahl at nibio.no
Thu Apr 11 09:57:02 PDT 2019


Hi

Yes then it switched GroupAggregate, but it stil used around 1 hour.


sl=# SET enable_hashagg=false;

SET

sl=# EXPLAIN ANALYZE

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

sl-# from sde_markslag.markslag_myrikilden_temp

sl-# group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;




                                                                               QUERY PLAN

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

 Result  (cost=0.42..173072.99 rows=192000 width=46) (actual time=4574.289..3348175.809 rows=482885 loops=1)

   ->  ProjectSet  (cost=0.42..123152.99 rows=192000 width=46) (actual time=4574.285..3348126.192 rows=482885 loops=1)

         ->  GroupAggregate  (cost=0.42..122144.03 rows=192 width=46) (actual time=4572.051..3344371.117 rows=166 loops=1)

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

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

 Planning Time: 0.255 ms

 Execution Time: 3348188.751 ms

(7 rows)


Thanks.

Lars

________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Regina Obe <lr at pcorp.us>
Sent: Thursday, April 11, 2019 4:42 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


Can you try to force them to use the same plan.

I vaguely recall running into this issue with HashAggregate or GroupAggregate misbehaving but can't recall the exact fix.



At anyrate to check if that's the issue on your PostgreSQL 11, try doing



SET enable_hashagg=false;



And then rerun your query – that should prevent it from using hashagg and presumably would force it to use groupagg



From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Lars Aksel Opsahl
Sent: Thursday, April 11, 2019 6:27 AM
To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Subject: [postgis-users] diffrent execution plan on Postgres 9.5 and Postgres 11 for ST_union and performance problem Postgres 11



Hi



We are upgrading from Postgres 9.5 to Postgres 11 and have a problem with ST_Union query.



It used around 5 minutts on Postgres 9.5 but on the Postgres 11 it takes around a hour.



This is two different physical servers.

  *   Postgres 11 server (Intel(R) Xeon(R) Gold 6126 CPU @ 2.60GHz), is running   Redhat 7.0
  *   Postgres 9.5 server ( Intel(R) Xeon(R) CPU E5-2667 v2 @ 3.30GHz) is running Cent 6.9 .



The only index on table in the geo column



  *   Sql and query plan for from Postgres 9.5

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





sl=# EXPLAIN ANALYZE

sl-#     select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext

sl-#     , st_union(geo) as geo

sl-#     from sde_markslag.markslag_myrikilden_temp

sl-#     group by atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext ;

                                                                    QUERY PLAN

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

                                                                     QUERY PLAN

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

 GroupAggregate  (cost=796867.85..1683469.04 rows=170173000 width=1668) (actual time=20548.994..386236.175 rows=482885 loops=1)

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

   ->  Sort  (cost=796867.85..801122.16 rows=1701723 width=1668) (actual time=19215.245..28191.175 rows=567241 loops=1)

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

         Sort Method: external merge  Disk: 834880kB

         ->  Seq Scan on markslag_myrikilden_temp  (cost=0.00..180364.23 rows=1701723 width=1668) (actual time=0.115..2625.401 rows=567241 loops=1)

 Planning time: 1.942 ms

 Execution time: 386479.469 ms

(8 rows)





  *   Sql and query plan from Postgres 11

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



EXPLAIN ANALYZE

    select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext

    , st_union(geo) 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=962.266..3272057.295 rows=482885 loops=1)

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

         ->  HashAggregate  (cost=69528.63..69531.03 rows=192 width=46) (actual time=962.214..3268248.140 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.012..336.203 rows=567241 loops=1)

 Planning Time: 0.163 ms

 Execution Time: 3272080.299 ms

(7 rows)





So on Postgres 9.5 we see groupAggregate but not on postgres 11.



If I on postgres 11 tests with ST_collect it's very fast, bu we can not use that result.



EXPLAIN ANALYZE

select atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext , (ST_dump(st_Collect(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=934.723..5611.094 rows=567241 loops=1)

   ->  ProjectSet  (cost=69528.63..70539.99 rows=192000 width=46) (actual time=934.720..5556.601 rows=567241 loops=1)

         ->  HashAggregate  (cost=69528.63..69531.03 rows=192 width=46) (actual time=934.684..1554.725 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.012..335.069 rows=567241 loops=1)

 Planning Time: 0.179 ms

 Execution Time: 5629.207 ms

(7 rows)





Can it be problem related ST_Union and aggregate ?



I have tested with and with out indexes on atil, myr, myrtype,myromdanning,myrtypetext,myromdanningtext and it does not make any difference .



Lars





Thanks



Lars












-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190411/4a6bf4a2/attachment.html>


More information about the postgis-users mailing list