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 <[email protected]> on behalf of Martin
Davis <[email protected]>
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
<[email protected]<mailto:[email protected]>> 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.)
_______________________________________________
postgis-users mailing list
[email protected]
https://lists.osgeo.org/mailman/listinfo/postgis-users
