pg_trgm

• функции и операторы для определения схожести алфавитно-цифровых строк на основе триграмм;
• классы операторов индексов, поддерживающие быстрый поиск схожих строк.

CREATE TABLE test_trgm (t text);
CREATE INDEX trgm_idx ON test_trgm USING GIST (t gist_trgm_ops);

Раскрыть type=sql
                                 Table "ext.test_trgm"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
 t      | text |           |          |         | extended |              |
Indexes:
    "trgm_idx" gist (t gist_trgm_ops)
Access method: heap

Раскрыть type=sql
                                 Table "ext.test_trgm"
 Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
 t      | text |           |          |         | extended |              |
Indexes:
    "trgm_idx" gist (t gist_trgm_ops)
    "trgm_idxs" gist (t gist_trgm_ops (siglen='32'))
Access method: heap

SELECT t, similarity (t, 'first') AS sml
  FROM test_trgm
  WHERE t % 'fir'
  ORDER BY sml DESC, t;

Раскрыть type=sql
   t   |    sml
-------+------------
 first |          1
 fire  |      0.375
 fires | 0.33333334
 fi    |  0.2857143
(4 rows)

SELECT t, t <-> 'fir' AS dist
  FROM test_trgm
  ORDER BY dist LIMIT 10;

Раскрыть type=sql
   t    |    dist
--------+------------
 fire   |        0.5
 first  | 0.57142854
 fires  | 0.57142854
 fi     |        0.6
 th     |          1
 third  |          1
 second |          1
 tyres  |          1
 second |          1
(9 rows)

SELECT t, word_similarity('fir', t) AS sml
  FROM test_trgm
  WHERE 'fir' <% t
  ORDER BY sml DESC, t;

Раскрыть type=sql
   t   | sml
-------+------
 fire  | 0.75
 fires | 0.75
 first | 0.75
 first | 0.75
(4 rows)

SELECT t, strict_word_similarity('fir', t) AS sml
  FROM test_trgm
  WHERE 'fir' <<% t
  ORDER BY sml DESC, t;

Раскрыть type=sql
  t   | sml
------+-----
 fire | 0.5
(1 row)

• с операторами LIKE и ILIKE, например:
  SELECT * FROM test_trgm WHERE t LIKE '%fir%tyr';
  При таком поиске по индексу сначала из искомой строки извлекаются триграммы, а затем они ищутся в индексе. Чем больше триграмм оказывается в искомой строке, тем более эффективным будет поиск по индексу. В отличие от поиска по B-дереву, искомая строка не должна привязываться к левому краю.
  Результат выполнения запроса:

  Раскрыть type=sql
   t
  ---
  (0 rows)
  

• по регулярным выражениям (операторы ~ и ~*), например:
  SELECT * FROM test_trgm WHERE t ~ '(fir|tyr)';
  При таком поиске из регулярного выражения извлекаются триграммы, а затем они ищутся в индексе. Чем больше триграмм удается извлечь из регулярного выражения, тем более эффективным будет поиск по индексу. В отличие от поиска по B-дереву, искомая строка не должна привязываться к левому краю.
  Результат выполнения запроса:

  Раскрыть type=sql
   t
  -------
   first
   fire
   fires
   tyres
  (4 rows)
  

• построить дополнительную таблицу, содержащую все уникальные слова в документе:

  CREATE TABLE words AS SELECT word FROM
          ts_stat('SELECT to_tsvector(''simple'', bodytext) FROM documents');
  

  где:
  • documents — это таблица с текстовым полем bodytext, по которому будет выполняться поиск;
  • simple – конфигурация используется с функцией to_tsvector вместо конфигурации для определенного языка по причине того, что нам нужен список исходных (необработанных стеммером) слов.
• создать индекс триграмм по столбцу со словами:
  CREATE INDEX words_idx ON words USING GIN (word gin_trgm_ops);

Использование модуля

1. Функция similarity:
   SELECT similarity ('Pangolin','Postgres');
   Пример результата запроса:

   Раскрыть type=sql
    similarity
   ------------
    0.05882353
   (1 row)
   

   Другой пример:
   SELECT similarity ('cat','cate');
   Пример результата запроса:

   Раскрыть type=sql
    similarity
   ------------
           0.5
   (1 row)
   

2. Функция show_trgm:
   SELECT show_trgm ('Pangolin');
   Пример результата запроса:

   Раскрыть type=sql
                     show_trgm
   ---------------------------------------------
    {"  p"," pa",ang,gol,"in ",lin,ngo,oli,pan}
   (1 row)
   

3. Функция word_similarity:
   SELECT word_similarity ('Pangolin','Mango');
   Пример результата запроса:

   Раскрыть type=sql
    word_similarity
   -----------------
         0.22222222
   (1 row)
   

4. Функция strict_word_similarity:
   SELECT strict_word_similarity ('Pangolin','Mango');
   Пример результата запроса:

   Раскрыть type=sql
    strict_word_similarity
   ------------------------
                0.15384616
   (1 row)
   

Ссылки на документацию разработчика