Re: Integer keys vs. Varchar keys

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: David Link <dlink(at)soundscan(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Integer keys vs. Varchar keys
Date: 2001-11-30 17:37:27
Message-ID: 8501.1007141847@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-11-30 17:44:08 Re: Initdb failure with PG 7.1.3 on RH 7.1...
Previous Message Paul Stavrides 2001-11-30 17:18:27 Re: Initdb failure with PG 7.1.3 on RH 7.1...