On 10/09/2009 12:56 PM, tsuraan wrote:
I have a system where it would be very useful for the primary keys for a few tables to be UUIDs (actually MD5s of files, but UUID seems to be the best 128-bit type available). What is the expected performance of using a UUID as a primary key which will have numerous foreign references to it, versus using a 64-bit int (32-bit isn't big enough)? > From the uuid.c in adt, it looks like a UUID is just stored as 8 consecutive bytes, and are compared using memcmp, whereas an int uses primitive CPU instructions for comparison. Is that a significant issue with foreign key performance, or is it mostly just the size that the key would take in all related tables?
The most significant impact is that it takes up twice as much space, including the primary key index. This means fewer entries per block, which means slower scans and/or more blocks to navigate through. Still, compared to the rest of the overhead of an index row or a table row, it is low - I think it's more important to understand whether you can get away with using a sequential integer, in which case UUID is unnecessary overhead - or whether you are going to need UUID anyways. If you need UUID anyways - having two primary keys is probably not worth it.
Cheers, mark -- Mark Mielke<mark@xxxxxxxxx> -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
