From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Ancoron Luciferis <ancoron.luciferis@googlemail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
Date: Sun, 26 May 2019 01:44:18 +0200
Message-ID: <20190525234418.q3dcnirxog3dptwd@development> (raw)
In-Reply-To: <21750.1558823888@sss.pgh.pa.us>
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>
<21750.1558823888@sss.pgh.pa.us>
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
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: tomas.vondra@2ndquadrant.com, tgl@sss.pgh.pa.us, ancoron.luciferis@googlemail.com, pgsql-performance@lists.postgresql.org
Subject: Re: UUID v1 optimizations...
In-Reply-To: <20190525234418.q3dcnirxog3dptwd@development>
* 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