X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id F3AF2D1B8B6 for ; Thu, 11 Dec 2003 16:51:44 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 72502-03 for ; Thu, 11 Dec 2003 12:51:14 -0400 (AST) Received: from dycon.com (titus.dycon.com [12.13.144.17]) by svr1.postgresql.org (Postfix) with SMTP id 5DA30D1D1F2 for ; Thu, 11 Dec 2003 12:51:13 -0400 (AST) Received: (qmail 25373 invoked by uid 1015); 11 Dec 2003 16:44:36 -0000 Received: from unknown (HELO ulanji.com) (12.13.144.25) by 0 with SMTP; 11 Dec 2003 16:44:36 -0000 Message-ID: <3FD89D72.8080508@ulanji.com> Date: Thu, 11 Dec 2003 11:38:10 -0500 From: "Sean P. Thomas" User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Optimizing FK & PK performance... Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/234 X-Sequence-Number: 5095 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