Skip site navigation (1) Skip section navigation (2)

Re: Differences between int8 and int4 as pkeys and fkeys

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: Thomas Swan <tswan(at)olemiss(dot)edu>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Differences between int8 and int4 as pkeys and fkeys
Date: 2000-08-05 02:28:32
Message-ID: 398B7BD0.29F6B732@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-hackers
> I had been using int8 as primary / foreign keys pairs.   After joining
> several large tables on the keys, I noticed about a 30/40% speed
> improvement just in changing the keys from int8 to int4 data types.

Apparently it should be that much (though this is the first report
either way). Here are some reasons why there is the difference:

1) int4 is "pass by value", int8 is "pass by reference". So int8 hits
palloc() every time you generate a new one, whereas int4 may just copy
the data itself.

2) int8 is implemented in software libraries, at least on 32-bit
machines. int4 is implemented in hardware. Libraries are slower than
~1cycle hardware operations.

                          - Thomas

In response to

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2000-08-05 03:13:23
Subject: Re: Security choices...
Previous:From: Thomas LockhartDate: 2000-08-05 02:20:20
Subject: Re: Installation Report for powerpc-apple-netbsdelf1.5

pgsql-general by date

Next:From: Alex PilosovDate: 2000-08-05 02:52:06
Subject: Re: libperl.so
Previous:From: Michael MeskesDate: 2000-08-04 23:26:32
Subject: Re: Embedded SQL C++ preprocessor ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group