On Sat, May 25, 2019 at 06:38:08PM -0400, Tom Lane wrote:
Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxx> 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
