Re: Slow statement using parallelism after 9.6>11 upgrade

> We have upgraded our database from 9.6 to 11

This is now the latest PG ? PG11.5?
( for example - in PG11.5 fixed: * "Fix failure to resolve deadlocks
involving multiple parallel worker processes"*
https://www.postgresql.org/docs/current/release-11-5.html )

> populated by the osm2pgsql program and updated on a daily basis.
> What runtime setting should I change to fix this, without losing the
benefit of parallelism ?

- the osm2pgsql has an own parallelizations ... ( osm2pgsql
--number-processes .. )
so be careful to add more parallelisation to the PG side with the same
osm2pgsql parameters ! ( check the memory usages! )
- check the benchmarks and the tunnings:
https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks
- you can ask help on the : "osm dev mail list" (="dev OpenStreetMap
developer discusssion" https://lists.openstreetmap.org/listinfo/dev )

Imre

Arnaud L. <arnaud(dot)listes(at)codata(dot)eu> ezt írta (időpont: 2019. szept. 3., K,
14:11):

> Hi list
>
> We have upgraded our database from 9.6 to 11 (and updated PostGIS from
> 2.3 to 2.5 as well).
> We are using it among other things to store an OpenStreetMap database,
> populated by the osm2pgsql program and updated on a daily basis.
>
> The process used to take ~1h30 minutes before the upgrade, but after the
> upgrade it looks like it will never run to completion in a 24h time frame.
>
> The problem is apparently that the planner choses to run some statements
> in parallel, but these take a lot longer than when run without it.
> Here are the to explain analyze of the same statement, with parallelism
> on and off :
>
> Statement :
> PREPARE mark_ways_by_node(int8) AS select id from planet_osm_ways WHERE
> nodes && ARRAY[$1];
> EXPLAIN EXECUTE mark_ways_by_node(1);
>
>
> max_parallel_workers_per_gather = 2 :
>
> Gather (cost=12545.61..2357352.25 rows=1420982 width=8) (actual
> time=2.577..64.028 rows=1 loops=1)
> Workers Planned: 2
> Workers Launched: 2
> -> Parallel Bitmap Heap Scan on planet_osm_ways
> (cost=11545.61..2214254.05 rows=592076 width=8) (actual
> time=0.213..0.213 rows=0 loops=3)
> Recheck Cond: (nodes && '{1}'::bigint[])
> Heap Blocks: exact=1
> -> Bitmap Index Scan on planet_osm_ways_nodes_idx
> (cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.173..0.174
> rows=1 loops=1)
> Index Cond: (nodes && '{1}'::bigint[])
> Planning Time: 8.596 ms
> Execution Time: 64.135 ms
>
>
> max_parallel_workers_per_gather = 0 :
>
> Bitmap Heap Scan on planet_osm_ways (cost=11545.61..3462154.46
> rows=1420982 width=8) (actual time=0.677..0.679 rows=1 loops=1)
> Recheck Cond: (nodes && '{1}'::bigint[])
> Heap Blocks: exact=1
> -> Bitmap Index Scan on planet_osm_ways_nodes_idx
> (cost=0.00..11190.36 rows=1420982 width=0) (actual time=0.268..0.268
> rows=1 loops=1)
> Index Cond: (nodes && '{1}'::bigint[])
> Planning Time: 0.193 ms
> Execution Time: 0.773 ms
>
>
> So this Parallel Bitmap Heap Scan seems to be quite problematic here.
> What runtime setting should I change to fix this, without losing the
> benefit of parallelism ?
>
> Thanks a lot !
>
> Cheers
> --
> Arnaud
>
>
>