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