Foreign Keys

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

Responses

Browse pgsql-general by date

  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