Re: Index with new opclass not used for sorting

On 21/06/2019 15:36, Tom Lane wrote:
> Ancoron Luciferis <ancoron.luciferis@googlemail.com> writes:
>> I am creating a new operator class for version 1 UUID's with an
>> extension and thought I was almost done by implementing everything
>> including SortSupport and creating a new opclass as follows:
> 
>> CREATE OPERATOR CLASS uuid_timestamp_ops FOR TYPE uuid
>>     USING btree AS
>>         OPERATOR        1       <*,
>> ...
> 
>> ...but when sorting on an (unique) index column, I still get a separate
>> sort, not using the index, e.g.:
> 
> You did not show your test query, but I imagine it just asked for the
> type's ordinary sort order, which is not what this opclass is claiming
> to provide.  To rely on the index's sort order you'd need something like
> 
>     select id from uuid_v1_ext
>       where id <* '2b55fb04-33d8-11e9-9cfa-e03f494ffcf7'
>       order by id using <* ;
> 
> If you want this opclass to become the default sort order for uuid
> you'd have to remove the opcdefault marking from uuid_ops and attach
> it to this opclass instead.  No, I'm not sure that that wouldn't have
> unpleasant side-effects.
> 
>             regards, tom lane
> 

OK, I somehow feared I'd be getting such an answer.

But thanks a lot for the "using <*" trick, which I somehow didn't know
even exists. That solves the problem for me at least.

Because of this issue I was already thinking about creating a new data
type which is basically just a new name for the existing "uuid" but
would enforce version 1, for which I then could provide the opclass as
default, or?

Btw. is there some example how to create derived types for PG properly
without copy/paste of a lot of existing code?

Thanx a lot and cheers,

    Ancoron