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

[Lars Aksel Opsahl]

Hi

Do mean something like this ?

Here is the result from postgres 11 -


EXPLAIN ANALYZE

select (ST_dump(st_union(geo))).geom as geo

from sde_markslag.markslag_myrikilden_temp

where gid  < 10000

;

                                                                    QUERY PLAN

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

 Result  (cost=62634.26..62899.53 rows=1000 width=32) (actual time=25249.956..25331.325 rows=9205 loops=1)

   ->  ProjectSet  (cost=62634.26..62639.53 rows=1000 width=32) (actual time=25249.953..25330.483 rows=9205 loops=1)

         ->  Aggregate  (cost=62634.26..62634.28 rows=1 width=32) (actual time=25244.718..25244.719 rows=1 loops=1)

               ->  Seq Scan on markslag_myrikilden_temp  (cost=0.00..62609.51 rows=9900 width=1593) (actual time=0.008..173.033 rows=9999 loops=1)

                     Filter: (gid < 10000)

                     Rows Removed by Filter: 557242

 Planning Time: 0.064 ms

 Execution Time: 25335.136 ms

(8 rows)


Here is the same postgres 9.5 -


EXPLAIN ANALYZE

[more] - > select (ST_dump(st_union(geo))).geom as geo

[more] - > from sde_markslag.markslag_myrikilden_temp

[more] - > where gid  < 10000

[more] - > ;

                                                               QUERY PLAN

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

 Aggregate  (cost=170462.27..170467.28 rows=1000 width=1604) (actual time=4311.911..4409.250 rows=9205 loops=1)

   ->  Seq Scan on markslag_myrikilden_temp  (cost=0.00..170437.51 rows=9903 width=1604) (actual time=0.054..629.045 rows=9999 loops=1)

         Filter: (gid < 10000)

         Rows Removed by Filter: 557242

 Planning time: 0.045 ms

 Execution time: 4409.919 ms

(6 rows)


Thanks

Lars

________________________________
From: postgis-users <postgis-users-bounces at lists.osgeo.org> on behalf of Martin Davis <mtnclimb at gmail.com>
Sent: Thursday, April 11, 2019 8:07 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



On Thu, Apr 11, 2019 at 3:27 AM Lars Aksel Opsahl <Lars.Opsahl at nibio.no<mailto:Lars.Opsahl at nibio.no>> wrote:
Can it be problem related ST_Union and aggregate ?

Can you test the performance of aggregate ST_Union on it's own by dropping the GROUP BY and instead using a WHERE clause to union a small-ish subset of the geometries?  (But large enough to give a measurable query time.)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190411/355f6f92/attachment.html>