Optimizing FK & PK 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

"Sean P. Thomas" <spt(at)ulanji(dot)com> writes:
> 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)
> );

These are equivalent -- the performance should be the same.

-Neil

> 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)
> );

No difference - they're parsed to exactly the same thing (the first
version).

> 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:

No idea on this one :/

Chris