On Thu, Jun 14, 2007 at 03:38:44PM -0400, Robert Wojciechowski wrote: > I've been testing the new UUID functionality in 8.3dev and noticed that > UUIDs are sorted using memcmp in their default in-memory layout, which > is: > struct uuid { > uint32_t time_low; > uint16_t time_mid; > uint16_t time_hi_and_version; > uint8_t clock_seq_hi_and_reserved; > uint8_t clock_seq_low; > uint8_t node[_UUID_NODE_LEN]; > }; > When done that way, you're going to see a lot of index B-tree > fragmentation with even DCE 1.1 (ISO/IEC 11578:1996) time based UUIDs, > as described above. With random (version 4) or hashed based (version 3 > or 5) UUIDs there's nothing that can be done to improve the situation, > obviously.
I suggest that treating the UUID as anything other than a unique random value is a mistake. There should be no assumptions by users with regard to how the order is displayed. Also, as UUID generation based on time is always in sequence, it seems to me that sorting by UUID time would have the effect of inserts always being to the end of the index. While this might pack tightly, wouldn't this hurt concurrency? Random access vs sequential performance. For UUID, I would value random access before sequential performance. Why would anybody scan UUID through the index in "sequential" order? Cheers, mark -- [EMAIL PROTECTED] / [EMAIL PROTECTED] / [EMAIL PROTECTED] __________________________ . . _ ._ . . .__ . . ._. .__ . . . .__ | Neighbourhood Coder |\/| |_| |_| |/ |_ |\/| | |_ | |/ |_ | | | | | | \ | \ |__ . | | .|. |__ |__ | \ |__ | Ottawa, Ontario, Canada One ring to rule them all, one ring to find them, one ring to bring them all and in the darkness bind them... http://mark.mielke.cc/ ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match