Optimizing FK & PK performance...

From: "Sean P(dot) Thomas" <spt(at)ulanji(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizing FK & PK performance...
Date: 2003-12-11 16:38:10
Message-ID: 3FD89D72.8080508@ulanji.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I am working on migrating to postgres and had some questions regarding
optimization that I could not find references in the documentation:

1. Is there any performance difference for declaring a primary or
foreign key a column or table contraint? From the documentation, which
way is faster and/or scales better:

CREATE TABLE distributors (
did integer,
name varchar(40),
PRIMARY KEY(did)
);

CREATE TABLE distributors (
did integer PRIMARY KEY,
name varchar(40)
);

2. Is DEFERRABLE and INITIALLY IMMEDIATE or INITIALLY DEFERRABLE
perferred for performance? We generally have very small transactions
(web app) but we utilize a model of:

view (limit scope for security) -> rules -> before triggers (validate
permissions and to set proper permissions) -> tables.

I know there were some issues with deferring that was fixed but does it
benefit performance or cause any reliability issues?

Thank you for your assistance and let me know if I can offer additional
information.

--spt

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message nbarraza 2003-12-11 16:45:17 Command
Previous Message Christopher Browne 2003-12-11 16:31:45 Re: hints in Postgres?