On Mon, 2008-08-25 at 11:02 -0600, Scott Marlowe wrote: > Well, of course a 64 bit int is gonna be bigger than a 32 bit, but > with alignment issues and on 64 bit hardware, I'm guessing the > difference isn't exactly twice as slow / twice as much storage. And > it's way faster than a GUID which was what I think started this > thread.
I took a slice of data from our dev box and generated a table using integers and bigints. For reference, the schema is: bigint table: Type | Modifiers --------+----------- bigint | date | bigint | bigint | bigint | bigint | bigint | date | date | bytea | integer | integer | numeric | numeric | numeric | integer | integer | integer | integer | integer | integer | integer | bytea | int table: Type | Modifiers --------+----------- bigint | date | integer | integer | integer | integer | integer | date | date | bytea | integer | integer | numeric | numeric | numeric | integer | integer | integer | integer | integer | integer | integer | bytea | The integer version is 599752704 bytes, and the bigint version is 673120256 bytes (a ~12% size increase). When joining the table to itself (keys = 1 date, 5 (big)ints, no indexes), the bigint version performs a join to itself with an average of 44.1 sec, and the integer version in 29.6 sec (a 48% performance hit). While granted that it's not twice as big and twice as slow, I think it's a fairly valid reason to want to stay within (small)int ranges. Sometimes the initial performance hit on insert would really be worth the continuing space/performance savings down the road. Of course, this wasn't very scientific and the benchmarks aren't very thorough (for instance I assumed that bigserial is implemented as a bigint), but it should remain a valid point. Of course, it probably has no bearing on the OP's problem. So my advice to the OP: have you considered not keying such a volatile table on a serial value? -Mark -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
