Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 52CE29FB521 for ; Mon, 16 Apr 2007 10:04:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 20207-01 for ; Mon, 16 Apr 2007 10:04:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from ik-out-1112.google.com (ik-out-1112.google.com [66.249.90.178]) by postgresql.org (Postfix) with ESMTP id E3BA39FA9BD for ; Mon, 16 Apr 2007 10:03:43 -0300 (ADT) Received: by ik-out-1112.google.com with SMTP id c21so1609169ika for ; Mon, 16 Apr 2007 06:03:42 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=QgFL/P+VqseSL4GiVmy8t09a2xveff4x72tNS30ZJ0++IKfMr4WcPphnek6Cq9cWo8TeYb+MHQktONzXaK7738rEOXlnoKP21ZFoAREIGbvvfh1YfmAc0MfgeRls1oGnkDuX24CkOiTz9PGfeJMQwsVklxVQpq9THxy/+wU2qGo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=PYC3jN9UliUTAqt5rW7I154LSERcilGuFd/hsKLTVV6CyL0Pyb9aXpTKcqkiVcJIc0H6Icr8MwxOjHO/a8I2K3D+okOws1JP9EcQQLQqvGg3S7+OSePVIqae3pdSc9rpSUPUes1yJuLCypd+ZLOw4G4gVQ7lo4uyRNar4zC1syM= Received: by 10.78.170.17 with SMTP id s17mr1061058hue.1176728622051; Mon, 16 Apr 2007 06:03:42 -0700 (PDT) Received: by 10.78.56.7 with HTTP; Mon, 16 Apr 2007 06:03:41 -0700 (PDT) Message-ID: Date: Mon, 16 Apr 2007 09:03:42 -0400 From: "Merlin Moncure" To: "Bill Moran" Subject: Re: Basic Q on superfluous primary keys Cc: "Kynn Jones" , pgsql-performance@postgresql.org In-Reply-To: <20070414075947.e5fbeec8.wmoran@collaborativefusion.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <20070414075947.e5fbeec8.wmoran@collaborativefusion.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200704/315 X-Sequence-Number: 24192 On 4/14/07, Bill Moran wrote: > In response to "Kynn Jones" : > > The two situations are semantically identical: each record in table bar > > refers to a record in table foo. The difference is that in the first > > schema, this referencing is done through an "artificial" serial-integer > > primary key, while in the second schema this reference is done through a > > data field that happens to be unique and not null, so it can serve as > > primary key. > > I had a discussion about this recently on the Drupal mailing lists, at the > end of which I promised to do some benchmarking to determine whether or > not text keys really do hurt performance of indexes. Unfortunately, I > still haven't followed through on that promise -- maybe I'll get to it > tomorrow. > The main reason why integer indexes are faster than natural counterparts is that the index is smaller and puts less pressure on cache. This is however offset by removing joins here and there and you usually just end up indexing the data anyways. Performance is kind of tangential to the argument though -- I've seen databases using all natural keys and found them to be very clean and performant. Using surrogate keys is dangerous and can lead to very bad design habits that are unfortunately so prevalent in the software industry they are virtually taught in schools. Many software frameworks assume you use them and refuse to work without them (avoid!) While there is nothing wrong with them in principle (you are exchanging one key for another as a performance optimization), they make it all too easy to create denormalized designs and tables with no real identifying criteria, etc, and the resultant stinky queries to put it all back together again, (full of unions, self joins, extraneous groups, case statements, etc). A good compromise in your designs is to identify your natural key but use the surrogate if you have valid performance reasons: CREATE TABLE foo ( frobnitz_id int unique, frobnitz character(varying 100) PRIMARY KEY\ [...] ); frobnitz_id is of course optional and not necessary in all tables. It may be a pain to relate a large table with a four or five part key and judicious use of surrogates may be justified for performance or even just to keep your queries smaller: create table order_line_item_discount ( company_name text, order_no int, line_item_seq_no int, discount_code text, primary key(company_name, order_no, line_item_seq_no, discount_code) ) becomes create table order_line_item_discount ( order_line_item_id int, discount_code text, primary key (order_line_item_id, discount_code) ) merlin