int8 vs int4

mljv@planwerk6.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

On Tuesday 17 July 2007 17:52:11 you wrote:

mljv@planwerk6.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.

thank you very much for your very detailed and helpful answer. int4 is ok for
almost all use cases for a long time. As my hardware budget is small, i use
small boxes with only 4 GB Ram so if i can reduce the size of database more
data fits in ram. so i will take this aproach and use int4

kind regards
Janning