Create an operator class for pattern matching, for example text_pattern_ops

Question

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

Now I put them in an operator class to be able to create an index on them, for example:

 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 when using text_pattern_ops .

How to declare my operator class to allow like 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 a different index using text_pattern_ops, which was selected by the scheduler because it works much faster. However, because of order by ... using only an index using my new operating systems will be useful ... another index returns too many results, and I need the restriction clause to be available for index scanning.

Answer 1

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 a different index using text_pattern_ops, which was selected by the scheduler because it works much faster. However, due to the order using only the index using my new operating systems, it will be useful ... another index returns too many results, and I need the restriction suggestion to be available for index scanning.

Answer 2

Take a look at the PostgreSQL prefix extension at https://github.com/dimitri/prefix They also define their own OPERATING CLASS and tell PostgreSQL to use special GIST indexes for certain operators. Maybe you need something like that?

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

Answer 3

I do not think that's possible. You have to think about what you are doing and why it will not work. Either you will have an index that supports prefix search, or something that supports natural range search. You do not have a btree index that supports both.

Consider something like:

 SELECT * FROM foo WHERE bar like '10%' ORDER BY bar USING #>#; 

Your problem is that your two indexes will not have an order that matches. The index for use with LIKE will go 101, 101000, 101001, etc., while the index for your order will go 10, 11, 12, 13 ... 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, ...

Since your order requires a different search order, there is no way to use the same index for both. At some point, if PostgreSQL allows searching for indexes of the physical order, this may become possible, but as long as the indexes are a logical order, I don't think it might work.