Index with new opclass not used for sorting - Mailing list pgsql-general

From Ancoron Luciferis
Subject Index with new opclass not used for sorting
Date
Msg-id 271e728c-80dd-e3ee-508b-a21017d2e519@googlemail.com
Whole thread Raw
Responses Re: Index with new opclass not used for sorting
List pgsql-general
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



pgsql-general by date:

Previous
From: Prakash Ramakrishnan
Date:
Subject: perl extension error
Next
From: Ian Barwick
Date:
Subject: Re: perl extension error