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

[Stefan Duling]

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)