Re: Pattern matching operators a index

On 09.10.2013 13:24, Soroosh Sardari wrote:
> I'm developing a new type for character string, like varchar. I wrote
> operators for btree and so forth.
> I wonder how pattern matching operators using btree index, because btree
> operator class ony knows about>,>=,<=, and = operators, but operators
> for pattern matching, such as LIKE, are not known for btree access method.
>
> Now my question is:
> Is Postgre using btree for pattern matching query for varchar or other
> character string types?
>
> If it does, how i implement it for my new type?

Yes, Postgres can use b-tree for LIKE, if the pattern contains a fixed
prefix. For example, "col LIKE 'foo%'" can use an index. Unfortunately
the support for that is hardcoded for the built-in pattern matching
operators, and it's not possible to do the same for a custom data type
without changing the backend code. The code that does the transformation
is in src/backend/optimizer/path/indxpath.c, see section 'routines for
"special" indexable operators'.

There has been some talk on generalizing that, but no-one's gotten
around to it. See e.g
http://www.postgresql.org/message-id/9860.1364013108@sss.pgh.pa.us.
Patches are welcome.

- Heikki