pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizing FK & PK performance...
3+ messages / 3 participants
[nested] [flat]
* Optimizing FK & PK performance...
@ 2003-12-11 16:38 Sean P. Thomas <spt@ulanji.com>
2003-12-16 22:47 ` Re: Optimizing FK & PK performance... Neil Conway <neilc@samurai.com>
2003-12-17 01:17 ` Re: Optimizing FK & PK performance... Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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-11 16:38 Optimizing FK & PK performance... Sean P. Thomas <spt@ulanji.com>
@ 2003-12-16 22:47 ` Neil Conway <neilc@samurai.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-11 16:38 Optimizing FK & PK performance... Sean P. Thomas <spt@ulanji.com>
@ 2003-12-17 01:17 ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
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