Обсуждение: 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

Tom Lane escreveu: <blockquote cite="mid20197.1179929832@sss.pgh.pa.us" type="cite"><pre wrap="">André Volpato <a
class="moz-txt-link-rfc2396E"
href="mailto:andre.volpato@ecomtecnologia.com.br"><andre.volpato@ecomtecnologia.com.br></a>writes:
</pre><blockquotetype="cite"><pre wrap="">I need to store one-length values like '1', '2', '3' or '4' .
</pre></blockquote><prewrap=""> </pre><blockquote type="cite"><pre wrap="">Today, this field is indexed with btree and
isof 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
queryperformance, 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>

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