Results 1 to 3 of 3

Thread: varchar vs char

  1. #1
    Join Date
    Apr 2002

    Talking Unanswered: varchar vs char

    i know it is a bit primer: varchar (200) is more efficient than
    char(200) in term of space, but how about performance if i
    need to compare this field with a string?

    from postgresql site, it says:

    Tip: There are no performance differences between these three types
    (char, varchar, text), apart from the increased storage size when using the blank-padded type.

    let say my field is varchar(4000) or char(4000), and about 1 million
    records. there is no difference of performance?

    another simple question is, is there any way to alert a field's definition
    in a table, e.g., changing from char to varchar?


  2. #2
    Join Date
    Sep 2003

    Re: varchar vs char

    Interesting question. I did a comparison to see what happens in a
    real database.

    I used Postgres 7.3.2 and an Athlon wunderboxen (XP 1700 or some-such) with 1GB of RAM (all tables fit in RAM). The comparison did this:

    * Create temporary table with one column
    * Add 100,000 rows to it (in a transaction)
    * Select the last row. When there's no index, this forces a comparison
    on every row.

    I tried 3 different data types for the table's column:
    * char(200)
    * varchar(200)
    * text -- a varchar with no declared size limit (postgres extension)

    For each data type, I did the comparison both with and without an
    index (to make an index, I just declared the column to be a primary
    key). The comparions with an index were for my own curiosity and aren't needed to answer your question.

    Here are the results:

    rows = 100000
    ----- char(200) primary key
    populate: 20.78274
    select last row: 0.00025032
    ----- varchar(200) primary key
    populate: 12.293082
    select last row: 0.0002386
    ----- text primary key
    populate: 11.919549
    select last row: 0.00023299
    ----- char(200)
    populate: 12.209116
    select last row: 0.14448643
    ----- varchar(200)
    populate: 10.644892
    select last row: 0.05929275
    ----- text
    populate: 9.88626
    select last row: 0.05877966

    In general, char(200) is much slower than varchar(200), and text is a little bit faster.

    Of course, like all benchmarks, these results may or may not be valid in
    The Real World.

    By the way, I don't know of any easy way to change a column's data type. When there are no FK relationships involved, you can use "alter table" in a way similar to this (you'll need a relatively recent Postgres; the ability to rename columns hasn't always been there):

    wconrad=# create table foo (i int);
    wconrad=# insert into foo values (1);
    INSERT 5754809 1
    wconrad=# insert into foo values (2);
    INSERT 5754810 1
    wconrad=# insert into foo values (3);
    INSERT 5754811 1
    wconrad=# select * from foo;
    (3 rows)

    wconrad=# begin transaction;
    wconrad=# alter table foo rename i to old_i;
    wconrad=# alter table foo add column i float;
    wconrad=# update foo set i = old_i;
    UPDATE 3
    wconrad=# alter table foo drop column old_i;
    wconrad=# end transaction;
    wconrad=# select * from foo;
    (3 rows)

    wconrad=# \d foo
    Table ""
    Column | Type | Modifiers
    i | double precision |


  3. #3
    Join Date
    Apr 2002

    Thumbs up Re: varchar vs char

    thanks for the information. very helpful.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts