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

Assuming PostgreSQL is running on a 64 bit server, what is the performance difference between an int4 (32 b开发者_如何转开发it) 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)?

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

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.