[postgis-users] Performance Postgres12/Postgis3 vs Postgres10/Postgis2.4

[Alexander Gataric]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200225/c518c59f/attachment.html>