Automatic Creation of Indexes Supporting Fuzzy Matching

Availability

This feature is available since MogDB 5.0.4.

Introduction

In A compatibility mode, the automatic creation of indexes that support fuzzy matching is supported by enabling a GUC parameter.

Description

Usually, in MogDB, to create an index that supports fuzzy matching, you need to specify the fuzzy matching operator class (such as text_pattern_ops, varchar_pattern_ops, or bpchar_pattern_ops) when creating the index. Since MogDB version 5.0.4, the use of these operator classes in indexes can be controlled using the allow_like_indexable option in the behavior_compat_options parameter.

Constraints

The allow_like_indexable option is only used to control the usage behavior of operator classes. The following should be noted in actual use:

1. Set this parameter option before creating an index.
2. Ensure that fuzzy matching operations meet the usage conditions of the operator class.

Example

MogDB=# create table t1(a int, b text);
CREATE TABLE
MogDB=# insert into t1 values (1, 'foo'), (2, 'bar');
INSERT 0 2
MogDB=# set behavior_compat_options='allow_like_indexable';
SET
MogDB=# create index on t1(b);
CREATE INDEX
MogDB=# explain select /*+ indexscan(t1) */ * from t1 where b like 'fo%';
                             QUERY PLAN                             
--------------------------------------------------------------------
 Index Scan using t1_b_idx on t1  (cost=0.00..8.27 rows=1 width=36)
   Index Cond: ((b >= 'fo'::text) AND (b < 'fp'::text))
   Filter: (b ~~ 'fo%'::text)
(3 rows)

Related Pages

behavior_compat_options