Index with new opclass not used for sorting

From: Ancoron Luciferis <ancoron(dot)luciferis(at)googlemail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Index with new opclass not used for sorting
Date: 2019-06-21 05:40:17
Message-ID: 271e728c-80dd-e3ee-508b-a21017d2e519@googlemail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ian Barwick 2019-06-21 05:41:10 Re: perl extension error
Previous Message Prakash Ramakrishnan 2019-06-21 05:21:57 perl extension error