Skip to content
This repository has been archived by the owner on Jul 24, 2021. It is now read-only.

Missing index on relation_members table #2998

Open
openstreetmap-trac opened this issue Jul 23, 2021 · 1 comment
Open

Missing index on relation_members table #2998

openstreetmap-trac opened this issue Jul 23, 2021 · 1 comment

Comments

@openstreetmap-trac
Copy link

Reporter: lonvia
[Submitted to the original trac issue database at 4.40pm, Saturday, 22nd May 2010]

I'm running a DB with a full planet using osmosis simple psql schema
and updating it once a day. That worked fine most of the time but
occasionally I had the problem that the daily update took significantly
longer (8 hours instead of the usual 3h). Postgresql was stuck in a 'delete relation' request for hours using 100% CPU and reading a lot of data from the disc.

I searched around a bit and noticed that there is no index on the relation_id column in the relation_members table. The creation of the index seems to have been forgotten in the table creation script pgsql_simple_schema_0.6.sql. If psql is doing a sequential scan of that table for every deleted relation, that would certainly explain the symptoms I have seen.

@openstreetmap-trac
Copy link
Author

Author: brett[at]bretth.com
[Added to the original trac issue at 11.49am, Monday, 24th May 2010]

I've added a primary key to the table which should fix the issue.

ALTER TABLE ONLY relation_members ADD CONSTRAINT pk_relation_members PRIMARY KEY (relation_id, sequence_id);

The schema version is now 5. An upgrade script is provided.

pgsql_simple_schema_0.6_upgrade_4-5.sql

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Projects
None yet
Development

No branches or pull requests

1 participant