[postgis-users] AWS RDS performance

[Paul Ramsey]

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 <gataric at 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 <gataric at 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.duling at 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 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users