To: "Jay" <arrival123@gmail.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Using PK value as a String
In-Reply-To: 
 <9e3be050-8b96-45d9-9bf3-b333bdee0405@i24g2000prf.googlegroups.com>
	(Jay's message of "Mon\, 11 Aug 2008 00\:34\:36 -0700 \(PDT\)")
User-Agent: Gnus/5.11 (Gnus v5.11) Emacs/22.1 (gnu/linux)
References: 
 <9e3be050-8b96-45d9-9bf3-b333bdee0405@i24g2000prf.googlegroups.com>
From: Gregory Stark <stark@enterprisedb.com>
Organization: EnterpriseDB
Date: Mon, 11 Aug 2008 10:30:31 +0100
Message-ID: <874p5ruceg.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Jay" <arrival123@gmail.com> 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...

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning