[Tile-serving] [openstreetmap/osm2pgsql] Something wrong with indexes during import (#1436)

[Andrey Novikov]

I've run import again with osm2pgsql build from latest source (also LuaJIT enabled) and `--cache 0`, now it was almost two times faster and no pauses during second stage:

```
2021-03-31 17:16:39  osm2pgsql version 1.4.1 (1.4.1-20-g86ad6d60)
2021-03-31 17:16:39  Database version: 11.11 (Debian 11.11-0+deb10u1)
2021-03-31 17:16:39  PostGIS version: 2.5
2021-03-31 22:19:56  Reading input files done in 18197s (5h 3m 17s).          94.4/s)
2021-03-31 22:19:56    Processed 6794233629 nodes in 1072s (17m 52s) - 6338k/s
2021-03-31 22:19:56    Processed 752633951 ways in 11231s (3h 7m 11s) - 67k/s
2021-03-31 22:19:56    Processed 8804337 relations in 5894s (1h 38m 14s) - 1k/s
2021-03-31 22:19:57  Reprocess marked ways (stage 2)...
2021-03-31 22:19:57  Creating id index on table 'osm_lines'...
2021-03-31 22:24:04  Creating id index on table 'osm_highways'...
2021-03-31 22:24:10  Creating id index on table 'osm_boundaries'...
2021-03-31 22:24:10  Creating id index on table 'osm_polygons'...
2021-03-31 22:26:26  Creating id index on table 'osm_buildings'...
2021-03-31 22:32:04  Creating id indexes took 727s (12m 7s)
2021-03-31 22:32:04  There are 147895 ways to reprocess...
2021-03-31 22:33:23  Clustering table 'osm_points' by geometry...
2021-03-31 22:45:54  Creating geometry index on table 'osm_points'...
2021-03-31 23:09:48  Creating id index on table 'osm_points'...
2021-03-31 23:10:28  Analyzing table 'osm_points'...
2021-03-31 23:10:29  All postprocessing on table 'osm_points' done in 2226s (37m 6s).
2021-03-31 23:10:33  Clustering table 'osm_lines' by geometry...
2021-03-31 23:44:10  Creating geometry index on table 'osm_lines'...
2021-04-01 00:25:36  Creating id index on table 'osm_lines'...
2021-04-01 00:26:58  Analyzing table 'osm_lines'...
2021-04-01 00:26:59  All postprocessing on table 'osm_lines' done in 4586s (1h 16m 26s).
2021-04-01 00:26:59  Clustering table 'osm_highways' by geometry...
2021-04-01 00:28:01  Creating geometry index on table 'osm_highways'...
2021-04-01 00:29:15  Creating id index on table 'osm_highways'...
2021-04-01 00:29:18  Analyzing table 'osm_highways'...
2021-04-01 00:29:18  All postprocessing on table 'osm_highways' done in 139s (2m 19s).
2021-04-01 00:29:18  Clustering table 'osm_boundaries' by geometry...
2021-04-01 00:29:22  Creating geometry index on table 'osm_boundaries'...
2021-04-01 00:29:22  Creating id index on table 'osm_boundaries'...
2021-04-01 00:29:22  Analyzing table 'osm_boundaries'...
2021-04-01 00:29:22  All postprocessing on table 'osm_boundaries' done in 4s.
2021-04-01 00:29:24  Clustering table 'osm_polygons' by geometry...
2021-04-01 01:40:48  Creating geometry index on table 'osm_polygons'...
2021-04-01 01:54:06  Creating id index on table 'osm_polygons'...
2021-04-01 01:54:27  Analyzing table 'osm_polygons'...
2021-04-01 01:54:27  All postprocessing on table 'osm_polygons' done in 5103s (1h 25m 3s).
2021-04-01 01:54:32  Clustering table 'osm_buildings' by geometry...
2021-04-01 04:08:35  Creating geometry index on table 'osm_buildings'...
2021-04-01 05:26:36  Creating id index on table 'osm_buildings'...
2021-04-01 05:29:08  Analyzing table 'osm_buildings'...
2021-04-01 05:29:09  All postprocessing on table 'osm_buildings' done in 12877s (3h 34m 37s).
2021-04-01 05:29:09  Clustering table 'osm_routes' by geometry...
2021-04-01 05:31:43  Creating geometry index on table 'osm_routes'...
2021-04-01 05:31:49  Creating id index on table 'osm_routes'...
2021-04-01 05:31:51  Analyzing table 'osm_routes'...
2021-04-01 05:31:52  All postprocessing on table 'osm_routes' done in 163s (2m 43s).
2021-04-01 05:31:52  Creating id index on table 'osm_building_outlines'...
2021-04-01 05:31:52  Analyzing table 'osm_building_outlines'...
2021-04-01 05:31:52  All postprocessing on table 'osm_building_outlines' done in 0s.
2021-04-01 05:31:52  Building index on table 'osm_ways'
2021-04-01 07:27:44  Done postprocessing on table 'osm_ways' in 6952s (1h 55m 52s)
2021-04-01 07:27:44  Building index on table 'osm_rels'
2021-04-01 07:30:16  Done postprocessing on table 'osm_rels' in 152s (2m 32s)
2021-04-01 07:30:16  osm2pgsql took 51217s (14h 13m 37s) overall.
```

As for 2nd stage `DELETE` query - it took 4 seconds. The only thing I've changed was increasing `maintenance_work_mem` to 10GB (previously it was 4GB, other settings as specified in your manual).

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1436#issuecomment-811735894
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210401/4e613c64/attachment.htm>