[postgis-users] AWS RDS performance

[Alexander Gataric]

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, 10:34 AM, 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, 3:31 AM, 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200225/9f21e8e6/attachment.html>