Message-ID: <4833D6AE.6060809@postnewspapers.com.au>
Date: Wed, 21 May 2008 16:00:46 +0800
From: Craig Ringer <craig@postnewspapers.com.au>
User-Agent: Thunderbird 2.0.0.14 (Windows/20080421)
MIME-Version: 1.0
Cc: pgsql-performance@postgresql.org
Subject: Re: Varchar pkey instead of integer
References: <36af4bed0805202240v5fbdd6bcv75643a6488cd1daf@mail.gmail.com>
	<4833C285.5000603@postnewspapers.com.au>
	<4833D04E.8060403@Sheeky.Biz>
In-Reply-To: <4833D04E.8060403@Sheeky.Biz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Shane Ambler wrote:

> Size can affect performance as much as anything else. In your case of 
> limited rows it will make little difference, though the larger table 
> with millions of rows will have this key entered for each row and be 
> indexed as the foreign key.
> 
> The real question is how you want to use the column, if you wish to 
> query for rows of a certain currency then you will notice the difference.
> 
> You could use a smallint of 2 bytes each (or a varchar(1) with an int 
> value instead of a real char) or an integer of 4 bytes, compared to your 
> varchar(10)

... and if there are only a few records in the currency column, it 
rarely changes, and you put a trigger in place to prevent the re-use of 
previously assigned keys you may be able to cache that data in your 
application.

That way you avoid a join or subquery on your lookup table to get the 
text description of the currency AND get the storage/performance of a 
small integer key.

It's something I'm doing in other places in my current DB where I have 
essentially static lookup tables. You do have to watch out for lookup 
table changes, though.

It's worth noting that my database is rather puny (the largest table has 
500,000 records) and I'm very, very far from an expert on any of this, 
so there might be some hidden downside to doing things this way that I 
just haven't hit yet.

--
Craig Ringer