Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 41D9764FD2D for ; Tue, 12 Aug 2008 06:58:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 27371-08 for ; Tue, 12 Aug 2008 06:58:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mx.inode.at (mx02.lb01.inode.at [62.99.145.2]) by postgresql.org (Postfix) with ESMTP id 4916864FD17 for ; Tue, 12 Aug 2008 06:58:44 -0300 (ADT) Received: from [85.124.56.44] (port=13379 helo=[10.192.17.33]) by smartmx-02.inode.at with esmtp (Exim 4.50) id 1KSqeI-0002mP-6P; Tue, 12 Aug 2008 11:58:42 +0200 Message-ID: <48A15ECF.5030203@sime.com> Date: Tue, 12 Aug 2008 11:58:39 +0200 From: Mario Weilguni User-Agent: Thunderbird 2.0.0.16 (X11/20080724) MIME-Version: 1.0 To: valiouk@yahoo.co.uk CC: Jay , Gregory Stark , pgsql-performance@postgresql.org Subject: Re: Using PK value as a String References: <18986.60888.qm@web25807.mail.ukl.yahoo.com> In-Reply-To: <18986.60888.qm@web25807.mail.ukl.yahoo.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200808/115 X-Sequence-Number: 30996 Valentin Bogdanov schrieb: > --- On Mon, 11/8/08, Gregory Stark wrote: > > >> From: Gregory Stark >> Subject: Re: [PERFORM] Using PK value as a String >> To: "Jay" >> Cc: pgsql-performance@postgresql.org >> Date: Monday, 11 August, 2008, 10:30 AM >> "Jay" writes: >> >> >>> I have a table named table_Users: >>> >>> CREATE TABLE table_Users ( >>> UserID character(40) NOT NULL default >>> >> '', >> >>> Username varchar(256) NOT NULL default >>> >> '', >> >>> Email varchar(256) NOT NULL default >>> >> '' >> >>> etc... >>> ); >>> >>> The UserID is a character(40) and is generated using >>> >> UUID function. We >> >>> started making making other tables and ended up not >>> >> really using >> >>> UserID, but instead using Username as the unique >>> >> identifier for the >> >>> other tables. Now, we pass and insert the Username to >>> >> for discussions, >> >>> wikis, etc, for all the modules we have developed. I >>> >> was wondering if >> >>> it would be a performance improvement to use the 40 >>> >> Character UserID >> >>> instead of Username when querying the other tables, or >>> >> if we should >> >>> change the UserID to a serial value and use that to >>> >> query the other >> >>> tables. Or just keep the way things are because it >>> >> doesn't really make >> >>> much a difference. >>> >> Username would not be any slower than UserID unless you >> have a lot of >> usernames longer than 40 characters. >> >> However making UserID an integer would be quite a bit more >> efficient. It would >> take 4 bytes instead of as the length of the Username which >> adds up when it's >> in all your other tables... Also internationalized text >> collations are quite a >> bit more expensive than a simple integer comparison. >> >> But the real question here is what's the better design. >> If you use Username >> you'll be cursing if you ever want to provide a >> facility to allow people to >> change their usernames. You may not want such a facility >> now but one day... >> >> > > I don't understand Gregory's suggestion about the design. I thought using natural primary keys as opposed to surrogate ones is a better design strategy, even when it comes to performance considerations and even more so if there are complex relationships within the database. > > Regards, > Valentin > > UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8?