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

[Lars Aksel Opsahl]

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/b007f605/attachment.html>