[Tile-serving] [osm2pgsql-dev/osm2pgsql] Is loading nodes to a --flat-nodes file using lat/lon 4326 slower than 3857? (Discussion #2272)

[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 releases.

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 resources.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2272#discussioncomment-11489087
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2272/comments/11489087 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20241206/c30ae6fd/attachment.htm>