Обсуждение: Which is faster: char(14) or varchar(14)
I think I already know the answer (char(14)), but I would like to confirm: which is faster? In Brazil, company id has 14 digits (12 identifiers, 2 control digits). By today, application use varchar(14) for these, but I intend to optimize insert/update/delete and search, and I'm considering to change it to char(14). Will it give ANY gain? I do use equality and like operators for search. Regards, Edson
On Tue, Dec 4, 2012 at 11:44:20AM -0200, Edson Richter wrote: > I think I already know the answer (char(14)), but I would like to > confirm: which is faster? > > In Brazil, company id has 14 digits (12 identifiers, 2 control > digits). By today, application use varchar(14) for these, but I > intend to optimize insert/update/delete and search, and I'm > considering to change it to char(14). > > Will it give ANY gain? I do use equality and like operators for search. You need to test it but I doubt there would be any measurable difference. If it will always be 14, I would use char(14), and perhaps use a CHECK constraint to make sure it is always 14 with spaces. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
Em 04/12/2012 11:50, Pavel Stehule escreveu: > Hello > > 2012/12/4 Edson Richter <edsonrichter@hotmail.com>: >> I think I already know the answer (char(14)), but I would like to confirm: >> which is faster? >> >> In Brazil, company id has 14 digits (12 identifiers, 2 control digits). By >> today, application use varchar(14) for these, but I intend to optimize >> insert/update/delete and search, and I'm considering to change it to >> char(14). >> >> Will it give ANY gain? I do use equality and like operators for search. >> > There are no big differences between char and varchar - char can be > little bit slower, because empty chars to limit are filled by space. > So usually varchar is more effective (in PostgreSQL). In this specific case, the full length (14) is mandatory... so seems there is no loss or gain. Also, I see all varchar(...) created are by default "storage = EXTENDED" (from "Pg Admin"), while other datatypes (like numeric, smallint, integer) are "storage = MAIN". Can I have a gain using fixed length datatype in place of current varchar (like "numeric (14,0)")? Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? Sorry if there are many questions in one, but I'm in a brainstorm... Thanks, Edson > > Regards > > Pavel Stehule > > >> Regards, >> >> Edson >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >
Edson Richter wrote:
> Also, I see all varchar(...) created are by default "storage =
> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric,
> smallint, integer) are "storage = MAIN".
That's unlikely to matter on a 14 character value.
> Can I have a gain using fixed length datatype in place of
> current varchar (like "numeric (14,0)")?
> Or changing to "char(14) check length(doc)=14" and
> "storage=MAIN"?
In PostgreSQL char(n) is never, ever, under any circumstances
faster than varchar(n) to store or retrieve. char(n) is stored
exactly the same as varchar(n) except that before storing the
length is checked and spaces are added if necessary to fill it out
to the maximum length, and when comparing spaces are stripped
before using the value in comparisons to other strings. The
semantics of char(n) are confusing and very odd. Personally, I
recommend never, ever using char(n).
PostgreSQL provides a function to check the storage length in bytes
for various types of objects (although some of them might be
compressed or stored out of line under some circumstances).
test=# select pg_column_size('12345678901234'::char(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('1'::char(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('12345678901234'::varchar(14));
pg_column_size
----------------
18
(1 row)
test=# select pg_column_size('1'::varchar(14));
pg_column_size
----------------
5
(1 row)
test=# select pg_column_size('12345678901234'::numeric(14,0));
pg_column_size
----------------
14
(1 row)
test=# select pg_column_size('1'::numeric(14,0));
pg_column_size
----------------
8
(1 row)
test=# select pg_column_size('12345678901234'::bigint);
pg_column_size
----------------
8
(1 row)
If your value is always 14 numeric digits, bigint would save space
and generally be faster than varcher(14).
-Kevin
Em 04/12/2012 12:53, Kevin Grittner escreveu:
> Edson Richter wrote:
>
>> Also, I see all varchar(...) created are by default "storage =
>> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric,
>> smallint, integer) are "storage = MAIN".
> That's unlikely to matter on a 14 character value.
>
>> Can I have a gain using fixed length datatype in place of
>> current varchar (like "numeric (14,0)")?
>> Or changing to "char(14) check length(doc)=14" and
>> "storage=MAIN"?
> In PostgreSQL char(n) is never, ever, under any circumstances
> faster than varchar(n) to store or retrieve. char(n) is stored
> exactly the same as varchar(n) except that before storing the
> length is checked and spaces are added if necessary to fill it out
> to the maximum length, and when comparing spaces are stripped
> before using the value in comparisons to other strings. The
> semantics of char(n) are confusing and very odd. Personally, I
> recommend never, ever using char(n).
>
> PostgreSQL provides a function to check the storage length in bytes
> for various types of objects (although some of them might be
> compressed or stored out of line under some circumstances).
>
> test=# select pg_column_size('12345678901234'::char(14));
> pg_column_size
> ----------------
> 18
> (1 row)
>
> test=# select pg_column_size('1'::char(14));
> pg_column_size
> ----------------
> 18
> (1 row)
>
> test=# select pg_column_size('12345678901234'::varchar(14));
> pg_column_size
> ----------------
> 18
> (1 row)
>
> test=# select pg_column_size('1'::varchar(14));
> pg_column_size
> ----------------
> 5
> (1 row)
>
> test=# select pg_column_size('12345678901234'::numeric(14,0));
> pg_column_size
> ----------------
> 14
> (1 row)
>
> test=# select pg_column_size('1'::numeric(14,0));
> pg_column_size
> ----------------
> 8
> (1 row)
>
> test=# select pg_column_size('12345678901234'::bigint);
> pg_column_size
> ----------------
> 8
> (1 row)
>
> If your value is always 14 numeric digits, bigint would save space
> and generally be faster than varcher(14).
Thanks, I've learned a lot.
Now, I'll make my home work.
Regards,
Edson
>
> -Kevin
>
>
Edson Richter <edsonrichter@hotmail.com> writes: > In this specific case, the full length (14) is mandatory... so seems > there is no loss or gain. > Also, I see all varchar(...) created are by default "storage = > EXTENDED" (from "Pg Admin"), while other datatypes (like numeric, > smallint, integer) are "storage = MAIN". > Can I have a gain using fixed length datatype in place of current > varchar (like "numeric (14,0)")? > Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? Sounds like premature optimization to me. I'd first express what I want as clear as possible, e.g. "CREATE DOMAIN BrazilianCompanyId AS char(14)", and try to spot and fix performance problems when I'm done with all that.
Em 04/12/2012 14:59, hari.fuchs@gmail.com escreveu: > Edson Richter <edsonrichter@hotmail.com> writes: > >> In this specific case, the full length (14) is mandatory... so seems >> there is no loss or gain. >> Also, I see all varchar(...) created are by default "storage = >> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric, >> smallint, integer) are "storage = MAIN". >> Can I have a gain using fixed length datatype in place of current >> varchar (like "numeric (14,0)")? >> Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? > Sounds like premature optimization to me. I'd first express what I want > as clear as possible, e.g. "CREATE DOMAIN BrazilianCompanyId AS char(14)", > and try to spot and fix performance problems when I'm done with all that. > > > Actually, I already stressed performance over these fields (query optimization, indexing, reverse indexing, full text index inside PostgreSQL and outside PostgreSQL, etc). At current stage, I'm just looking for finetuning. Maybe storage is one possibility. It's already established database that I can't make big changes (even changing from varchar to decimal or bigint would not be possible because of leading zeroes). Thanks for all that provided hints! I've learned a lot with you all. Regards, Edson
On 05/12/12 06:06, Edson Richter wrote:
> Em 04/12/2012 14:59, hari.fuchs@gmail.com escreveu:
>> Edson Richter <edsonrichter@hotmail.com> writes:
>>
>>> In this specific case, the full length (14) is mandatory... so seems
>>> there is no loss or gain.
>>> Also, I see all varchar(...) created are by default "storage =
>>> EXTENDED" (from "Pg Admin"), while other datatypes (like numeric,
>>> smallint, integer) are "storage = MAIN".
>>> Can I have a gain using fixed length datatype in place of current
>>> varchar (like "numeric (14,0)")?
>>> Or changing to "char(14) check length(doc)=14" and "storage=MAIN"?
>> Sounds like premature optimization to me. I'd first express what I want
>> as clear as possible, e.g. "CREATE DOMAIN BrazilianCompanyId AS
>> char(14)",
>> and try to spot and fix performance problems when I'm done with all
>> that.
>>
>>
>>
> Actually, I already stressed performance over these fields (query
> optimization, indexing, reverse indexing, full text index inside
> PostgreSQL and outside PostgreSQL, etc).
> At current stage, I'm just looking for finetuning. Maybe storage is
> one possibility.
> It's already established database that I can't make big changes (even
> changing from varchar to decimal or bigint would not be possible
> because of leading zeroes).
>
> Thanks for all that provided hints! I've learned a lot with you all.
>
> Regards,
>
> Edson
>
>
If your number is always the same length, you don't need to store the
zeros in the database, so you can use bigint! You can add the leading
zeros when you display to the user.
More specifically, you could add leading zeros in the SQL you use to
extract the value from the database.
N.B. lpad(*) truncates values larger than the field size!
For example:
DROP TABLE IF EXISTS tabzer;
CREATE TABLE tabzer
(
id SERIAL PRIMARY KEY,
payload bigint
);
INSERT INTO tabzer (payload)
VALUES
(123),
(1234567890),
(1234567890123456),
(12345678901234567) ;
TABLE tabzer;
SELECT
lpad(t.payload::text, 16, '0')
FROM
tabzer t
/**/;/**/
On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter <edsonrichter@hotmail.com> wrote: > In this specific case, the full length (14) is mandatory... so seems there > is no loss or gain. > Also, I see all varchar(...) created are by default "storage = EXTENDED" > (from "Pg Admin"), while other datatypes (like numeric, smallint, integer) > are "storage = MAIN". > Can I have a gain using fixed length datatype in place of current varchar > (like "numeric (14,0)")? > Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? May be I am late with my reply but I would also recommend to take into consideration the article from depesz where he explains and tests all the textual types http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. Very useful one. -- Sergey Konoplev Database and Software Architect http://www.linkedin.com/in/grayhemp Phones: USA +1 415 867 9984 Russia, Moscow +7 901 903 0499 Russia, Krasnodar +7 988 888 1979 Skype: gray-hemp Jabber: gray.ru@gmail.com
Em 04/12/2012 18:49, Sergey Konoplev escreveu: > On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter <edsonrichter@hotmail.com> wrote: >> In this specific case, the full length (14) is mandatory... so seems there >> is no loss or gain. >> Also, I see all varchar(...) created are by default "storage = EXTENDED" >> (from "Pg Admin"), while other datatypes (like numeric, smallint, integer) >> are "storage = MAIN". >> Can I have a gain using fixed length datatype in place of current varchar >> (like "numeric (14,0)")? >> Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? > May be I am late with my reply but I would also recommend to take into > consideration the article from depesz where he explains and tests all > the textual types > http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. > > Very useful one. Yes, good education as well. I've read, and I'll consider the different datatypes and more domain use in future. But I don't see much changes in our schema, so using varchar(14) seems to be as good as text. My experience with other databases (MS SQL Server and Oracle) seems to not apply 1:1 here (I've studied MSSQL internals and - at least up to 2005 version - is much more efficient with char than with varchar than with text - for all operations). Thanks again, Edson > > -- > Sergey Konoplev > Database and Software Architect > http://www.linkedin.com/in/grayhemp > > Phones: > USA +1 415 867 9984 > Russia, Moscow +7 901 903 0499 > Russia, Krasnodar +7 988 888 1979 > > Skype: gray-hemp > Jabber: gray.ru@gmail.com > >
Hello 2012/12/4 Edson Richter <edsonrichter@hotmail.com>: > Em 04/12/2012 18:49, Sergey Konoplev escreveu: > >> On Tue, Dec 4, 2012 at 5:57 AM, Edson Richter <edsonrichter@hotmail.com> >> wrote: >>> >>> In this specific case, the full length (14) is mandatory... so seems >>> there >>> is no loss or gain. >>> Also, I see all varchar(...) created are by default "storage = EXTENDED" >>> (from "Pg Admin"), while other datatypes (like numeric, smallint, >>> integer) >>> are "storage = MAIN". >>> Can I have a gain using fixed length datatype in place of current varchar >>> (like "numeric (14,0)")? >>> Or changing to "char(14) check length(doc)=14" and "storage=MAIN"? >> >> May be I am late with my reply but I would also recommend to take into >> consideration the article from depesz where he explains and tests all >> the textual types >> http://www.depesz.com/2010/03/02/charx-vs-varcharx-vs-varchar-vs-text/. >> >> Very useful one. > > > Yes, good education as well. I've read, and I'll consider the different > datatypes and more domain use in future. > > But I don't see much changes in our schema, so using varchar(14) seems to be > as good as text. > > My experience with other databases (MS SQL Server and Oracle) seems to not > apply 1:1 here (I've studied MSSQL internals and - at least up to 2005 > version - is much more efficient with char than with varchar than with text > - for all operations). sure - PostgreSQL has different design than "older" SQL servers that was developed for fixed length records. So some knowledges related to these databases are wrong here. Regards Pavel Stehule > > Thanks again, > > Edson > > > >> >> -- >> Sergey Konoplev >> Database and Software Architect >> http://www.linkedin.com/in/grayhemp >> >> Phones: >> USA +1 415 867 9984 >> Russia, Moscow +7 901 903 0499 >> Russia, Krasnodar +7 988 888 1979 >> >> Skype: gray-hemp >> Jabber: gray.ru@gmail.com >> >> > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general