Re: int8 vs int4

On Tuesday 17 July 2007 17:52:11 you wrote:
> 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.

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