[Tile-serving] [openstreetmap/osm2pgsql] osm2pgsql fails to create "osm_id" indexes on PostgreSQL 14 (Issue #1674)

[mboeringa]

## What version of osm2pgsql are you using?

1.6.0 (1.3.0-580-g06d4013)

## What operating system and PostgreSQL/PostGIS version are you using?

Ubuntu 20.04.1 LTS, PostgreSQL 14.3, PostGIS 3.2

## Tell us something about your system

HP Z840 with dual Xeon E5-2680 v4, 256 GB RAM, 4x2TB NVMe

## What did you do exactly?

Import Planet using osm2pgsql in an empty database.

## What did you expect to happen?

**osm_id** column indexes being properly created during the import process, and before the DELETE taking place in flex processing during the **"There are X ways to reprocess"** processing stage.

## What did happen instead?

Despite the index statements being visible as "idle" sessions in pgAdmin, no indexes are actually being created in PostgreSQL 14 after a successful upgrade from PostgreSQL 13 to 14. This causes a significant delay in the processing of the subsequent DELETE statements issued by osm2pgsql as part of the **"There are X ways to reprocess"** processing stage. It takes hours to process all of the DELETE statements, with consistent high disk read activity, suggesting PostgreSQL is going over the entire table time and again in a full table scan.

Although I can no longer properly verify, since I upgraded my cluster, I have never seen such issues with PostgreSQL 13, and no huge delay was experienced on previous releases of PostgreSQL (The reprocessing ways stage in PostgreSQL 13 only took between 15-30 minutes max).

## What did you do to try analyzing the problem?

Reviewed osm2pgsql output and monitored database activity in pgAdmin:

![afbeelding](https://user-images.githubusercontent.com/7635726/168461331-bc484e0d-6ce5-4721-b3f3-20e10e6511fe.png)

![afbeelding](https://user-images.githubusercontent.com/7635726/168461360-56c9faff-165c-4ce5-88ba-246e488ae249.png)

![afbeelding](https://user-images.githubusercontent.com/7635726/168461367-d59e3f22-e6ff-4902-b179-e995e84c428c.png)




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

Message ID: <openstreetmap/osm2pgsql/issues/1674 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20220515/58969d4d/attachment.htm>