Ben wrote:
Yes, it does. So of course it depends on how you use it to know what's
going to be more efficient. For instance, if the rows in this table
contain strings of more than a few bytes, and more than a couple tables
reference this table with a foreign key, then you will quickly start to
save space by using a numeric primary key, even if it is an artificial
construct.
For the kind of work I find myself doing, it's rare that it would be
more efficient to not have the artificial construct. But that doesn't
mean one is always better than the other.
So let me see if I understand this correctly.
If the real-world primary key is large (say up to 100 characters in length) then
the disadvantage is that you are duplicating this referenced key in several
other tables, each element taking up 100 characters. Space is wasted when
compared to int4 ID's. But not really sure if this is a performance problem for
SELECT except for the space required (varchar(128) vs. int4).
Having two keys, a primary_key of int4 and a unique key of varchar(128) would be
very ugly on INSERT/DELETE/UPDATE queries because of the index overhead.
One table may have rows on the order of 100's.
Another table will be 1,000,000.
The many-to-many join would be.. 100,000,000's
And maybe there I would have need for smaller physical index variable types...
