Обсуждение: int8 vs int4
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? int4 will reduze the size of the table, of course. Would this reduce size of index, too? By the same amount? 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? kind regards Janning -- kind regards, janning
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