<div dir="ltr"><div>Hi Stefan,</div><div><br></div>> I tried Postgis 2.5 on the new setup with Postgres 12.1. I can’t see any significant differences.<div><br></div><div>Please re-test with:</div><div>- "ANALYZE osm_admin_areas; " AND " jit=off max_parallel_workers_per_gather=0" parameters </div><div>based on similar problem: <a href="https://github.com/openstreetmap/osm2pgsql/issues/1045#issuecomment-570165252">https://github.com/openstreetmap/osm2pgsql/issues/1045#issuecomment-570165252</a> </div><div>And check the debugging thread: <a href="https://www.postgresql.org/message-id/flat/16183-64843a4ebc44d9d2%40postgresql.org">https://www.postgresql.org/message-id/flat/16183-64843a4ebc44d9d2%40postgresql.org</a> ( maybe there is a fix )</div><div><br></div><div>--------</div><div><br></div><div>And is it possible to upgrade to PG12.2 ( from 12.1 ) ? <br></div><div><div>- There are lot of fixes : <a href="https://www.postgresql.org/docs/release/12.2/">https://www.postgresql.org/docs/release/12.2/</a></div><div>imho: your old database: <i> "Postgres 10.12"</i> is the latest PG10 ; and it is correct to compare with the latest PG12 )</div><div></div></div><div><br></div><div>regards,</div><div> Imre</div><div><br></div><div><br></div><div><br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">Stefan Duling <<a href="mailto:stefan.duling@mapz.com">stefan.duling@mapz.com</a>> ezt írta (időpont: 2020. febr. 20., Cs, 11:36):<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">Hi Paul,<br>
<br>
thanks for your response!<br>
<br>
I tried Postgis 2.5 on the new setup with Postgres 12.1. I can’t see any significant differences.<br>
I also tried to use only one worker per gatherer. Although there is a little improvement, the query time still is doubled compared to the old setup with Postgres 10.<br>
<br>
Almost all geometry related queries of my data processing pipeline need multiple calculation times while indexing and clustering is faster with Postgres 12.<br>
<br>
I continue in trying to break it down further.<br>
<br>
Regards,<br>
<br>
Stefan<br>
<br>
—————————<br>
<br>
NEW SETUP WITH POSTGIS 2.5<br>
<br>
<br>
EXPLAIN ANALYZE CREATE TABLE public.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>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
ProjectSet (cost=40703445.82..43928566.78 rows=609088000 width=65) (actual time=51587.707..506619.624 rows=750429 loops=1)<br>
-> GroupAggregate (cost=40703445.82..40727809.34 rows=609088 width=65) (actual time=51587.663..67802.117 rows=611704 loops=1)<br>
Group Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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=40703445.82..40704968.54 rows=609088 width=65) (actual time=51586.580..57029.848 rows=708178 loops=1)<br>
Sort Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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: 4087808kB<br>
-> Gather (cost=1000.00..40638832.88 rows=609088 width=65) (actual time=1240.826..35018.389 rows=708178 loops=1)<br>
Workers Planned: 4<br>
Workers Launched: 4<br>
-> Result (cost=0.00..40576924.08 rows=152272000 width=65) (actual time=988.020..29198.984 rows=141636 loops=5)<br>
-> ProjectSet (cost=0.00..986204.08 rows=152272000 width=65) (actual time=988.012..29085.994 rows=141636 loops=5)<br>
-> Parallel Seq Scan on osm_admin_areas (cost=0.00..186014.72 rows=152272 width=6545) (actual time=0.369..5959.617 rows=122341 loops=5)<br>
Planning Time: 14.213 ms<br>
JIT:<br>
Functions: 110<br>
Options: Inlining true, Optimization true, Expressions true, Deforming true<br>
Timing: Generation 39.248 ms, Inlining 649.432 ms, Optimization 2369.206 ms, Emission 1896.395 ms, Total 4954.282 ms<br>
Execution Time: 620994.272 ms<br>
(18 rows)<br>
<br>
—————————<br>
<br>
NEW SETUP WITH POSTGIS 2.5 AND max_parallel_workers_per_gather = 1<br>
<br>
<br>
EXPLAIN ANALYZE CREATE TABLE public.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>
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
ProjectSet (cost=95352014.80..98577135.76 rows=609088000 width=65) (actual time=51995.515..493885.155 rows=750429 loops=1)<br>
-> GroupAggregate (cost=95352014.80..95376378.32 rows=609088 width=65) (actual time=51995.438..66093.453 rows=611704 loops=1)<br>
Group Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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=95352014.80..95353537.52 rows=609088 width=65) (actual time=51995.267..55931.535 rows=708178 loops=1)<br>
Sort Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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: 4087808kB<br>
-> Gather (cost=1000.00..95287401.86 rows=609088 width=65) (actual time=969.364..38077.753 rows=708178 loops=1)<br>
Workers Planned: 1<br>
Workers Launched: 1<br>
-> Result (cost=0.00..95225493.06 rows=358287000 width=65) (actual time=755.966..34508.959 rows=354089 loops=2)<br>
-> ProjectSet (cost=0.00..2070873.06 rows=358287000 width=65) (actual time=755.958..34344.231 rows=354089 loops=2)<br>
-> Parallel Seq Scan on osm_admin_areas (cost=0.00..188074.87 rows=358287 width=6545) (actual time=4.870..4186.864 rows=305854 loops=2)<br>
Planning Time: 0.310 ms<br>
JIT:<br>
Functions: 53<br>
Options: Inlining true, Optimization true, Expressions true, Deforming true<br>
Timing: Generation 10.729 ms, Inlining 235.111 ms, Optimization 789.374 ms, Emission 455.139 ms, Total 1490.353 ms<br>
Execution Time: 602556.200 ms<br>
(18 rows)<br>
<br>
<br>
<br>
> Am 19.02.2020 um 12:24 schrieb Paul Ramsey <<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>>:<br>
> <br>
> 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:<br>
> - 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)<br>
> - one of the other functions in your query has gotten a lot hotter<br>
> The external sort is the same size in both cases, so that’s not the issue.<br>
> 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.<br>
> Thanks for gathering data,<br>
> P<br>
> <br>
>> On Feb 19, 2020, at 5:37 AM, Stefan Duling <<a href="mailto:stefan.duling@mapz.com" target="_blank">stefan.duling@mapz.com</a>> wrote:<br>
>> <br>
>> Hi everyone,<br>
>> <br>
>> 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:<br>
>> <br>
>> OLD SETUP<br>
>> <br>
>> 16GB RAM<br>
>> Postgres 10.12<br>
>> Postgis 2.4<br>
>> shared_buffers = 4GB<br>
>> work_mem = 128MB<br>
>> maintenance_work_mem = 1536MB <br>
>> <br>
>> NEW SETUP<br>
>> <br>
>> 32GB RAM<br>
>> Postgres 12.1<br>
>> Postgis 3.0<br>
>> shared_buffers = 6GB<br>
>> work_mem = 256MB<br>
>> maintenance_work_mem = 3GB<br>
>> <br>
>> <br>
>> 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.<br>
>> <br>
>> 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?<br>
>> <br>
>> Thanks in advance!<br>
>> <br>
>> Stefan Duling<br>
>> <br>
>> —————————<br>
>> <br>
>> OLD SETUP<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>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
>> ProjectSet (cost=274906731.77..3540274986.77 rows=616689000000 width=65) (actual time=97024.487..141100.410 rows=739733 loops=1)<br>
>> -> GroupAggregate (cost=274906731.77..299574291.77 rows=616689000 width=65) (actual time=97024.476..118509.455 rows=611704 loops=1)<br>
>> Group Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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=274906731.77..276448454.27 rows=616689000 width=65) (actual time=97024.431..108902.529 rows=708178 loops=1)<br>
>> Sort Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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: 4087720kB<br>
>> -> Result (cost=0.00..163888355.59 rows=616689000 width=65) (actual time=42.029..84168.985 rows=708178 loops=1)<br>
>> -> ProjectSet (cost=0.00..3549215.58 rows=616689000 width=65) (actual time=42.019..83866.290 rows=708178 loops=1)<br>
>> -> 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)<br>
>> Planning time: 0.364 ms<br>
>> Execution time: 244169.613 ms<br>
>> (11 rows)<br>
>> <br>
>> —————————<br>
>> <br>
>> NEW SETUP<br>
>> <br>
>> EXPLAIN ANALYZE CREATE TABLE public.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>
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------<br>
>> ProjectSet (cost=390996247.38..409798165.99 rows=620679000 width=65) (actual time=38272.152..512018.424 rows=750429 loops=1)<br>
>> -> GroupAggregate (cost=390996247.38..391174692.59 rows=620679 width=65) (actual time=38272.119..58302.727 rows=611704 loops=1)<br>
>> Group Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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=390996247.38..390997799.08 rows=620679 width=65) (actual time=38272.021..46632.706 rows=708178 loops=1)<br>
>> Sort Key: osm_admin_areas.osm_id, <a href="http://osm_admin_areas.name" rel="noreferrer" target="_blank">osm_admin_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: 4087328kB<br>
>> -> Gather (cost=1000.00..390930320.45 rows=620679 width=65) (actual time=1035.032..23429.953 rows=708178 loops=1)<br>
>> Workers Planned: 4<br>
>> Workers Launched: 4<br>
>> -> Result (cost=0.00..390867252.55 rows=155170000 width=65) (actual time=817.294..20634.253 rows=141636 loops=5)<br>
>> -> ProjectSet (cost=0.00..1390552.55 rows=155170000 width=65) (actual time=817.287..20546.850 rows=141636 loops=5)<br>
>> -> 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)<br>
>> Planning Time: 13.173 ms<br>
>> JIT:<br>
>> Functions: 110<br>
>> Options: Inlining true, Optimization true, Expressions true, Deforming true<br>
>> Timing: Generation 27.663 ms, Inlining 580.814 ms, Optimization 2265.257 ms, Emission 1222.527 ms, Total 4096.260 ms<br>
>> Execution Time: 629042.211 ms<br>
>> (18 rows)<br>
>> _______________________________________________<br>
>> postgis-users mailing list<br>
>> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
>> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
> <br>
> _______________________________________________<br>
> postgis-users mailing list<br>
> <a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
> <a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a><br>
<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>