I am having a massive performance problem with foreign keys. Below is the table definition:
CREATE TABLE subjects ( id SERIAL PRIMARY KEY, type VARCHAR(3) NOT NULL, status SMALLINT NOT NULL, selectable BOOLEAN DEFAULT true, parent_id BIGINT REFRENCES subjects( id ), subject VARCHAR(255) NOT NULL, description TEXT NOT NULL, childcount BIGINT DEFAULT 0, eurekacount BIGINT DEFAULT 0, path TEXT NOT NULL UNIQUE, updated BIGINT NOT NULL );
This table needs to represent a tree of subjects. The problem is with parent_id, which refers to the primary key. This causes an update to take over 10sec. Without the constraint the operations will take <1s which is of course acceptable. This is when the row count is >450k. Update SQL:
UPDATE subjects SET parent_id = 1 WHERE id = 2;
I thought that the way to fix the problem was with an index so I created various indexes nothing helped. any input would be great.