Thread: Integer keys vs. Varchar keys

Hi,

What performance gains would an integer primary key have over a varchar
primary key, for table joins?

On a 100,000 record table joining with a 9,000 record table, there
appears to be no difference. Index Scan cost=0.00..4.51 in either case.

Would one expect it to make a difference with a 1,000,000 record table?

Thanks, David

David Link <dlink@soundscan.com> writes:
> What performance gains would an integer primary key have over a varchar
> primary key, for table joins?

The comparison operation itself would surely be faster for integers, but
that would probably be swamped out by disk access and other overhead.
What I'd expect is that any measurable difference would come from more
I/O required due to physical increase in the table and index sizes,
because of larger field sizes in the varchar case.  Since you didn't say
how long your varchar keys might be, it's anyone's guess what the
differential will be.

> On a 100,000 record table joining with a 9,000 record table, there
> appears to be no difference. Index Scan cost=0.00..4.51 in either case.

Surely you're not foolish enough to believe that EXPLAIN's estimates
should be trusted ;-).  Get out your stopwatch or turn on
print_query_statistics to get some real elapsed-time numbers.

            regards, tom lane

Tom Lane wrote:
>
> David Link <dlink@soundscan.com> writes:
> > What performance gains would an integer primary key have over a varchar
> > primary key, for table joins?
>
> The comparison operation itself would surely be faster for integers, but
> that would probably be swamped out by disk access and other overhead.
> What I'd expect is that any measurable difference would come from more
> I/O required due to physical increase in the table and index sizes,
> because of larger field sizes in the varchar case.  Since you didn't say
> how long your varchar keys might be, it's anyone's guess what the
> differential will be.

varchar(12).  An alpha numeric UPC code.

Thanks for the information,
David