Yes, that's exactly the test needed. So it looks like ST_Union(agg) on the 11 instance is much slower than on 9.5:
11: 25335.136 ms 9.5: 4409.919 ms The question is why... We'll have a look and see if there's any regression in GEOS (which is what is performing the union). Are you able to share the dataset from the above test? (As a file of WKT or WKB) On Thu, Apr 11, 2019 at 11:55 AM Lars Aksel Opsahl <[email protected]> wrote: > 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]> > 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
_______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
