Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id 2AB176506B7
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Sat,  5 Jul 2008 13:28:09 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 66978-07 for <pgsql-admin-postgresql.org@postgresql.org>;
	Sat,  5 Jul 2008 13:28:05 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from ipmail01.adl6.internode.on.net (ipmail01.adl6.internode.on.net
	[203.16.214.146])
	by postgresql.org (Postfix) with ESMTP id 7F37265066C
	for <pgsql-admin@postgresql.org>; Sat,  5 Jul 2008 13:28:04 -0300 (ADT)
X-IronPort-Anti-Spam-Filtered: true
X-IronPort-Anti-Spam-Result: Ai8GANM7b0jLevdH/2dsb2JhbACBXKo3
X-IronPort-AV: E=Sophos;i="4.30,307,1212330600"; d="scan'208";a="142536408"
Received: from ppp247-71.static.internode.on.net (HELO DevBox.local)
	([203.122.247.71])
	by ipmail01.adl6.internode.on.net with ESMTP; 06 Jul 2008 01:58:00 +0930
Message-ID: <486FA10E.3040004@Sheeky.Biz>
Date: Sun, 06 Jul 2008 01:57:58 +0930
From: Shane Ambler <pgsql@Sheeky.Biz>
User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421)
MIME-Version: 1.0
To: Jessica Richard <rjessil@yahoo.com>
CC: pgsql-admin@postgresql.org
Subject: Re: performance cost for varchar(20), varchar(255), and text
References: <801541.60585.qm@web56406.mail.re3.yahoo.com>
In-Reply-To: <801541.60585.qm@web56406.mail.re3.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200807/74
X-Sequence-Number: 29684

Jessica Richard wrote:
> I am tuning a database created by someone else.
> 
> I noticed that some column lengths were defined longer than needed.
> 
> For example, an Id column is holding a stand length of 20 characters
> but was defined as varchar(255).
> 
> On some other columns, for example, a Description column is supposed
> to hold less than 100 characters but defined as text.
> 
> I am trying to understand the performance impact if a column is over
> defined in the following cases:
> 
> 1. char(20) vs varchar(20)
> 
> 2. varchar(20) vs varchar(255)
> 
> 3. varchar(255) vs text
> 
> 
> thanks, Jessica
> 

 From the manual -
<quote>
Tip: There are no performance differences between these three types,
apart from increased storage size when using the blank-padded type, and
a few extra cycles to check the length when storing into a
length-constrained column. While character(n) has performance advantages
in some other database systems, it has no such advantages in PostgreSQL.
In most situations text or character varying should be used instead.
</quote>

http://www.postgresql.org/docs/8.3/static/datatype-character.html


-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz