Message-ID: <4833D04E.8060403@Sheeky.Biz>
Date: Wed, 21 May 2008 17:03:34 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.12 (Macintosh/20080213)
MIME-Version: 1.0
To: Craig Ringer <craig@postnewspapers.com.au>
CC: Robins Tharakan <tharakan@gmail.com>,
 pgsql-performance@postgresql.org
Subject: Re: Varchar pkey instead of integer
References: <36af4bed0805202240v5fbdd6bcv75643a6488cd1daf@mail.gmail.com>
	<4833C285.5000603@postnewspapers.com.au>
In-Reply-To: <4833C285.5000603@postnewspapers.com.au>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Craig Ringer wrote:

>> p.s.: I am not as much concerned with the size that it'd take on the data
>> tables, as much as the fact that the select / insert performances 
>> shouldn't
>> suffer. However, if that increase in size (per data record) may make a
>> considerable impact on the performance, I would certainly want to take 
>> that
>> into account during design phase.
> 
> I suspect it's just another tradeoff - table size increase (and thus 
> scan performance cost) from storing the text vs avoiding the need to 
> access the lookup table for most operations.
> 

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)

Data size on the column could be less than half of the size of the 
varchar(10) so there will be less disk reads (the biggest slow down) and 
smaller indexes which can increase chances of caching.

Without storage overheads each million rows will have 10*1000000=10M 
bytes of data compared to 4*1000000=4M bytes - you can see that the 
chance of caching and the time to read off disk will come into effect 
each time you reference that column.


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz