From rjessil@yahoo.com Sat Jul 5 11:19:58 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id CDBDA65003C for ; Sat, 5 Jul 2008 08:19:58 -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 91323-07 for ; Sat, 5 Jul 2008 08:19:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from web56406.mail.re3.yahoo.com (web56406.mail.re3.yahoo.com [216.252.111.85]) by postgresql.org (Postfix) with SMTP id 35D4364FD34 for ; Sat, 5 Jul 2008 08:19:54 -0300 (ADT) Received: (qmail 68012 invoked by uid 60001); 5 Jul 2008 11:19:52 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=L5tbY0CNixA24No3wKlloqsvj+tVj5x02c3ojKvNthiQ3CfBCuiJd3IMt2KBVWZDYjIpiYzD+//yLm6UVFu+dViuQSfDM9sYpmZqywoK4/LuogLsqkQYyPAq9mHbQ2X+ulaH2dsXSIl9hL/3PAvo9b4fsLnz0BxIuAbollDASOU=; Received: from [98.166.112.215] by web56406.mail.re3.yahoo.com via HTTP; Sat, 05 Jul 2008 04:19:52 PDT X-Mailer: YahooMailRC/1042.33 YahooMailWebService/0.7.199 Date: Sat, 5 Jul 2008 04:19:52 -0700 (PDT) From: Jessica Richard Subject: performance cost for varchar(20), varchar(255), and text To: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-530801136-1215256792=:60585" Message-ID: <801541.60585.qm@web56406.mail.re3.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200807/73 X-Sequence-Number: 29683 --0-530801136-1215256792=:60585 Content-Type: text/plain; charset=us-ascii 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 --0-530801136-1215256792=:60585 Content-Type: text/html; charset=us-ascii
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

--0-530801136-1215256792=:60585-- From lew@lwsc.ehost-services.com Sat Jul 5 15:10:12 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 0A629650044 for ; Sat, 5 Jul 2008 12:10:12 -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 93520-04 for ; Sat, 5 Jul 2008 12:10:11 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by postgresql.org (Postfix) with ESMTP id 3FAB764FD34 for ; Sat, 5 Jul 2008 12:10:11 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id m65FAAW8095069 for ; Sat, 5 Jul 2008 12:10:10 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id m65F4swp090736 for pgsql-admin@postgresql.org; Sat, 5 Jul 2008 12:04:54 -0300 (ADT) (envelope-from news) Date: Sat, 05 Jul 2008 11:04:53 -0400 From: Lew User-Agent: Thunderbird 2.0.0.14 (X11/20080505) MIME-Version: 1.0 X-Newsgroups: pgsql.admin 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=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Message-ID: Lines: 36 X-Usenet-Provider: http://www.giganews.com X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.39 To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200807/77 X-Sequence-Number: 29687 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 Interestingly, the Postgres documentation has the answer: > 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. Performance is not the only issue. The semantics of CHAR and VARCHAR differ. You cannot use them equivalently. I recommend searching the docs as a first approach to finding such answers. -- Lew From pgsql@Sheeky.Biz Sat Jul 5 16:28:09 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2AB176506B7 for ; 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 ; 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 ; 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 User-Agent: Thunderbird 2.0.0.14 (Macintosh/20080421) MIME-Version: 1.0 To: Jessica Richard 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 - 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. http://www.postgresql.org/docs/8.3/static/datatype-character.html -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz