[postgis-users] Performance Postgres12/Postgis3 vs Postgres10/Postgis2.4
Paul Ramsey
pramsey at cleverelephant.ca
Wed Feb 19 03:24:16 PST 2020
If you can cut this down to a smaller, shorter query that shows the same characteristics, I would like to see it in a profiler to determine if the hot spots have moved. The explain seems more or less structurally the same, which leaves a couple possibilities:
- the st_collect running in parallel is actually an antipattern, (there’s no performance benefit in the collect itself, so the only potential win is in allowing other things under the collect to go parallel)
- one of the other functions in your query has gotten a lot hotter
The external sort is the same size in both cases, so that’s not the issue.
You can probably confirm if parallelism is the problem by just turning it off in pg12 and re-running, see if things get better. set max_workers_per_gather to 1 or one of the other parallel config options.
Thanks for gathering data,
P
> On Feb 19, 2020, at 5:37 AM, Stefan Duling <stefan.duling at mapz.com> wrote:
>
> Hi everyone,
>
> at the moment I am trying out a new setup for our geo database server. Primarily I upgraded from Postgres and Postgis to newer versions. Beside of the memory both databases run on the same machine setups and contain identical data. Differences are:
>
> OLD SETUP
>
> 16GB RAM
> Postgres 10.12
> Postgis 2.4
> shared_buffers = 4GB
> work_mem = 128MB
> maintenance_work_mem = 1536MB
>
> NEW SETUP
>
> 32GB RAM
> Postgres 12.1
> Postgis 3.0
> shared_buffers = 6GB
> work_mem = 256MB
> maintenance_work_mem = 3GB
>
>
> Sadly I observe several queries that take multiple times to execute with the new setup. While the most expensive query took ~10h on the old setup, nearly 3 days are needed on the new setup. Currently I am analysing a more lightweight query, that execution time doubled with the new setup.
>
> Can someone help me with the EXPLAIN ANALYZE results? Is Postgres 12 / Postgis 3 slower in general? Is the use of parallel workers ineffective in my use case?
>
> Thanks in advance!
>
> Stefan Duling
>
> —————————
>
> OLD SETUP
>
> 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=274906731.77..3540274986.77 rows=616689000000 width=65) (actual time=97024.487..141100.410 rows=739733 loops=1)
> -> GroupAggregate (cost=274906731.77..299574291.77 rows=616689000 width=65) (actual time=97024.476..118509.455 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=274906731.77..276448454.27 rows=616689000 width=65) (actual time=97024.431..108902.529 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: 4087720kB
> -> Result (cost=0.00..163888355.59 rows=616689000 width=65) (actual time=42.029..84168.985 rows=708178 loops=1)
> -> ProjectSet (cost=0.00..3549215.58 rows=616689000 width=65) (actual time=42.019..83866.290 rows=708178 loops=1)
> -> Seq Scan on osm_admin_areas (cost=0.00..308514.89 rows=616689 width=6931) (actual time=16.288..9536.779 rows=611707 loops=1)
> Planning time: 0.364 ms
> Execution time: 244169.613 ms
> (11 rows)
>
> —————————
>
> NEW SETUP
>
> EXPLAIN ANALYZE CREATE TABLE public.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=390996247.38..409798165.99 rows=620679000 width=65) (actual time=38272.152..512018.424 rows=750429 loops=1)
> -> GroupAggregate (cost=390996247.38..391174692.59 rows=620679 width=65) (actual time=38272.119..58302.727 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=390996247.38..390997799.08 rows=620679 width=65) (actual time=38272.021..46632.706 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: 4087328kB
> -> Gather (cost=1000.00..390930320.45 rows=620679 width=65) (actual time=1035.032..23429.953 rows=708178 loops=1)
> Workers Planned: 4
> Workers Launched: 4
> -> Result (cost=0.00..390867252.55 rows=155170000 width=65) (actual time=817.294..20634.253 rows=141636 loops=5)
> -> ProjectSet (cost=0.00..1390552.55 rows=155170000 width=65) (actual time=817.287..20546.850 rows=141636 loops=5)
> -> Parallel Seq Scan on osm_admin_areas (cost=0.00..226001.70 rows=155170 width=6702) (actual time=0.381..3937.298 rows=122341 loops=5)
> Planning Time: 13.173 ms
> JIT:
> Functions: 110
> Options: Inlining true, Optimization true, Expressions true, Deforming true
> Timing: Generation 27.663 ms, Inlining 580.814 ms, Optimization 2265.257 ms, Emission 1222.527 ms, Total 4096.260 ms
> Execution Time: 629042.211 ms
> (18 rows)
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
More information about the postgis-users
mailing list