From: | "Adam T(dot) Gautier" <adam_gautier(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Foreign Keys |
Date: | 2002-09-25 00:09:30 |
Message-ID: | 1032912571.13727.109.camel@tortola.eurekafarms.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
Thanks
adam
From | Date | Subject | |
---|---|---|---|
Next Message | Adahma | 2002-09-25 00:46:47 | Error at startup |
Previous Message | Neil Conway | 2002-09-24 22:37:40 | Re: Postmaster Uptime |