What parameter file settings are you using? I got low CPU utilization on 11.6 with postgis 2.4.
Get BlueMail for Android On Feb 25, 2020, 3:31 AM, at 3:31 AM, Stefan Duling <stefan.dul...@mapz.com> wrote: >Sorry there is a major typor in my previous mail with the postgres >version. Each time i wrote 10.4 it has to be 10.12. Corrected version: > > > >Hello everyone, > >I just want to report the outcomes of my latest performance research. > >Postgres 12.1 and Postgres 12.2 are both restrictively slow in >performing our geometry processing queries - no matter what postgis >version (3.0.0, 2.5) I use and how the raw data came into the db. >Setting jit on or off and/or allow parallel workers and ANALYZE before >the queries hasn’t a significant effect on my test query. > >I installed Postgres 10.12 with postgis 2.4 on the same machine on a >separate cluster and it runs the queries in fraction of time. See >explain analyze output below. > >I haven’t tested the newly released postgis 3.0.1 since there is no >easy installation so far (as long as I see). >As long as I don’t see a clear way to break it down further we will >stay with postgres 10.12. > >Regards, > >Stefan > >————————— >NEW SETUP WITH POSTGRES 10.12, POSTGIS 2.4 > > >EXPLAIN ANALYZE CREATE TABLE osm_admin_lines_split AS >SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, is_in, >ST_SubDivide(ST_Collect(ST_ExteriorRing(the_geom)),5000) AS geometry >FROM (SELECT osm_id, name, admin_level, name_en, name_fr, area, iso, >is_in, (ST_Dump(geometry)).geom AS the_geom FROM osm_admin_areas) AS >foo >GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in; > QUERY PLAN >----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >ProjectSet (cost=272668581.44..3513224466.44 rows=612003000000 >width=65) (actual time=56997.276..91803.763 rows=739733 loops=1) >-> GroupAggregate (cost=272668581.44..297148701.44 rows=612003000 >width=65) (actual time=56997.263..69736.892 rows=611704 loops=1) >Group Key: osm_admin_areas.osm_id, osm_admin_areas.name, >osm_admin_areas.admin_level, osm_admin_areas.name_en, >osm_admin_areas.name_fr, osm_admin_areas.area, osm_admin_areas.iso, >osm_admin_areas.is_in >-> Sort (cost=272668581.44..274198588.94 rows=612003000 width=65) >(actual time=56997.204..61484.384 rows=708178 loops=1) >Sort Key: osm_admin_areas.osm_id, osm_admin_areas.name, >osm_admin_areas.admin_level, osm_admin_areas.name_en, >osm_admin_areas.name_fr, osm_admin_areas.area, osm_admin_areas.iso, >osm_admin_areas.is_in > Sort Method: external merge Disk: 4087800kB >-> Result (cost=0.00..162527467.79 rows=612003000 width=65) (actual >time=6.103..44923.073 rows=708178 loops=1) >-> ProjectSet (cost=0.00..3406687.79 rows=612003000 width=65) (actual >time=6.095..44633.240 rows=708178 loops=1) >-> Seq Scan on osm_admin_areas (cost=0.00..190612.03 rows=612003 >width=6983) (actual time=0.103..4156.204 rows=611707 loops=1) > Planning time: 4.031 ms > Execution time: 193917.392 ms >(11 rows) > >_______________________________________________ >postgis-users mailing list >postgis-users@lists.osgeo.org >https://lists.osgeo.org/mailman/listinfo/postgis-users
_______________________________________________ postgis-users mailing list postgis-users@lists.osgeo.org https://lists.osgeo.org/mailman/listinfo/postgis-users