Opened 9 years ago

Last modified 9 years ago

#2998 new defect

Missing index on relation_members table

Reported by: Sarah Hoffmann Owned by: Sarah Hoffmann
Priority: major Milestone:
Component: osmosis Version:
Keywords: Cc: lonvia@…

Description

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.

Change History (1)

comment:1 Changed 9 years ago by bretth

Owner: changed from bretth to Sarah Hoffmann

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

Note: See TracTickets for help on using tickets.