PostgreSQL里的17种文本相似算法与GIN索引 - pg_similarity

标签

PostgreSQL , 文本相似 , pg_similarity , pg_trgm , rum , fuzzymatch gin , smlar

---

背景

文本相似算法，结合PostgreSQL的开放索引框架GIN，可以实现各种相似算法的文本高效检索。

PostgreSQL中常见的文本相似搜索插件：rum, pg_trgm, fuzzymatch, pg_similarity, smlar。

其中pg_similarity支持的算法达到了17种。

Introduction

pg_similarity is an extension to support similarity queries on PostgreSQL.

The implementation is tightly integrated in the RDBMS in the sense that it defines operators
so instead of the traditional operators (= and <>) you can use ~~~ and ! (any of these
operators represents a similarity function).

pg_similarity has three main components:

Functions:

a set of functions that implements similarity algorithms available in the literature.

These functions can be used as UDFs and, will be the base for implementing the similarity operators;

Operators:

a set of operators defined at the top of similarity functions.

They use similarity functions to obtain the similarity threshold and,
compare its value to a user-defined threshold to decide if it is a match or not;

Session Variables:

a set of variables that store similarity function parameters. Theses variables can be defined at run time.

• L1 Distance (as known as City Block or Manhattan Distance);
• Cosine Distance;
• Dice Coefficient;
• Euclidean Distance;
• Hamming Distance;
• Jaccard Coefficient;
• Jaro Distance;
• Jaro-Winkler Distance;
• Levenshtein Distance;
• Matching Coefficient;
• Monge-Elkan Coefficient;
• Needleman-Wunsch Coefficient;
• Overlap Coefficient;
• Q-Gram Distance;
• Smith-Waterman Coefficient;
• Smith-Waterman-Gotoh Coefficient;
• Soundex Distance.

用法如下

Algorithm	Function	Operator	Use Index?	Parameters
L1 Distance	block(text, text) returns float8	~++	yes	pg_similarity.block_tokenizer (enum) pg_similarity.block_threshold (float8) pg_similarity.block_is_normalized (bool)
Cosine Distance	cosine(text, text) returns float8	~##	yes	pg_similarity.cosine_tokenizer (enum) pg_similarity.cosine_threshold (float8) pg_similarity.cosine_is_normalized (bool)
Dice Coefficient	dice(text, text) returns float8	~-~	yes	pg_similarity.dice_tokenizer (enum) pg_similarity.dice_threshold (float8) pg_similarity.dice_is_normalized (bool)
Euclidean Distance	euclidean(text, text) returns float8	~!!	yes	pg_similarity.euclidean_tokenizer (enum) pg_similarity.euclidean_threshold (float8) pg_similarity.euclidean_is_normalized (bool)
Hamming Distance	hamming(bit varying, bit varying) returns float8 hamming_text(text, text) returns float8	~@~	no	pg_similarity.hamming_threshold (float8) pg_similarity.hamming_is_normalized (bool)
Jaccard Coefficient	jaccard(text, text) returns float8	~??	yes	pg_similarity.jaccard_tokenizer (enum) pg_similarity.jaccard_threshold (float8) pg_similarity.jaccard_is_normalized (bool)
Jaro Distance	jaro(text, text) returns float8	~%%	no	pg_similarity.jaro_threshold (float8) pg_similarity.jaro_is_normalized (bool)
Jaro-Winkler Distance	jarowinkler(text, text) returns float8	~@@	no	pg_similarity.jarowinkler_threshold (float8) pg_similarity.jarowinkler_is_normalized (bool)
Levenshtein Distance	lev(text, text) returns float8	~==	no	pg_similarity.levenshtein_threshold (float8) pg_similarity.levenshtein_is_normalized (bool)
Matching Coefficient	matchingcoefficient(text, text) returns float8	~^^	yes	pg_similarity.matching_tokenizer (enum) pg_similarity.matching_threshold (float8) pg_similarity.matching_is_normalized (bool)
Monge-Elkan Coefficient	mongeelkan(text, text) returns float8	~||	no	pg_similarity.mongeelkan_tokenizer (enum) pg_similarity.mongeelkan_threshold (float8) pg_similarity.mongeelkan_is_normalized (bool)
Needleman-Wunsch Coefficient	needlemanwunsch(text, text) returns float8	~#~	no	pg_similarity.nw_threshold (float8) pg_similarity.nw_is_normalized (bool)
Overlap Coefficient	overlapcoefficient(text, text) returns float8	~**	yes	pg_similarity.overlap_tokenizer (enum) pg_similarity.overlap_threshold (float8) pg_similarity.overlap_is_normalized (bool)
Q-Gram Distance	qgram(text, text) returns float8	~~~	yes	pg_similarity.qgram_threshold (float8) pg_similarity.qgram_is_normalized (bool)
Smith-Waterman Coefficient	smithwaterman(text, text) returns float8	~=~	no	pg_similarity.sw_threshold (float8) pg_similarity.sw_is_normalized (bool)
Smith-Waterman-Gotoh Coefficient	smithwatermangotoh(text, text) returns float8	~!~	no	pg_similarity.swg_threshold (float8) pg_similarity.swg_is_normalized (bool)
Soundex Distance	soundex(text, text) returns float8	~*~	no

参考

http://pgsimilarity.projects.pgfoundry.org/

https://github.com/eulerto/pg_similarity

https://www.pgcon.org/2009/schedule/attachments/108_pg_similarity.pdf

http://www.sigaev.ru/git/gitweb.cgi?p=smlar.git;a=summary

https://github.com/postgrespro/rum

https://www.postgresql.org/docs/9.6/static/fuzzystrmatch.html

https://www.postgresql.org/docs/9.6/static/pgtrgm.html