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

[Jochen Topf]

I tried running osm2pgsql with your config on a planet file and it took about 10 hours. On the same machine a "normal" import without any stage-2-processing will need about 8 hours. So this is inside the range of what I would expect. Also the `DELETE FROM "public"."osm_lines" WHERE way_id IN` type queries seem to use the index as expected.

You could try to debug this using [auto_explain](https://www.postgresql.org/docs/current/auto-explain.html) to check what queries are run and whether they use the index.

You have to be a bit careful with the content of `pg_stat_activity`. This can contain queries that have been run and are not actually running any more. Add `WHERE state == 'active'` to see what PostgreSQL is actually doing at the moment. So I believe it could be some other query that is the problem.

I see you are using `--cache 32000`. That's 32 GB of cache. This is quite a lot and unless you really have a lot of RAM (>64 GB at least) this might not leave enough RAM for kernel buffers and the database. That's why we usually recommend setting `--cache 0` when a flat node file is in use.

-- 
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-808701553
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210327/9a48e38a/attachment.htm>