Create operator class for pattern matching like text_pattern_ops

Question

I created my own comparison operators on the text datatype that uses natural ordering ('1' < '2' < '10' < '11' etc), using my new operators #<#, #<=#, #># and #>=#.

Now I place them into an operator class to be able to create an index on them, like this:

CREATE OPERATOR CLASS text_natsort_ops
   FOR TYPE text USING btree AS
   OPERATOR 1  #<#,
   OPERATOR 2  #<=#,
   OPERATOR 3  =,
   OPERATOR 4  #>=#,
   OPERATOR 5  #>#,
   FUNCTION 1  bttext_natsort_cmp(text, text);

However, when I create an index using my new text_natsort_ops, this is not used in queries involving like as it is done when the text_pattern_ops are used.

How do I declare my operator class to allow like to use my index?

UPDATE:

The above seems to work, so the question is invalid. My real problem was that I used a query like:

select *
  from mytable
 where number like 'edi%'
 order by number using #<#
 limit 10

and I also had another index using text_pattern_ops, which was choosen by the planner because it seems to work much faster. However, because of the order by ... using only the index using my new ops will be useful... the other index returns too many results, and I need the limit clause to be available to the index scan.

Answer 1

Have a look at the PostgreSQL Prefix extension at https://github.com/dimitri/prefix They, too, define their own OPERATOR CLASS and tell PostgreSQL to use special GIST indexes for certain operators, Maybe you need something similar?

CREATE OPERATOR CLASS gist_prefix_range_ops DEFAULT FOR TYPE prefix_range USING gist ...

Answer 2

The above seems to work, so the question is invalid. My real problem was that I used a query like:

select *
  from mytable
 where number like 'edi%'
 order by number using #<#
 limit 10

and I also had another index using text_pattern_ops, which was choosen by the planner because it seems to work much faster. However, because of the order by ... using only the index using my new ops will be useful... the other index returns too many results, and I need the limit clause to be available to the index scan.