Performance difference between int4 and int8 in PostgreSQL on 64 bit server

Question

Assuming PostgreSQL is running on a 64 bit server, what is the performance difference between an int4 (32 bit) and int8 (64 bit) column? The manual states that int4 is more efficient than int8, but if the underlying server is 64 bit, is there a practical performance difference (in terms of (1) cpu, (2) memory and (3) storage)?

Answer 1

in terms of (1) cpu, (2) memory and (3) storage

Put bluntly:

1. 64 bits is twice as large as 32 bits.

2. 64 bits is twice as large as 32 bits.

3. 64 bits is twice as large as 32 bits.

I recall a thread in wp-hackers that did a few benchmarks. Create a table, fill in a million rows. Then find, add, group, join, etc. I don't recall the specifics, but it was indeed slower to be using int8 than int4.

---

test=# create table int4_test (id int primary key);
CREATE TABLE
test=# create table int8_test (id bigint primary key);
CREATE TABLE
test=# insert into int4_test select i from generate_series(1,1000000) i;
INSERT 0 1000000
test=# insert into int8_test select i from generate_series(1,1000000) i;
INSERT 0 1000000
test=# vacuum analyze;
VACUUM
test=# \timing on
Timing is on.
test=# select sum(i.id) from int4_test i natural join int4_test j where i.id % 19 = 0;
     sum     
-------------
 26315710524
(1 row)

Time: 1364.925 ms
test=# select sum(i.id) from int4_test i natural join int4_test j where i.id % 19 = 0;
     sum     
-------------
 26315710524
(1 row)

Time: 1286.810 ms
test=# select sum(i.id) from int8_test i natural join int8_test j where i.id % 19 = 0;
     sum     
-------------
 26315710524
(1 row)

Time: 1610.638 ms
test=# select sum(i.id) from int8_test i natural join int8_test j where i.id % 19 = 0;
     sum     
-------------
 26315710524
(1 row)

Time: 1554.066 ms

test=# select count(*) from int4_test i natural join int4_test j where i.id % 19 = 0;
 count 
-------
 52631
(1 row)

Time: 1244.654 ms
test=# select count(*) from int4_test i natural join int4_test j where i.id % 19 = 0;
 count 
-------
 52631
(1 row)

Time: 1247.114 ms
test=# select count(*) from int8_test i natural join int8_test j where i.id % 19 = 0;
 count 
-------
 52631
(1 row)

Time: 1541.751 ms
test=# select count(*) from int8_test i natural join int8_test j where i.id % 19 = 0;
 count 
-------
 52631
(1 row)

Time: 1519.986 ms

Answer 2

In terms of storage and memory, the answer is obvious: An INT8 is twice as large as an INT4, therefore it uses twice the storage and twice the memory.

In terms of computational (CPU) performance, I suspect it makes no difference at all on a 64-bit machine, and in some cases INT4 may be more efficient on a 32-bit machine. Although unless you're doing complex math on these INTs (and not just using them as a serial, etc), the computational differences are probably zero, or very nearly zero.

And once you start doing complex things with your INTs, it's not really a database performance question any more anyway.