From jkanter@virginia.edu Wed Feb 11 13:07:17 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E7DCDD1D1CD for ; Wed, 11 Feb 2004 13:07:17 +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 96322-04 for ; Wed, 11 Feb 2004 09:07:00 -0400 (AST) Received: from unix.mail.Virginia.EDU (unix.mail.Virginia.EDU [128.143.2.28]) by svr1.postgresql.org (Postfix) with ESMTP id 9BFAED1DD0E for ; Wed, 11 Feb 2004 09:06:56 -0400 (AST) Received: from smtp.mail.Virginia.EDU (smtp.mail.Virginia.EDU [128.143.22.52]) by unix.mail.Virginia.EDU (Postfix) with ESMTP id DDB89A9CA for ; Wed, 11 Feb 2004 08:06:59 -0500 (EST) Received: from virginia.edu (d-137-133-128.bootp.Virginia.EDU [137.54.133.128]) by smtp.mail.Virginia.EDU (Postfix) with ESMTP id 6A8092105A for ; Wed, 11 Feb 2004 08:06:59 -0500 (EST) Message-ID: <402A2C3D.7010009@virginia.edu> Date: Wed, 11 Feb 2004 08:21:01 -0500 From: Jodi Kanter User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres Admin List Subject: constraints and performance Content-Type: multipart/alternative; boundary="------------030601000303040500060004" X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/157 X-Sequence-Number: 12331 This is a multi-part message in MIME format. --------------030601000303040500060004 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Do constraints effect performance significantly? Thanks Jodi -- /_______________________________ //Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu / / / / / --------------030601000303040500060004 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit Do constraints effect performance significantly?
Thanks
Jodi
--
________________________________

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

--------------030601000303040500060004-- From cbbrowne@acm.org Wed Feb 11 14:16:20 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 06574D1CE3E for ; Wed, 11 Feb 2004 14:16:20 +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 28343-02 for ; Wed, 11 Feb 2004 10:16:14 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 967BDD1B4D2 for ; Wed, 11 Feb 2004 10:16:10 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i1BEGA1Y008021 for ; Wed, 11 Feb 2004 14:16:10 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i1BECWMf006710 for pgsql-admin@postgresql.org; Wed, 11 Feb 2004 14:12:32 GMT From: Christopher Browne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: constraints and performance Date: Wed, 11 Feb 2004 09:06:46 -0500 Organization: cbbrowne Computing Inc Lines: 20 Message-ID: References: <402A2C3D.7010009@virginia.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org X-message-flag: Outlook is rather hackable, isn't it? X-Home-Page: http://www.cbbrowne.com/info/ X-Affero: http://svcs.affero.net/rm.php?r=cbbrowne User-Agent: Gnus/5.1006 (Gnus v5.10.6) XEmacs/21.4 (Reasonable Discussion, linux) Cancel-Lock: sha1:e9y8vJqP1tbULQCvGhvoSqEJYYs= To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/177 X-Sequence-Number: 12351 A long time ago, in a galaxy far, far away, jkanter@virginia.edu (Jodi Kanter) wrote: > Do constraints effect performance significantly? They would be expected to provide a significant enhancement to performance over: a) Firing triggers, b) Firing rules, and c) Forcing the application to validate the data, and then adding in "data validation" reports to report on the cases where a buggy application violated the constraints. So yes, they should be considerably faster than any of their alternatives. -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org'). http://cbbrowne.com/info/lsf.html "The only constructive theory connecting neuroscience and psychology will arise from the study of software." -- Alan Perlis [To the endless aggravation of both disciplines. Ed.] From scott.marlowe@ihs.com Wed Feb 11 16:08:04 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 52BE3D1D8AE for ; Wed, 11 Feb 2004 16:08:04 +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 78072-03 for ; Wed, 11 Feb 2004 12:07:48 -0400 (AST) Received: from mail1.ihs.com (mail1.ihs.com [170.207.70.222]) by svr1.postgresql.org (Postfix) with ESMTP id 8844FD1CCBE for ; Wed, 11 Feb 2004 12:07:49 -0400 (AST) Received: from css120.ihs.com (css120.ihs.com [170.207.105.120]) by mail1.ihs.com (8.12.10/8.12.10) with ESMTP id i1BG6Qc5000585; Wed, 11 Feb 2004 09:06:26 -0700 (MST) Date: Wed, 11 Feb 2004 09:01:31 -0700 (MST) From: "scott.marlowe" To: Jodi Kanter Cc: Postgres Admin List Subject: Re: constraints and performance In-Reply-To: <402A2C3D.7010009@virginia.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-IHS-MailScanner: Found to be clean X-IHS-MailScanner-SpamCheck: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/161 X-Sequence-Number: 12335 On Wed, 11 Feb 2004, Jodi Kanter wrote: > Do constraints effect performance significantly? That depends. Foreign key constraints on int4 columns or int8 columns (matching of course) are usually quite fast. FK constraints on non-int (numeric, text, etc...) tend to be slower. check constraints can be a killer if they are complex or use functions, but simple check constraints are usually alright. Do you have a specific use case you're looking at? From postgres@jal.org Sun May 30 22:39:32 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A6904D1B256 for ; Sun, 30 May 2004 19:39:32 -0300 (ADT) 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 42727-09 for ; Sun, 30 May 2004 19:39:33 -0300 (ADT) Received: from frankenbride.clueinc.net (xd84b4366.ip.e-nt.net [216.75.67.102]) by svr1.postgresql.org (Postfix) with ESMTP id B8B61D1B268 for ; Sun, 30 May 2004 19:39:29 -0300 (ADT) Received: by frankenbride.clueinc.net (Postfix, from userid 16220) id 42657BA6E; Sun, 30 May 2004 18:39:32 -0400 (EDT) Date: Sun, 30 May 2004 18:39:31 -0400 From: postgres@jal.org To: Christopher Browne Cc: pgsql-admin@postgresql.org, jkanter@virginia.edu Subject: Re: constraints and performance Message-ID: <20040530223931.GF30015@clueinc.net> Mail-Followup-To: Christopher Browne , pgsql-admin@postgresql.org, jkanter@virginia.edu References: <402A2C3D.7010009@virginia.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.3.28i Organization: clue inc X-URL: http://www.clueinc.net/ X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=NO_REAL_NAME X-Spam-Level: X-Archive-Number: 200405/317 X-Sequence-Number: 13633 On Wed, 11 Feb 2004, Christopher Browne wrote: > A long time ago, in a galaxy far, far away, jkanter@virginia.edu (Jodi Kanter) wrote: > > Do constraints effect performance significantly? > > They would be expected to provide a significant enhancement to > performance over: > a) Firing triggers, > b) Firing rules, and > c) Forcing the application to validate the data, and then adding > in "data validation" reports to report on the cases where a buggy > application violated the constraints. > > So yes, they should be considerably faster than any of their > alternatives. A completely correct answer, but not the one I suspect Jodi wanted, which was whether there was a "significant" penalty difference between using constraints on a table and not using constraints on a table. I'm not sure I have any better answer, because we don't know what "significant" means, or the nature of the constraints. I will share my experience, which is that constraints add little noticable overhead in simple cases. However, when constraining cascading deletes through many tables, for instance, it is absolutely noticable. Between those two extremes, it isn't "too bad", for me and my applications, wherein I rely heavily on constraints (and rules, and server-side triggers). I don't think it is possible to say "constraints add an n% overhead", due to the extreme variability of the way they can be used. Best practice, as Christopher notes, indicates that they should be used. It saves a lot of grief (why write the code in the application layer when you can the DB authors already have?). If you are in a situation where the difference between using them and not using them forms a critical boundary, I would suggest you have some other problems, either in design or specification. I realize that may not be helpful, given real world constraints - the consumers of applications may not be realistic in setting requirements. One thing to think about carefully, if building an application that has to scale to any real degree, is the tradeoff between client side and server side processing. While it is best-practice to keep data validation close to the data, I have been involved in some projects where scaling the DB server to the task was not economically possible; the project would not have happened if that were an enforced criteria. Messy, bad, poor practice? Yes on all counts. One must be very, very careful if one chooses to ensure data integrity client-side. Anyway, getting back to the question, the only real answer is "try it and see". As far as I know, there's no way to quantify the impact of constraints on query performance without taking the data model and usage patterns of the application into account. I hope this helps some. -j -- Jamie Lawrence jal@jal.org There is nothing more demoralizing than a small but adequate income. - Edmund Wilson