Faster data type for one-length values
Hello,
I need to store one-length values like '1', '2', '3' or '4' .
Today, this field is indexed with btree and is of type smallint (int2).
How much performance will be improved if I change it to "char" (1 byte
length) ? Or int2 is the best solution in this case ?
Thanks !
--
[]�s,
Andr� Volpato
ECOM Tecnologia Ltda
andre.volpato@ecomtecnologia.com.br
=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre.volpato@ecomtecnologia.com.br> writes:
I need to store one-length values like '1', '2', '3' or '4' .
Today, this field is indexed with btree and is of type smallint (int2).
How much performance will be improved if I change it to "char" (1 byte
length) ?
The improvement will be zero. Because of alignment restrictions, you
don't save any space from making an index entry smaller than 4 bytes.
If you have several such fields adjacent in a table row, making them all
narrower can save space, but it doesn't help for standalone index
entries.
regards, tom lane
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Tom Lane escreveu:
<blockquote cite="mid20197.1179929832@sss.pgh.pa.us" type="cite">
<pre wrap="">=?ISO-8859-1?Q?Andr=E9_Volpato?= <a class="moz-txt-link-rfc2396E" href="mailto:andre.volpato@ecomtecnologia.com.br"><andre.volpato@ecomtecnologia.com.br></a> writes:
</pre>
<blockquote type="cite">
<pre wrap="">I need to store one-length values like '1', '2', '3' or '4' .
</pre>
</blockquote>
<pre wrap=""><!---->
</pre>
<blockquote type="cite">
<pre wrap="">Today, this field is indexed with btree and is of type smallint (int2).
How much performance will be improved if I change it to "char" (1 byte
length) ?
</pre>
</blockquote>
<pre wrap=""><!---->
The improvement will be zero. Because of alignment restrictions, you
don't save any space from making an index entry smaller than 4 bytes.
If you have several such fields adjacent in a table row, making them all
narrower can save space, but it doesn't help for standalone index
entries.
regards, tom lane
</pre>
</blockquote>
<br>
Tom,<br>
<br>
The ammount of space saved seems pretty clear to me. <br>
<br>
What are you saying is that the index behaviour is the same, for all
types smaller than 4 bytes ? For query performance, in a search is
based on that standalone indexed field, would be any difference using :<br>
a. char(1) ; b. int2; c. "char"<br>
<br>
Thanks again !<br>
<br>
<pre class="moz-signature" cols="72">--
[]´s,
André Volpato
ECOM Tecnologia Ltda
<a class="moz-txt-link-abbreviated" href="mailto:andre.volpato@ecomtecnologia.com.br">andre.volpato@ecomtecnologia.com.br</a>
(41) 3014 2322</pre>
</body>
</html>
Andr� Volpato escreveu:
Tom Lane escreveu:
=?ISO-8859-1?Q?Andr=E9_Volpato?= <andre.volpato@ecomtecnologia.com.br> writes:
I need to store one-length values like '1', '2', '3' or '4' .
Today, this field is indexed with btree and is of type smallint (int2).
How much performance will be improved if I change it to "char" (1 byte
length) ?The improvement will be zero. Because of alignment restrictions, you
don't save any space from making an index entry smaller than 4 bytes.If you have several such fields adjacent in a table row, making them all
narrower can save space, but it doesn't help for standalone index
entries.regards, tom lane
Tom,
The ammount of space saved seems pretty clear to me.
What are you saying is that the index behaviour is the same, for all
types smaller than 4 bytes ? For query performance, in a search is
based on that standalone indexed field, would be any difference using :
a. char(1) ; b. int2; c. "char"
Thanks again !
Andre Volpato
Andr� Volpato wrote:
The ammount of space saved seems pretty clear to me.
Yeah, zero most of the time due to alignment.
What are you saying is that the index behaviour is the same, for all
types smaller than 4 bytes ? For query performance, in a search is
based on that standalone indexed field, would be any difference using :a. char(1) ; b. int2; c. "char"
char(1) takes at least five bytes (more if the char is multibyte), so it
would be slower than the other two, ceteris paribus.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Alvaro Herrera wrote:
Andr� Volpato wrote:
The ammount of space saved seems pretty clear to me.
Yeah, zero most of the time due to alignment.
So trading off more I/O for less CPU?
I wonder if for any I/O bound database servers
it might be worth packing tightly rather than
aligning indexes on one-byte data.
Ron Mayer wrote:
Alvaro Herrera wrote:
André Volpato wrote:
The ammount of space saved seems pretty clear to me.
Yeah, zero most of the time due to alignment.
So trading off more I/O for less CPU?
I wonder if for any I/O bound database servers
it might be worth packing tightly rather than
aligning indexes on one-byte data.
The OP didn't say one-byte data, they said one-char data.
--
Lew