[Tile-serving] [openstreetmap/osm2pgsql] Call ANALYZE on tables with ids when two-stage processing is used (PR #1675)

[mboeringa]

@joto,

I have now successfully tested this, and it now took 15 minutes to get through the "There are X ways to reprocess" stage. This means that the missing ANALYZE was indeed the main problem of this issue.

However, it also again shows, as I mentioned already in my first opened issue regarding the reprocess stage being slow, and that was directed to the "Discussion" section (https://github.com/openstreetmap/osm2pgsql/discussions/1642), that the current solution of using many consecutively issued DELETE statements with large IN clauses, may be an inefficient way to DELETE large amounts of records.

As I have shown, my hardware is capable of DELETEing 87M records from a 462M record data set of OpenStreetMap buildings in just 30 minutes using a JOIN statement to a secondary table keeping track of records to DELETE, while with the current solution in osm2pgsql, deleting just 2384321 records takes 15 minutes. 

That is a marked difference, and would mean that deleting the equivalent number of records of my tests, would take 9 hours to process with the current osm2pgsql method.

The only other reason I can think of that might explain the difference, is that most OpenStreetMap buildings are likely not TOASTed, while the ways to "reprocess" in my flex style, which are e.g. OpenStreetMap boundaries, likely are.

However, I actually have no idea if data being TOASTed actually makes any difference for the speed with which PostgreSQL can delete it. If it has no significant impact, than it is likely that the suggested solution in https://github.com/openstreetmap/osm2pgsql/discussions/1642 using a JOIN is a considerably more efficient implementation.

If it does have a significant impact on DELETE speed, than that might explain the difference.

-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/pull/1675#issuecomment-1136214174
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/pull/1675/c1136214174 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20220524/85e9d9bd/attachment.htm>