<html><head></head><body style="zoom: 0%;"><div dir="auto">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).<br><br></div>
<div dir="auto">Any suggestions? Performance is not what I'd expect from a box with those specs. <br><br></div>
<div dir="auto"><!-- tmjah_g_1299s -->Get <!-- tmjah_g_1299e --><a href="http://www.bluemail.me/r?b=15818"><!-- tmjah_g_1299s -->BlueMail for Android<!-- tmjah_g_1299e --></a><!-- tmjah_g_1299s --> <!-- tmjah_g_1299e --></div>
<div class="gmail_quote" >On Feb 25, 2020, at 10:34 AM, Alexander Gataric <<a href="mailto:gataric@usa.net" target="_blank">gataric@usa.net</a>> wrote:<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div dir="auto">
What parameter file settings are you using? I got low CPU utilization on 11.6 with postgis 2.4.
<br>
<br>
</div>
<div dir="auto">
<!-- tmjah_g_1299s -->Get
<!-- tmjah_g_1299e -->
<a href="http://www.bluemail.me/r?b=15818">
<!-- tmjah_g_1299s -->BlueMail for Android
<!-- tmjah_g_1299e --></a>
<!-- tmjah_g_1299s -->
<!-- tmjah_g_1299e -->
</div>
<div class="gmail_quote">
On Feb 25, 2020, at 3:31 AM, Stefan Duling <
<a href="mailto:stefan.duling@mapz.com" target="_blank">stefan.duling@mapz.com</a>> wrote:
<blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<pre class="blue">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:<br><br><br><br>Hello everyone,<br><br>I just want to report the outcomes of my latest performance research.<br><br>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. <br><br>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.<br><br>I haven’t tested the newly released postgis 3.0.1 since there is no easy installation so far (as long as I see).<br>As long as I don’t see a clear way to break it down further we will stay with postgres 10.12.<br><br>Regards,<br><br>Stefan<br><br>—————————<br>NEW SETUP WITH POSTGRES 10.12, POSTGIS 2.4<br><br><br>EXPLAIN ANALYZE CREATE TABLE osm_admin_lines_split AS<br>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<br>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<br>GROUP BY osm_id, name, admin_level, name_en, name_fr, area, iso, is_in;<br> QUERY PLAN<br>
<hr><br> ProjectSet (cost=272668581.44..3513224466.44 rows=612003000000 width=65) (actual time=56997.276..91803.763 rows=739733 loops=1)<br> -> GroupAggregate (cost=272668581.44..297148701.44 rows=612003000 width=65) (actual time=56997.263..69736.892 rows=611704 loops=1)<br> Group Key: osm_admin_areas.osm_id, osm_admin_<a href="http://areas.name">areas.name</a>, 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<br> -> Sort (cost=272668581.44..274198588.94 rows=612003000 width=65) (actual time=56997.204..61484.384 rows=708178 loops=1)<br> Sort Key: osm_admin_areas.osm_id, osm_admin_<a href="http://areas.name">areas.name</a>, 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<br> Sort Method: external merge Disk: 4087800kB<br> -> Result (cost=0.00..162527467.79 rows=612003000 width=65) (actual time=6.103..44923.073 rows=708178 loops=1)<br> -> ProjectSet (cost=0.00..3406687.79 rows=612003000 width=65) (actual time=6.095..44633.240 rows=708178 loops=1)<br> -> 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)<br> Planning time: 4.031 ms<br> Execution time: 193917.392 ms<br>(11 rows)<br><br>
<hr><br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
</div>
<pre class="blue"> <hr><br>postgis-users mailing list<br>postgis-users@lists.osgeo.org<br><a href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre></blockquote></div></body></html>