pg_trgm

pg_trgm Overview

The pg_trgm module provides functions and operators to determine the similarity of the alphanumeric text based on the matching of the ternary model, and an index operator class that supports quick search of similar strings.

Install pg_trgm

1. Access Download page of the MogDB official website, download the pg_trgm extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf pg_trgm-1.0-3.1.0-01-CentOS-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd pg_trgm/
   make install

4. Connect to the database and execute the create extension pg_trgm; to use it.

   MogDB=# create extension pg_trgm;
   CREATE EXTENSION

Use pg_trgm

1. In the database, create the following extension:

   -- pg_trgm:
   create extension pg_trgm;

2. Create a table and insert 1,000,000 rows of data into the table.

   create table trgm_test(id int, name varchar);
   insert into trgm_test select generate_series(1,1000000),md5(random()::name);

3. Query the table without using pg_trgm.

   explain analyze select * from trgm_test where name like '%69089%';

   select * from trgm_test where name like '%69089%';

4. Create an index.

   create index idx_trgm_test_1 on trgm_test using gin(name gin_trgm_ops);

   explain analyze select * from trgm_test where name like '%ad44%';

   select * from trgm_test where name like '%305696%';