Re: int8 vs int4

mljv(at)planwerk6(dot)de wrote:
> I use int8 types in most PK or FK columns in a pg 8.1 database.
>
> Would int4 instead of int8 speed up creation of an index?

Almost certainly, but by how much will depend on your hardware and size
of index.

> int4 will reduze the size of the table, of course. Would this reduce size of
> index, too? By the same amount?

By four bytes per entry. That's not to say you'll halve the size of your
index - obviously there's overhead for each row.

> How much speed up will i gain on queries? Postgresql Doc mentions a speed-up.
> Is it more like 0,1%, 1% or 10% speed-up?

Depends. If your index didn't fit in cache before and does now, the
difference can be startling.

Here's the question to ask yourself: which columns need a 32-bit
identifier, and which need a 64-bit one? Unless you're planning a
*really* big application, user_id can probably be an int4.

--
Richard Huxton
Archonet Ltd