Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: nbarrazaDate: 2003-12-11 16:45:17
Subject: Command
Previous:From: Christopher BrowneDate: 2003-12-11 16:31:45
Subject: Re: hints in Postgres?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group