A single query won’t parallelize, so you’ll usually only heat up one core. With pg12/ you should get more parallel behaviour and better utilitization of a box that size for analytical queries. P
> On Feb 25, 2020, at 9:04 AM, Alexander Gataric <gata...@usa.net> wrote: > > I've been using PostGIS on AWS with low CPU utilization. I tried 11.6 and > 10.11 and RDS size db.m5.2xlarge (8 vCPU, 32 GB). > > Any suggestions? Performance is not what I'd expect from a box with those > specs. > > Get BlueMail for Android > On Feb 25, 2020, at 10:34 AM, Alexander Gataric <gata...@usa.net> wrote: > 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, 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 > _______________________________________________ > 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