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

[Andrey Novikov]

Have started import once more to show exact query (unfortunately truncated). Current log progress is:

```
2021-03-25 14:54:04  osm2pgsql version 1.4.1
2021-03-25 14:54:04  Database version: 11.10 (Debian 11.10-0+deb10u1)
2021-03-25 14:54:04  PostGIS version: 2.5
2021-03-25 14:54:04  Node-cache: cache=32000MB, maxblocks=512000*65536, allocation method=11
2021-03-25 23:28:20  Reading input files done in 30853s (8h 34m 13s).         66.9/s)
2021-03-25 23:28:20    Processed 6742003174 nodes in 2595s (43m 15s) - 2598k/s
2021-03-25 23:28:20    Processed 746437654 ways in 21363s (5h 56m 3s) - 35k/s
2021-03-25 23:28:20    Processed 8731600 relations in 6895s (1h 54m 55s) - 1k/s
2021-03-25 23:28:20  Reprocess marked ways (stage 2)...
2021-03-25 23:28:20  Creating id index on table 'osm_lines'...
2021-03-25 23:32:48  Creating id index on table 'osm_boundaries'...
2021-03-25 23:32:48  Creating id index on table 'osm_polygons'...
2021-03-25 23:40:32  Creating id indexes took 732s (12m 12s)
2021-03-25 23:40:32  There are 320513 ways to reprocess...
```

and query list is:

```
gis=# SELECT query_start, query FROM pg_stat_activity;
 2021-03-25 14:54:07.591899+03 │ PREPARE get_wkb(bigint) AS SELECT "geom" FROM "public"."osm_routes" WHERE "relation_id" = $1
 2021-03-25 23:28:17.256343+03 │ ANALYZE "osm_rels"
 2021-03-26 00:29:38.937814+03 │ SELECT query_start, query FROM pg_stat_activity;
 2021-03-26 00:13:53.168338+03 │ PREPARE get_way(int8) AS  SELECT nodes, tags    FROM osm_ways WHERE id = $1;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   ↵
                               │ PREPARE get_way_list(int8[]) AS  SELECT id, nodes, tags    FROM osm_ways      WHERE id = ANY($1::int8[]);                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      ↵
                               │ 
 2021-03-26 00:11:58.613717+03 │ DELETE FROM "public"."osm_lines" WHERE way_id IN (9104543,9104556,9104559,9104567,9104599,9104605,9104651,9104668,9104688,9104712,9104752,9104773,9104799,9104888,9104889,9104891,9104893,9104894,9104895,9104897,9104901,9104974,9104975,9104976,9104977,9104980,9104981,9104982,9104983,9104984,9105014,9105022,9105024,9105025,9105031,9105034,9105037,9105038,9105058,9105060,9105061,9105074,9105087,9105094,9105113,9105120,9105122,9105133,9105134,9105143,9105157,9105159,9105161,9208206,9208835,9209809,9210020,9211090,9211134,9211179,9211323,9212182,9212266,9212425,9213012,9213037,9214165,9214268,9214867,9406173,9406220,9406221,9406379,9406518,9406562,9406697,9406731,9406814,9406882,9406971,9407605,9407608,9407615,9407665,9407701,9407725,9408389,9408479,9408577,9408733,9408752,9408842,9408874,9408998,9409053,9409124,9409546,9409768,9409794,9409904,9409915,9409921,9409927,9409931,9410060,9410115,9410142,9410284,9410301,9410321,9410331,9410338,9410343,9410365,9410479,9410480,9410572,9410592,9410681,9410723,9410825,94109
 2021-03-25 14:54:05.632386+03 │ PREPARE get_wkb(bigint) AS SELECT "geom" FROM "public"."osm_points" WHERE "node_id" = $1
 2021-03-26 00:13:53.168564+03 │ PREPARE get_wkb(bigint) AS SELECT "geom" FROM "public"."osm_lines" WHERE "way_id" = $1
 2021-03-26 00:13:53.186673+03 │ PREPARE get_wkb(bigint) AS SELECT "geom" FROM "public"."osm_boundaries" WHERE "way_id" = $1
 2021-03-26 00:13:53.186819+03 │ PREPARE get_wkb(bigint) AS SELECT "geom" FROM "public"."osm_polygons" WHERE "area_id" = $1
```

-- 
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-807536448
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210325/4e5f8f2f/attachment.htm>