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

For details, please refer to gs_install_plugin or gs_install_plugin_local.

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%';