Why ways importing speed depends on imported nodes? Is it memory allocation bug?

[likemusic]

I'v tested import on Chicago extract (51MB) from bbbike in pbf format.

The result is above: 652 - 26 (k/s for nodes and ways).

Then I extract only ways from file ( osmconvert --drop-nodes --drop-relations --out-pbf -o=$RESULT_NAME-nodes.osm.pbf $OSM_SOURCE) and run import again and I have the results: 0 - 9 .

Why for file with nodes ways imported almost x3 faster then for file without nodes?

To exclude the possibility of influence of the pbf-file format I extracted nodes from source file (osmconvert --drop-ways --drop-relations --out-pbf -o=$RESULT_NAME-nodes.osm.pbf $OSM_SOURCE) and then merge nodes with ways (osmium cat -o $RESULT_FILE -f osm.pbf,add_metadata=false $NODES_FILE $WAYS_FILE ) and have the same results like for source file (652 - 26).

Also I've tested import source file without cache (--cache=0) and I've got ways import speed 9k/s, like for only ways files.

Its seems like ways buffer depends on nodes buffer and it not used when imported pbf-file without nodes.

At the same time, when I import full planet file, I've got ways import speed just 0.89k/s. But when I extract only ways from planet file I'v got speed 9-12 k/s for ways.

Any ideas why is this happens?

Any suggestion how to debug where is the bottleneck and how to avoid it?

My environment:

# osm2pgsql --version
2021-03-19 20:11:36  osm2pgsql version 1.4.1 (1.4.1-16-gf7993c95)
Compiled using the following library versions:
Libosmium 2.16.0
Proj [API 4] Rel. 6.3.1, February 10th, 2020
Lua 5.2.4

# sudo --user=postgres osm2pgsql --create --slim --database=gis --merc --hstore-all --style=hstore-only.style --tag-transform-script=openstreetmap-carto.lua --prefix=planet_osm_hstore --number-processes=$NumberProcesses --cache=$CacheSizeMb $OSM_EXTRACT

CacheSizeMb=16000

[joto]

OSM files without nodes don't make much sense. Without nodes, there is no geometry for the ways so there is nothing to import into the database. And then it is much faster, because there is no work to do.

[likemusic]

@joto But according to osm2pgsql output there is clustering by geometry and indexing runs after reading input file. Am I wrong?

2021-03-19 05:40:00  osm2pgsql version 1.4.1 (1.4.1-16-gf7993c95)
2021-03-19 05:40:00  Database version: 12.6 (Ubuntu 12.6-0ubuntu0.20.04.1)
2021-03-19 05:40:00  PostGIS version: 3.0
2021-03-19 05:40:00  Node-cache: cache=16000MB, maxblocks=256000*65536, allocation method=11
2021-03-19 05:40:00  Setting up table 'planet_osm_hstore_point'
2021-03-19 05:40:00  Setting up table 'planet_osm_hstore_line'
2021-03-19 05:40:00  Setting up table 'planet_osm_hstore_polygon'
2021-03-19 05:40:00  Setting up table 'planet_osm_hstore_roads'
2021-03-19 22:23:02  Reading input files done in 60182s (16h 43m 2s).
2021-03-19 22:23:02    Processed 0 nodes in 0s - 0/s
2021-03-19 22:23:02    Processed 748079580 ways in 60182s (16h 43m 2s) - 12k/s
2021-03-19 22:23:02    Processed 0 relations in 0s - 0/s
2021-03-19 22:23:02  Clustering table 'planet_osm_hstore_point' by geometry...
2021-03-19 22:23:02  Clustering table 'planet_osm_hstore_line' by geometry...
2021-03-19 22:23:02  Clustering table 'planet_osm_hstore_polygon' by geometry...
2021-03-19 22:23:02  Clustering table 'planet_osm_hstore_roads' by geometry...
2021-03-19 22:23:02  Building index on table 'planet_osm_hstore_ways'
2021-03-19 22:23:02  Building index on table 'planet_osm_hstore_rels'
2021-03-19 22:23:02  Done postprocessing on table 'planet_osm_hstore_rels' in 0s
2021-03-19 22:23:02  Creating geometry index on table 'planet_osm_hstore_line'...
2021-03-19 22:23:02  Creating geometry index on table 'planet_osm_hstore_point'...
2021-03-19 22:23:02  Creating geometry index on table 'planet_osm_hstore_roads'...
2021-03-19 22:23:02  Creating geometry index on table 'planet_osm_hstore_polygon'...
2021-03-19 22:23:02  Creating osm_id index on table 'planet_osm_hstore_line'...
2021-03-19 22:23:02  Creating osm_id index on table 'planet_osm_hstore_polygon'...
2021-03-19 22:23:02  Creating osm_id index on table 'planet_osm_hstore_point'...
2021-03-19 22:23:02  Creating osm_id index on table 'planet_osm_hstore_roads'...
2021-03-19 22:23:02  Analyzing table 'planet_osm_hstore_roads'...
2021-03-19 22:23:02  Analyzing table 'planet_osm_hstore_polygon'...
2021-03-19 22:23:02  Analyzing table 'planet_osm_hstore_line'...
2021-03-19 22:23:02  Analyzing table 'planet_osm_hstore_point'...
2021-03-19 22:23:02  All postprocessing on table 'planet_osm_hstore_point' done in 0s.
2021-03-19 22:23:02  All postprocessing on table 'planet_osm_hstore_polygon' done in 0s.
2021-03-19 22:23:02  All postprocessing on table 'planet_osm_hstore_roads' done in 0s.
2021-03-19 22:23:02  All postprocessing on table 'planet_osm_hstore_line' done in 0s.

2021-03-20 13:03:58  Done postprocessing on table 'planet_osm_hstore_ways' in 52856s (14h 40m 56s)
2021-03-20 13:03:58  node cache: stored: 0(0.00%), storage efficiency: 0.00% (dense blocks: 0, sparse nodes: 0), hit rate: 0.00%
2021-03-20 13:03:58  osm2pgsql took 113038s (31h 23m 58s) overall.

[likemusic]

By the way, is it bad idea to run import for nodes and ways with relations separately, then create 3 dumps (1- only schema; 2 - nodes data; 3 - ways with relations data) and then import that dumps into one database (to increase planet import speed)?

In that case result database would contains all necessary data and would be equal to full import database or not?

[joto]

@likemusic Please read about the OSM data model first and understand how it works. You can start, for instance, here