int2 vs int4 in Postgres

From: "Announce" <truthhurts(at)insightbb(dot)com>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: int2 vs int4 in Postgres
Date: 2005-09-26 17:54:05
Message-ID: KBEKKNMFLELKGIADDEPEGEOKCAAA.truthhurts@insightbb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Genres
-------
genreid int2 pkey
name varchar
description varchar

I KNOW that I am not going to have anywhere near 32,000+ different genres in
my genre table so why use int4? Would that squeeze a few more milliseconds
of performance out of a LARGE song table query with a genre lookup?

Thanks,

-Aaron

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Alvaro Herrera 2005-09-26 18:42:53 Re: int2 vs int4 in Postgres
Previous Message Cristian Prieto 2005-09-26 17:49:59 Re: Index use in BETWEEN statement...