[Tile-serving] [openstreetmap/osm2pgsql] Problem with query to get changed parent objects (Issue #2056)

[Jochen Topf]

Version 1.9.0 changed the way we find parents of changed objects, i.e. ways that contain a changed node and relations that contain a changed node or way. We used to query the database for each changed object, which was rather wasteful. The new code does this check once for all changed nodes and once for all changed ways.

I had tested this with various changesets (including huge ones you would never have in real life) and never saw any problems. The performance was always better or at least not worse then before. For large changesets the performance could be an order of magnitude better!

Now we are seeing problems with this approach on one of the OSMF Nominatim servers. Strangely enough this only happens on one server, the others don't have this problem. We could narrow this down to the server choosing the wrong query plan involving a full table scan on the ways table. But it seems this only happens sometimes and is dependent on various PostgreSQL settings and also on the number of node ids we want to find the parents for. So the whole thing seems rather brittle and even if we get this fixed today for this server, we don't know what will happen tomorrow.

How do we fix this?

[This is the function we are talking about here](https://github.com/openstreetmap/osm2pgsql/blob/master/src/middle-pgsql.cpp#L808-L881)

Ideas:
* The `DISTINCT` clause seems to influence the planner sometimes. We can do that later. Code here: https://github.com/openstreetmap/osm2pgsql/compare/master...joto:osm2pgsql:find-parent-deps?expand=1
* Instead of asking for all nodes, we can limit the number of node ids and ask for them in a loop. But it is unclear how small to make this number. Going back to 1 node per query and we'll get the old behaviour...
* Instead of running that one query we could call a stored procedure that does the query in a loop for all node ids. For the database the query would look like in older osm2pgsql versions, one node id at a time. So there is less chance for the query planner to mess up. But we still have the advantage of having fewer osm2pgsql<->database roundtrips.
* We can play around with database settings. Disabling materialization seems to have an influence on that query. But that risks making a brittle query even more brittle.
* Do we need more configurable knobs here? Like how many node ids to work on per round? This would give the user at least a chance to fix something, if the query goes bad in their environment. On the other hand, this makes usage much harder, basically we would shift the burden to the user.

@pnorman any ideas?

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/2056
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/2056 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20230825/874cce9f/attachment.htm>