Re: indexes

On Sat, Nov 25, 2006 at 12:14:11PM -0500, Tom Allison wrote:
> 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).

Well, it kinda sucks for joining because comparing strings may take
tens to hundreds of times as long as compairng integers. It's not just
byte-wise comparison but you have to be locale sensetive about it.

My main problem with using any kind of string as "natural key" is that
once you start passing it around you have worry about the encoding of
said string and when it goes a round-trip to a client, will what you
get back still be the same? If you can guarentee ASCII you might be ok,
but otherwise... Integers have none of these issues.

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

In general my tables are queried several orders of magnitude more often
than they are updated, so index update cost isn't all that relevent.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.