Re: Change sort order on UUIDs? - Mailing list pgsql-hackers

From mark@mark.mielke.cc
Subject Re: Change sort order on UUIDs?
Date
Msg-id 20070615212623.GA9843@mark.mielke.cc
Whole thread Raw
In response to Re: Change sort order on UUIDs?  (Michael Glaesemann <grzm@seespotcode.net>)
List pgsql-hackers
On Fri, Jun 15, 2007 at 09:40:29AM -0500, Michael Glaesemann wrote:
> On Jun 14, 2007, at 19:04 , mark@mark.mielke.cc wrote:
> >For UUID, I
> >would value random access before sequential performance. Why would
> >anybody scan UUID through the index in "sequential" order?
> AIUI, to allow UUID columns to be indexed using BTREE, there needs to  
> be some ordering defined. So regardless of what this ordering is,  
> doesn't there need to be some order? And as a (primary?) purpose of  
> UUIDs is to be (universally) unique, and the implementation of  
> uniqueness constraints in PostgreSQL is based on BTREE indexes, this  
> makes the necessity of ordering doubly so. Or have I missed something?

The BTREE needs a comparator function, yes. The BTREE comparator
function need not match any expectation of the caller.

For example, if Robert is correct, that indexes on UUID will become
fragmented over time with the current comparator, then he is providing
only a half solution. He will have non fragmented time-based UUID
index, while continuing to allow everybody else to have fragmented
non time-based UUID. The logic does not make sense. Either fragmentation
is an issue, or it is not. Concurrency is either an issue, or it is not.

I do not believe that it has been demonstrated that there is a
fragmentation issue, nor do I believe it has been shown what effect
would occur on concurrency.

Personally, my preference is for the BTREE comparator to be the fastest
comparison possible, to allow the quickest scanning of the index entries.
I don't believe fragmentation is a serious issue, and I believe there are
concurrency benefits to inserting into different index pages, rather than
always adding to the end.

Cheers,
mark

-- 
mark@mielke.cc / markm@ncf.ca / markm@nortel.com     __________________________
.  .  _  ._  . .   .__    .  . ._. .__ .   . . .__  | 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
bindthem...
 
                          http://mark.mielke.cc/



pgsql-hackers by date:

Previous
From: PFC
Date:
Subject: Re: Load Distributed Checkpoints test results
Next
From: Mauricio Peccorini
Date:
Subject: Re: gettimeofday problem with mingw