Thread: varchar() vs char16 performance

varchar() vs char16 performance

From
"Thomas G. Lockhart"
Date:
I ran some timing tests to check the performance of varchar() vs char16.
The results of the test indicate that there is no difference in
performance (within the timing scatter of the tests):

char16    vc(16)
 0.99s     1.05s    1 row (this measures startup time, not types)
39.29s    39.28s    ~65000 rows

The char2,4,8,16 types seem to have no value-added over the
better-supported char(), varchar(), text types; I am considering
removing them from the backend, and instead have the parser
transparently translate the types into varchar() (or char() - I'm not
certain which is a better match for the types) for v6.4. Applications
would not have to be changed.

Comments?

                       - Tom

The test is included below:

-- create table c16 (c char16);
create table c16 (c varchar(16));

copy c16 from 'c16.copy';

select count(*) from c16 where c = 'hi there';

select count(*) from c16 where c = 'test string';

select count(*) from c16 where c != 'hi there';

select count(*) from c16 where c != 'test string';

delete from c16;

drop table c16;

Re: [HACKERS] varchar() vs char16 performance

From
Bruce Momjian
Date:
>
> I ran some timing tests to check the performance of varchar() vs char16.
> The results of the test indicate that there is no difference in
> performance (within the timing scatter of the tests):
>
> char16    vc(16)
>  0.99s     1.05s    1 row (this measures startup time, not types)
> 39.29s    39.28s    ~65000 rows
>
> The char2,4,8,16 types seem to have no value-added over the
> better-supported char(), varchar(), text types; I am considering
> removing them from the backend, and instead have the parser
> transparently translate the types into varchar() (or char() - I'm not
> certain which is a better match for the types) for v6.4. Applications
> would not have to be changed.

Fine, remove them.  You may want to keep 'char' because of the reduced
overhead compared to char(1), but the others certainly can be removed.
Seems like you have not mentioned char, so you may no intension of
removing it.

I would map to char().  They are fixed size, and the old types were
fixed size too.  I know char16 may be better for varchar(), but that is
not as clean a translation from the old type.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Re: [HACKERS] varchar() vs char16 performance

From
Hal Snyder
Date:
> Date: Wed, 11 Mar 1998 14:39:23 +0000
> From: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
...
> The char2,4,8,16 types seem to have no value-added over the
> better-supported char(), varchar(), text types; I am considering
> removing them from the backend, and instead have the parser
> transparently translate the types into varchar() (or char() - I'm not
> certain which is a better match for the types) for v6.4. Applications
> would not have to be changed.
>
> Comments?

I'm not up on the details of PostgreSQL's differing character types,
but wonder - would the proposed change break any apps where trailing
(or leading?)  whitespace is significant?  Not that I'm running any
...


Re: [HACKERS] varchar() vs char16 performance

From
Brett McCormick
Date:

n Wed, 11 March 1998, at 11:15:34, Hal Snyder wrote:

> > The char2,4,8,16 types seem to have no value-added over the
> > better-supported char(), varchar(), text types; I am considering
> > removing them from the backend, and instead have the parser
> > transparently translate the types into varchar() (or char() - I'm not
> > certain which is a better match for the types) for v6.4. Applications
> > would not have to be changed.
> >
> > Comments?
>
> I'm not up on the details of PostgreSQL's differing character types,
> but wonder - would the proposed change break any apps where trailing
> (or leading?)  whitespace is significant?  Not that I'm running any
> ...
>

Heh.. migrating to 6.3. was a surprise for me.. I certainly wasn't
expecting whitespace pads, and there are some cases where it makes a
big difference!

Re: [HACKERS] varchar() vs char16 performance

From
t-ishii@sra.co.jp
Date:
>I ran some timing tests to check the performance of varchar() vs char16.
>The results of the test indicate that there is no difference in
>performance (within the timing scatter of the tests):
>
>char16    vc(16)
> 0.99s     1.05s    1 row (this measures startup time, not types)
>39.29s    39.28s    ~65000 rows
>
>The char2,4,8,16 types seem to have no value-added over the
>better-supported char(), varchar(), text types; I am considering
>removing them from the backend, and instead have the parser
>transparently translate the types into varchar() (or char() - I'm not
>certain which is a better match for the types) for v6.4. Applications
>would not have to be changed.
>
>Comments?

Please do not remove char2! Some users uses it for making an array of
char.

create table c(c char2[]);

Seems strange? Yes. Actually what he wanted to do was:

test=> create table c(c char[]);
ERROR:  parser: parse error at or near "["
--
Tatsuo Ishii
t-ishii@sra.co.jp

Re: [HACKERS] varchar() vs char16 performance

From
Bruce Momjian
Date:
>
> >I ran some timing tests to check the performance of varchar() vs char16.
> >The results of the test indicate that there is no difference in
> >performance (within the timing scatter of the tests):
> >
> >char16    vc(16)
> > 0.99s     1.05s    1 row (this measures startup time, not types)
> >39.29s    39.28s    ~65000 rows
> >
> >The char2,4,8,16 types seem to have no value-added over the
> >better-supported char(), varchar(), text types; I am considering
> >removing them from the backend, and instead have the parser
> >transparently translate the types into varchar() (or char() - I'm not
> >certain which is a better match for the types) for v6.4. Applications
> >would not have to be changed.
> >
> >Comments?
>
> Please do not remove char2! Some users uses it for making an array of
> char.
>
> create table c(c char2[]);
>
> Seems strange? Yes. Actually what he wanted to do was:
>
> test=> create table c(c char[]);
> ERROR:  parser: parse error at or near "["

Maybe we just need to fix char[].

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Re: [HACKERS] varchar() vs char16 performance

From
Brett McCormick
Date:
hmm.. well until the grammar gets fixed, create table c(c _char)
should work, as _typename is the typename from an array of that type.
Although I don't see what advantages a character array has over text?

On Mon, 16 March 1998, at 15:20:36, t-ishii@sra.co.jp wrote:

> Please do not remove char2! Some users uses it for making an array of
> char.
>
> create table c(c char2[]);
>
> Seems strange? Yes. Actually what he wanted to do was:
>
> test=> create table c(c char[]);
> ERROR:  parser: parse error at or near "["
> --
> Tatsuo Ishii
> t-ishii@sra.co.jp

Re: [HACKERS] varchar() vs char16 performance

From
t-ishii@sra.co.jp
Date:
>hmm.. well until the grammar gets fixed, create table c(c _char)
>should work, as _typename is the typename from an array of that type.
>Although I don't see what advantages a character array has over text?

Good point. I asked him the same question. He needed a chararcter
vector in that each char represents an individual value. Rather than
using substr(), an array might be more intuitive, he said.

>On Mon, 16 March 1998, at 15:20:36, t-ishii@sra.co.jp wrote:
>
>> Please do not remove char2! Some users uses it for making an array of
>> char.
>>
>> create table c(c char2[]);
>>
>> Seems strange? Yes. Actually what he wanted to do was:
>>
>> test=> create table c(c char[]);
>> ERROR:  parser: parse error at or near "["
--
Tatsuo Ishii
t-ishii@sra.co.jp

Re: [HACKERS] varchar() vs char16 performance

From
"Thomas G. Lockhart"
Date:
> > >The char2,4,8,16 types seem to have no value-added over the
> > >better-supported char(), varchar(), text types; I am considering
> > >removing them from the backend, and instead have the parser
> > >transparently translate the types into varchar() or char()
> Maybe we just need to fix char[].

There are notes in the source code from Jolly wondering whether arrays
of char/varchar would work, and specifically disallowing it "for now".

imho, even though there is one user forcing arrays of single characters
by invoking char2, rather than using text and substrings, that is not
sufficient to keep this obsolete capability in the backend forever.

There are two ways to smoothly transition to a system in which char2-16
is not a native built-in type:

1) modify the parser to automatically translate char2-16 into
char(2-16). This will not allow arrays of char2.

2) put char2-16 into a user-loadable module, which could be configured
into the template1 database if the installer chooses. This would retain
all current char2-16 capabilities.

These options are mutually exclusive, since implementing (1) would mean
the parser would not allow user-defined types for (2).

I had thought that char2-16 add _no_ functionality over the char() and
varchar() types; Tatsuo points out at least one capability which they
have. Are there any others?

                  - Tom

Re: [HACKERS] varchar() vs char16 performance

From
t-ishii@sra.co.jp
Date:
>imho, even though there is one user forcing arrays of single characters
>by invoking char2, rather than using text and substrings, that is not
>sufficient to keep this obsolete capability in the backend forever.

I agree with you. With the suggestion from Brett, the user now have
better solution than using char2, I think.
--
Tatsuo Ishii
t-ishii@sra.co.jp