> >> 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, > >> ... > >> 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, > > > > This claim seems like nonsense. Btrees don't care about the ordering > > details of what they index. > > I believe he means that with his modified comparison function, when > inserting a series of UUIDs with increasing time-fields, the index keys > are always inserted to the rightmost page, which gives a more tightly > packed index than scattered inserts all-around the index. >
That was my thinking; that it would speed up (bulk) inserts causing fewer page splits. I'm also using my own contrib module that uses FreeBSD's uuid_create generating DCE 1.1 UUIDs, which keeps the state of the UUID generator in the kernel. The 8.3 contrib module based on uuid-ossp seems to 1) not compile on FreeBSD (conflicts with uuid.h from the OS) and 2) randomizes the clock sequence as there is no state stored between invocations. The other thing this modification does is allow ORDER BY to order by time when possible, which is a nice default behavior as well, yes? -- Robert ---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate