Re: varchar as primary key

On Thu, 2007-05-03 at 22:32 +0200, Alexander Staubo wrote:
> On 5/3/07, Matthew Hixson <hixson(at)poindextrose(dot)org> wrote:
> > Is there a significant performance difference between using int
> > primary keys and string primary keys in Postgres?
>
> PostgreSQL uses B-trees for its indexes, insertion time is logarithmic
> regardless of the type of the key, but strings have a larger overhead
> since they involve character comparisons; (i - j) is a lot faster than
> strcmp(i, j). If you do go for strings, I would suggest that the

If you're using a non-C locale, it's slower than strcmp() too.
PostgreSQL has to do an extra memcpy() in order to use strcoll(),
because strings in postgresql aren't necessarily NULL-terminated and
there's no such thing as strncoll(), unfortunately (a comment in the
code points this out).

This mostly matters in sorting. If your B-tree is in memory and you do a
simple lookup (what else would you do with UUIDs?), it's unlikely to
take very long at all.

Regards,
Jeff Davis