Thread: varchar() vs char16 performance
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;
> > 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)
> 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 ...
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!
>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
> > >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)
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
>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
> > >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
>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