DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.co.uk;
	h=Received:X-Mailer:Date:From:Reply-To:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=z6AD7tvVf9XyYFNH8dJxnS34RP2U4QxO6TCL6aaiNdKRPntGxUgLTj5oLrzH8em68WcR/dj54Th789gBBUIJxx0Pe3zAvlcXFWM3qyeLgcppljCUBk9RPIK3ylplpnR5jttksR4pObwhNlqAgPe+SRd6WEg7f3fhrEZfGabpQO0=;
Date: Mon, 11 Aug 2008 09:46:01 +0000 (GMT)
From: Valentin Bogdanov <valiouk@yahoo.co.uk>
Reply-To: valiouk@yahoo.co.uk
Subject: Re: Using PK value as a String
To: Jay <arrival123@gmail.com>, Gregory Stark <stark@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <874p5ruceg.fsf@oxford.xeocode.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Message-ID: <18986.60888.qm@web25807.mail.ukl.yahoo.com>

--- On Mon, 11/8/08, Gregory Stark <stark@enterprisedb.com> wrote:

> From: Gregory Stark <stark@enterprisedb.com>
> Subject: Re: [PERFORM] Using PK value as a String
> To: "Jay" <arrival123@gmail.com>
> Cc: pgsql-performance@postgresql.org
> Date: Monday, 11 August, 2008, 10:30 AM
> "Jay" <arrival123@gmail.com> writes:
>=20
> > 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.
>=20
> Username would not be any slower than UserID unless you
> have a lot of
> usernames longer than 40 characters.
>=20
> 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.
>=20
> 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...
>=20

I don't understand Gregory's suggestion about the design. I thought using n=
atural primary keys as opposed to surrogate ones is a better design strateg=
y, even when it comes to performance considerations and even more so if the=
re are complex relationships within the database.

Regards,
Valentin


> --=20
>   Gregory Stark
>   EnterpriseDB          http://www.enterprisedb.com
>   Ask me about EnterpriseDB's On-Demand Production
> Tuning
>=20
> --=20
> Sent via pgsql-performance mailing list
> (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance=0A=0A=0A      ______=
____________________________________________________=0ANot happy with your =
email address?.=0AGet the one you really want - millions of new email addre=
sses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html