Thread: Foreign Keys

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

Adam T. Gautier wrote:
> 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.

Have you run
   ANALYZE;
or
   VACUUM ANALYZE;
?

What does
   EXPLAIN ANALYZE UPDATE subjects SET parent_id = 1 WHERE id = 2;
say?

What indexes exactly have you created (definitions)?

Joe

"Adam T. Gautier" <adam_gautier@yahoo.com> writes:
> 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 );

Why is parent_id an int8, while id is only an int4? If you change the
types to match (either parent_id -> int4, or id -> bigserial), I'd bet
you'd see a lot better performance.

Cheers,

Neil

--
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC