From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Announce <truthhurts@insightbb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: int2 vs int4 in Postgres
Date: Mon, 26 Sep 2005 14:42:53 -0400
Message-ID: <20050926184253.GA8295@surnet.cl> (raw)
In-Reply-To: <KBEKKNMFLELKGIADDEPEGEOKCAAA.truthhurts@insightbb.com>
References: <KBEKKNMFLELKGIADDEPEGEOKCAAA.truthhurts@insightbb.com>
On Mon, Sep 26, 2005 at 12:54:05PM -0500, Announce wrote:
> Is there an performance benefit to using int2 (instead of int4) in cases
> where i know i will be well within its numeric range? I want to conserve
> storage space and gain speed anywhere i can, but i know some apps simply end
> up casting 2byte data to 4byte (like Java int/short).
>
> These int2 values will be used in primary and foreign key fields and I know
> that i must explicitly use tick marks (ex: where int2_column = '12') in
> order to make use of indexes, but my question is IS IT WORTH IT? IS THERE
> ANY REAL GAIN FOR DOING THIS?
> An simple scenario would be:
>
> Songs
> -------
> song_id serial pkey
> genre int2 fkey
> title varchar
Not in this case, because the varchar column that follows the int2
column needs 4-byte alignment, so after the int2 column there must be 2
bytes of padding.
If you had two consecutive int2 fields you would save some the space.
Or int2/bool/bool (bool has 1-byte alignment), etc.
This assumes you are in a tipical x86 environment ... in other
environments the situation may be different.
--
Alvaro Herrera Valdivia, Chile ICBM: S 39º 49' 17.7", W 73º 14' 26.8"
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: alvherre@alvh.no-ip.org, truthhurts@insightbb.com
Subject: Re: int2 vs int4 in Postgres
In-Reply-To: <20050926184253.GA8295@surnet.cl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox