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? -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance