From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Cc: Ancoron Luciferis <ancoron.luciferis@googlemail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
Date: Sat, 25 May 2019 18:38:08 -0400
Message-ID: <21750.1558823888@sss.pgh.pa.us> (raw)
In-Reply-To: <20190525221428.3yc6hhvj7aawjpjv@development>
References: <8d5f4b67-d540-8d3d-9979-808b9c1e1ca7@googlemail.com>
<20635.1558796270@sss.pgh.pa.us>
<19faa3aa-a103-b991-ff20-b73852085f30@googlemail.com>
<14074.1558821255@sss.pgh.pa.us>
<20190525221428.3yc6hhvj7aawjpjv@development>
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.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, tomas.vondra@2ndquadrant.com, ancoron.luciferis@googlemail.com, pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
In-Reply-To: <21750.1558823888@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox