[OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?

[Sarah Hoffmann]

Hi,

On Fri, Sep 13, 2019 at 01:07:39PM +0200, Michael Kussmaul wrote:
> local network: constant 17.8Mb in and 17.8Mb out. It looks like those are postgres UDP packets sent/received (according to lsof), googling it seems those are from the stats collector. Perhaps this is my problem - I will disable stats on the next run.
> 
> I then have mostly two postgres processes consuming CPU
> 19% CPU on "main: gis planet [local] SELECT" the queries on the DB
> 13% CPU on "/usr/lib/postgresql/11/bin/postgres" the binary itself
> 1-4% CPU on osm2pgsql

Please have a look what exactly the SELECT is doing. My guess would
be that you have lost an index during the upgrade to PG11 and it is
now sequentially scanning the tables.

Check for these indexes:

planet_osm_nodes : planet_osm_nodes_pkey
planet_osm_ways : planet_osm_ways_pkey, planet_osm_ways_nodes

Sarah