Обсуждение: Index with new opclass not used for sorting

Hi,

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       <*,
        OPERATOR        1       <~ (uuid, timestamp with time zone),
        OPERATOR        2       <=*,
        OPERATOR        2       <=~ (uuid, timestamp with time zone),
        OPERATOR        3       =,
        OPERATOR        3       =~ (uuid, timestamp with time zone),
        OPERATOR        4       >=*,
        OPERATOR        4       >=~ (uuid, timestamp with time zone),
        OPERATOR        5       >*,
        OPERATOR        5       >~ (uuid, timestamp with time zone),
        FUNCTION        1       uuid_timestamp_cmp(uuid, uuid),
        FUNCTION        1       uuid_timestamp_only_cmp(uuid, timestamp
with time zone),
        FUNCTION        2       uuid_timestamp_sortsupport(internal)
;

And I checked that after installation of the extension, that the
pg_amproc entries are there:

 family | left | right | num |           amproc
--------+------+-------+-----+----------------------------
 623810 | 2950 |  2950 |   1 | uuid_timestamp_cmp
 623810 | 2950 |  1184 |   1 | uuid_timestamp_only_cmp
 623810 | 2950 |  2950 |   2 | uuid_timestamp_sortsupport

...but when sorting on an (unique) index column, I still get a separate
sort, not using the index, e.g.:

                                       QUERY PLAN

-----------------------------------------------------------------------------------------
 Sort (actual rows=934567 loops=1)
   Sort Key: id
   Sort Method: external merge  Disk: 23784kB
   ->  Index Only Scan using idx_uuid_v1_ext on uuid_v1_ext (actual
rows=934567 loops=1)
         Index Cond: (id <* '2b55fb04-33d8-11e9-9cfa-e03f494ffcf7'::uuid)
         Heap Fetches: 934567

...but I was expecting a similar plan as for the standard UUID SortSupport:

                                 QUERY PLAN

-----------------------------------------------------------------------------
 Index Only Scan using uuid_v1_pkey on uuid_v1 (actual rows=1692025 loops=1)
   Index Cond: (id < '2b55fb04-33d8-11e9-9cfa-e03f494ffcf7'::uuid)
   Heap Fetches: 1692025

Am I missing something obvious here?

Cheers,

    Ancoron

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

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