Re: UUID v1 optimizations...

On Sat, May 25, 2019 at 06:38:08PM -0400, Tom Lane wrote:
>Tomas Vondra <tomas.vondra@2ndquadrant.com> writes:
>> On Sat, May 25, 2019 at 05:54:15PM -0400, Tom Lane wrote:
>>> What you might want to think about is creating a function that maps
>>> UUIDs into an ordering that makes sense to you, and then creating
>>> a unique index over that function instead of the raw UUIDs.  That
>>> would give the results you want without having to negotiate with the
>>> rest of the world about whether it's okay to change the semantics
>>> of type uuid.
>
>> FWIW that's essentially what I implemented as an extension some time
>> ago. See [1] for a more detailed explanation and some benchmarks.
>
>Also, another way to attack this is to create a new set of ordering
>operators for UUID and an associated non-default btree opclass.
>Then you declare your index using that opclass and you're done.
>The key advantage of this way is that the regular UUID equality
>operator can still be a member of that opclass, meaning that searches
>of the form "uuidcol = constant" can still use this index, so you
>don't have to change your queries (at least not for that common case).
>Look at the interrelationship of the regular text btree operators and
>the "pattern_ops" btree operators for a precedent.
>

Perhaps. But it does not allow to tune how often the values "wrap" and,
which I think is an useful capability.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services