Search Postgresql Archives

Re: indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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...


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux