pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Optimizing FK & PK performance...
3+ messages / 3 participants
[nested] [flat]

* Optimizing FK & PK performance...
@ 2003-12-11 16:38  Sean P. Thomas <spt@ulanji.com>
  0 siblings, 2 replies; 3+ messages in thread

From: Sean P. Thomas @ 2003-12-11 16:38 UTC (permalink / raw)
  To: 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






^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Optimizing FK & PK performance...
@ 2003-12-16 22:47  Neil Conway <neilc@samurai.com>
  parent: Sean P. Thomas <spt@ulanji.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Neil Conway @ 2003-12-16 22:47 UTC (permalink / raw)
  To: Sean P. Thomas <spt@ulanji.com>; +Cc: pgsql-performance

"Sean P. Thomas" <spt@ulanji.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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Optimizing FK & PK performance...
@ 2003-12-17 01:17  Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  parent: Sean P. Thomas <spt@ulanji.com>
  1 sibling, 0 replies; 3+ messages in thread

From: Christopher Kings-Lynne @ 2003-12-17 01:17 UTC (permalink / raw)
  To: Sean P. Thomas <spt@ulanji.com>; +Cc: pgsql-performance

> 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




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2003-12-17 01:17 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-12-11 16:38 Optimizing FK & PK performance... Sean P. Thomas <spt@ulanji.com>
2003-12-16 22:47 ` Neil Conway <neilc@samurai.com>
2003-12-17 01:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox