Re: Integer keys vs. Varchar keys

David Link <dlink(at)soundscan(dot)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