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

[Paul Ramsey]

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 <mtnclimb at gmail.com> 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 <Lars.Opsahl at nibio.no> 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 <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> 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
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users