Is loading nodes to a --flat-nodes file using lat/lon 4326 slower than 3857?

[mboeringa]

This is still a preliminary observation, but for some testing, I now used the new 'openstreetmap-carto-flex' LUA style and the --flat-nodes option of osm2pgsql.

While, if I have understood it well from previous discussions here in the repository, the loading data as 3857 is faster in general due to some shortcuts in the code, I generally assumed this would not relate to the --flat-nodes loading stage, as I assumed the major difference would likely be in processing ways and relations in the different projections.

But I now noticed a slightly higher node loading speed (7000k/s versus 5800k/s) using the new 'openstreetmap-carto-flex' style that loads data as 3857, while I routinely process the data as 4326 using my own custom flex style, which results in the lower figure.

This also raises another question: does --flat-nodes store node data always in 4326 as per the OpenStreetMap PBF, or in the style's defined projection (which might explain the difference I am seeing)?

[joto]

The flat node store stores the raw coordinates as they come from OSM before any kind of projection.

[mboeringa]

The flat node store stores the raw coordinates as they come from OSM before any kind of projection.

This is what I suspected, but makes this difference in performance a bit puzzling. When running these tests, I always fully reboot my host machine, and restart my shut-down virtual machine to ensure the conditions are as similar as possible.

Results of bench-marking Facebook Daylight over the last year using this method have proven really consistent. With my own custom style, I have also seen nearly 7000k/s node loading speed for osm2pgsql v1.10 and v1.11. Switching to v2.0.0 and now v2.0.1, I saw a very minor drop in speed to the listed +/-5800k/s, maybe due to some changes in the latest release, that was consistent for all v2.0.x release imports.

One interesting thing I now noticed though in pgAdmin, is that during the node loading stage, that pgAdmin besides the active COPY session of osm2pgsql, shows a secondary "idle" session with wait event "Client:ClientRead". However, puzzling enough, the actual SQL differs between my custom flex style (based on an adaptation by Paul Norman's variant of an openstreetmap-carto flex style), and the now official 'openstreetmap-carto-flex' LUA style you developed.

For my style, the waiting session shows a:

CREATE UNLOGGED TABLE <TABLE_NAME>...

type statement, while for your style it consistently shows:

CREATE TRIGGER "planet_osm_line_osm2pgsql_valid" BEFORE INSERT OR UPDATE ON "osm"."planet_osm_line" FOR EACH ROW EXECUTE PROCEDURE "osm"."planet_osm_line_osm2pgsql_valid"()

as the idle session SQL.

I wonder if this idle thread, and the difference in SQL, is somehow responsible for the difference in loading speed by holding onto some PostgreSQL database server resources.