Re: Change sort order on UUIDs?

> Heikki Linnakangas <heikki(at)enterprisedb(dot)com> writes:
> > 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.
>
> Hm. Still, given that that benefit would only accrue for one version
of
> uuid generation, it's a pretty weak argument.
>
> The concrete reason for not changing it is that the sort ordering of
> uuids would then look quite unnatural compared to the display format.
> Which would provoke confusion and bug reports...
>
> regards, tom lane

If it improves non-user controllable indexing behavior, doesn't
negatively affect the indexing of random/hash based UUIDs, and only
seems to affect ordering for the display format, it seems worth it to
me.

A paragraph in the documentation stating how UUIDs are sorted seems to
satisfy the visual ordering concern, which is more than what Microsoft
is doing (I had to dig for a blog post to find this out.)

In addition it would be very odd to sort random/hashed GUIDs and expect
anything that in meaningful, anyway. If the user wants to see a UUID
lexographically sorted, they could also cast the column to text like so:

select uuid_column from uuid_test order by uuid_column::text;

... which produces the desired output for visual analysis if that was
desired while still retaining all the other benefits.

I'll continue thinking about any other downsides to this tonight, too.

-- Robert