Re: Pattern matching operators a index

On Wed, Oct 9, 2013 at 4:20 PM, Heikki Linnakangas
<hlinnakangas(at)vmware(dot)com> wrote:
> On 09.10.2013 13:24, Soroosh Sardari wrote:
>> 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.

As per initial thoughts, here I think there are majorly two
functionalities for which some hooks are needed.

1. Identification of operator as a special operator and verification
if it can be indexable.
It is not guaranteed that operator LIKE can be considered
indexable, it is decided by match_special_index_operator() based on
clause.
So to generalize it, there is a need to have an additional column's
amopspecial(to indicate that there is need to verify that
this op is indexable) and amopverify (function that can verify if
special operator is indexable) in pg_amop.

2. Expansion of clauses in a different way for special operator's.
During expansion of opclauses (expand_indexqual_opclause()), LIKE
operator clause needs to be expanded to "textfield >= 'abc' AND
textfield <
'abd'". So here again there is a need to have an additional column
in pg_amop amopexpand (function to expand clauses of special
operators).

I am sure there will be many more things at top level which might be
required to generalize LIKE operator optimisation, but I could think
of only above as per my initial look at this problem. I think more
thoughts/suggestions on this problem can help someone to attempt a
patch for this problem.

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com