something like select encode(st_asbinary(st_collect(geom)), 'hex') from mytable where id < 10000
just a nice hex-encoded wkb p On Thu, Apr 11, 2019 at 12:11 PM Martin Davis <[email protected]> wrote: > > 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 _______________________________________________ postgis-users mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/postgis-users
