On Wed, Oct 9, 2013 at 4:20 PM, Heikki Linnakangas <hlinnakan...@vmware.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers