From emilu@encs.concordia.ca Wed Oct  8 14:22:52 2014
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52303=pgsql-performance-ar@postgresql.org>)
 id 1Xbs8i-0000Mg-IW
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:22:52 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52303=pgsql-performance-ar@postgresql.org>)
 id 1Xbs8h-0002rg-9t
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:22:51 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <emilu@encs.concordia.ca>)
 id 1Xbs8g-0002rZ-4W
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:22:50 +0000
Received: from oldperseverance.encs.concordia.ca ([132.205.96.92])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <emilu@encs.concordia.ca>) id 1Xbs8c-00058s-JH
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:22:48 +0000
Received: from cacao.encs.concordia.ca (emilu@cacao.encs.concordia.ca
 [132.205.47.225])
 by oldperseverance.encs.concordia.ca (envelope-from emilu@encs.concordia.ca)
 (8.13.7/8.13.7) with ESMTP id s98EMimD002610
 for <pgsql-performance@postgresql.org>; Wed, 8 Oct 2014 10:22:45 -0400
Message-ID: <543548B4.4060700@encs.concordia.ca>
Date: Wed, 08 Oct 2014 10:22:44 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Mozilla/5.0 (X11; Linux i686 on x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.8.1
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: char(N), varchar(N), varchar, text
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Scanned-By: MIMEDefang 2.58 on oldperseverance.encs.concordia.ca at
 2014-10-08 10:22:45 EDT
X-Pg-Spam-Score: -1.2 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <small>Good morning, <br>
      <br>
      For performance point of view, are there big differences between:
      char(N), varchar(N), varchar, text? <br>
    </small>
    <p><small>Some comments from google shows: <br>
        No difference, under the hood it's all varlena. Check this
        article from Depesz: <a
href="http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/">http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/</a><br>
        A couple of highlights:</small></p>
    <small>
    </small>
    <blockquote><small> </small>
      <p><small>To sum it all up:</small></p>
      <small> </small>
      <ul>
        <li><small>char(n) &#8211; takes too much space when dealing with
            values shorter than n, and can lead to subtle errors because
            of adding trailing spaces, plus it is problematic to change
            the limit</small></li>
        <li><small>varchar(n) &#8211; it's problematic to change the limit in
            live environment</small></li>
        <li><small>varchar &#8211; just like text</small></li>
        <li><small>text &#8211; for me a winner &#8211; over (n) data types because
            it lacks their problems, and over varchar &#8211; because it has
            distinct name</small></li>
      </ul>
      <small>
        So, can I assume no big performance differences? <br>
        Thanks alot!<br>
        Emi<br>
      </small></blockquote>
  </body>
</html>




From andrew@dunslane.net Wed Oct  8 14:30:23 2014
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52304=pgsql-performance-ar@postgresql.org>)
 id 1XbsFz-0000o2-6F
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:30:23 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52304=pgsql-performance-ar@postgresql.org>)
 id 1XbsFy-0006Vo-IO
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:30:22 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <andrew@dunslane.net>) id 1XbsFx-0006VW-Nx
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:30:21 +0000
Received: from relay6-d.mail.gandi.net ([2001:4b98:c:538::198])
 by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <andrew@dunslane.net>) id 1XbsFt-00005e-NQ
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:30:20 +0000
Received: from mfilter6-d.gandi.net (mfilter6-d.gandi.net [217.70.178.135])
 by relay6-d.mail.gandi.net (Postfix) with ESMTP id 2F601FB8E9;
 Wed,  8 Oct 2014 16:30:16 +0200 (CEST)
X-Virus-Scanned: Debian amavisd-new at mfilter6-d.gandi.net
Received: from relay6-d.mail.gandi.net ([217.70.183.198])
 by mfilter6-d.gandi.net (mfilter6-d.gandi.net [10.0.15.180]) (amavisd-new,
 port 10024)
 with ESMTP id bED4uvZsidOe; Wed,  8 Oct 2014 16:30:14 +0200 (CEST)
X-Originating-IP: 98.27.58.255
Received: from [192.168.10.146] (cpe-098-027-058-255.nc.res.rr.com
 [98.27.58.255]) (Authenticated sender: adsend@dunslane.net)
 by relay6-d.mail.gandi.net (Postfix) with ESMTPSA id 59BC1FB8D3;
 Wed,  8 Oct 2014 16:30:13 +0200 (CEST)
Message-ID: <54354A73.6030607@dunslane.net>
Date: Wed, 08 Oct 2014 10:30:11 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130110 Thunderbird/17.0.2
MIME-Version: 1.0
To: emilu@encs.concordia.ca
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: char(N), varchar(N), varchar, text
References: <543548B4.4060700@encs.concordia.ca>
In-Reply-To: <543548B4.4060700@encs.concordia.ca>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


On 10/08/2014 10:22 AM, Emi Lu wrote:
> Good morning,
>
> For performance point of view, are there big differences between:=20
> char(N), varchar(N), varchar, text?
>
> Some comments from google shows:
> No difference, under the hood it's all varlena. Check this article=20
> from Depesz:=20
> http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-v=
s-text/
> A couple of highlights:
>
>     To sum it all up:
>
>       * char(n) =96 takes too much space when dealing with values
>         shorter than n, and can lead to subtle errors because of
>         adding trailing spaces, plus it is problematic to change the limit
>       * varchar(n) =96 it's problematic to change the limit in live
>         environment
>       * varchar =96 just like text
>       * text =96 for me a winner =96 over (n) data types because it lacks
>         their problems, and over varchar =96 because it has distinct name
>
>     So, can I assume no big performance differences?
>     Thanks alot!
>     Emi
>


Why do you need to ask if you already have the answer? Depesz is right.

cheers

andrew


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



From emilu@encs.concordia.ca Wed Oct  8 14:43:05 2014
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52305=pgsql-performance-ar@postgresql.org>)
 id 1XbsSH-0001PG-Lp
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:43:05 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M52305=pgsql-performance-ar@postgresql.org>)
 id 1XbsSH-00026f-69
 for pgsql-performance@arkaria.postgresql.org; Wed, 08 Oct 2014 14:43:05 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <emilu@encs.concordia.ca>)
 id 1XbsSG-00026Z-BH
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:43:04 +0000
Received: from oldperseverance.encs.concordia.ca ([132.205.96.92])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <emilu@encs.concordia.ca>) id 1XbsS9-0005Vu-2P
 for pgsql-performance@postgresql.org; Wed, 08 Oct 2014 14:43:02 +0000
Received: from cacao.encs.concordia.ca (emilu@cacao.encs.concordia.ca
 [132.205.47.225])
 by oldperseverance.encs.concordia.ca (envelope-from emilu@encs.concordia.ca)
 (8.13.7/8.13.7) with ESMTP id s98EgtZj009392;
 Wed, 8 Oct 2014 10:42:55 -0400
Message-ID: <54354D6F.1080002@encs.concordia.ca>
Date: Wed, 08 Oct 2014 10:42:55 -0400
From: Emi Lu <emilu@encs.concordia.ca>
Reply-To: emilu@encs.concordia.ca
User-Agent: Mozilla/5.0 (X11; Linux i686 on x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.8.1
MIME-Version: 1.0
To: Andrew Dunstan <andrew@dunslane.net>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: char(N), varchar(N), varchar, text
References: <543548B4.4060700@encs.concordia.ca>
 <54354A73.6030607@dunslane.net>
In-Reply-To: <54354A73.6030607@dunslane.net>
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit
X-Scanned-By: MIMEDefang 2.58 on oldperseverance.encs.concordia.ca at
 2014-10-08 10:42:55 EDT
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


>
>>
>> For performance point of view, are there big differences between: 
>> char(N), varchar(N), varchar, text?
>>
>> Some comments from google shows:
>> No difference, under the hood it's all varlena. Check this article 
>> from Depesz: 
>> http://www.depesz.com/index.php/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/
>> A couple of highlights:
>>
>>     To sum it all up:
>>
>>       * char(n) – takes too much space when dealing with values
>>         shorter than n, and can lead to subtle errors because of
>>         adding trailing spaces, plus it is problematic to change the 
>> limit
>>       * varchar(n) – it's problematic to change the limit in live
>>         environment
>>       * varchar – just like text
>>       * text – for me a winner – over (n) data types because it lacks
>>         their problems, and over varchar – because it has distinct name
>>
>>     So, can I assume no big performance differences?
>>     Thanks alot!
>>     Emi
>>
>
>
> Why do you need to ask if you already have the answer? Depesz is right.
Good to hear this. Well, sorry I saw the time is:/2010/03 (might changes 
for diff/newer versions).

Thank you for the confirmation.
Emi



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance