Which is faster: char(14) or varchar(14)

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. +

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

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

--
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

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

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

--
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 <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.

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

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

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

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
/**/;/**/

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

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

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

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

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

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

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