使用pg |
您所在的位置:网站首页 › 数据库trim查询 › 使用pg |
功能简介 pg_trgm插件引入了Trigram概念,一个Trigram是从一个字符串中取出的由三个连续字符组成的文本组。在pg_trgm插件中,从文本提取的Trigram长度为3,对于长度小于3的Trigram,将以空格前后缀填充得到最终的Trigram,且默认只能包含两个空格前缀和一个空格后缀。例如: postgres=# SELECT show_trgm('abc'); show_trgm ------------------------- {" a"," ab","abc","bc "} (1 row)将文本拆解为Trigram后,可以通过对两个字符串之间共享的Trigram计数来度量它们的相似度。这种简单的思想已经成为在很多自然语言中度量词相似度的有效方法。下文介绍pg_trgm插件的几个使用场景。 注意事项AnalyticDB PostgreSQL 6.0版且小版本为V6.3.8.9及以上的实例支持pg_trgm插件。 AnalyticDB PostgreSQL 7.0版且小版本为V7.0.2及以上的实例支持pg_trgm插件。 说明如何查看实例内核版本,请参见查看内核小版本。 安装插件在使用pg_trgm插件进行文本相似度查询之前,请您在云原生数据仓库 AnalyticDB PostgreSQL 版实例插件管理中安装pg_trgm插件。具体操作,请参见安装、升级与卸载插件。 文本相似度匹配查询在安装pg_trgm插件后,使用Trigram可以对文本的相似度进行匹配查询,并对文本的相似度进行排序。例如,创建测试表并插入测试数据: CREATE TABLE test_trgm (t text); INSERT INTO test_trgm values('word'), ('This is a pg_trgm test'), ('word test'), ('w0rd'), ('test word');您可以通过以下两种方式进行文本相似度匹配查询。 方式一:使用pg_trgm插件中的similarity函数及%操作符实现。例如,进行包含关键词word的相似度查询。similarity函数及%操作符的更多信息,请参见附录。 SELECT t, similarity(t, 'word') AS sml FROM test_trgm WHERE t % 'word' ORDER BY sml DESC, t;包含关键词word的结果被输出,并按照相似度进行了排序。查询结果如下: t | sml -----------+----- word | 1 test word | 0.5 word test | 0.5 (3 rows)方式二:使用pg_trgm插件中的操作符实现。例如在如下查询中,查询得到测试数据与关键词之间的“距离”(相似度的反义),并按照距离从近到远排序。操作符的更多信息,请参见附录。 postgres=# SELECT t, t 'word' AS dist FROM test_trgm ORDER BY dist LIMIT 10; t | dist ------------------------+------ word | 0 word test | 0.5 test word | 0.5 w0rd | 0.75 This is a pg_trgm test | 1 (5 rows)结合索引加速模糊匹配查询在未使用pg_trgm插件时,模糊匹配查询(LIKE查询)不能使用索引,查询性能通常较慢。pg_trgm插件提供了GIST和GIN索引操作符类,允许在文本列创建索引用于加速相似度查询。下面通过示例说明如何结合pg_trgm插件和GIN索引加速查询。 首先创建一张测试表,并插入测试数据: CREATE TABLE test_trgm (t text); INSERT INTO test_trgm SELECT md5(random()::text) FROM generate_series(1,1000000) i;通过Explain Analyze执行模糊匹配查询并查看执行计划,可以看到查询使用了Seq Scan全表扫描,在数据量大时查询性能较慢: postgres=# explain analyze SELECT * FROM test_trgm WHERE t LIKE '%abcd%'; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=0.00..259.63 rows=422 width=32) (actual time=137.606..179.674 rows=431 loops=1) -> Seq Scan on test_trgm (cost=0.00..254.00 rows=141 width=32) (actual time=0.961..136.977 rows=146 loops=1) Filter: (t ~~ '%abcd%'::text) Rows Removed by Filter: 333458 Optimizer: Postgres-based planner Planning Time: 0.328 ms (slice0) Executor memory: 37K bytes. (slice1) Executor memory: 36K bytes avg x 3 workers, 36K bytes max (seg0). Memory used: 128000kB Execution Time: 180.533 ms (10 rows)使用pg_trgm插件中的gin_trgm_ops索引操作符创建GIN索引。gin_trgm_ops索引操作符的更多信息,请参见附录。 CREATE INDEX trgm_idx ON test_trgm USING GIN (t gin_trgm_ops);再次进行相同的查询,可以看到查询使用了Bitmap Index Scan,查询性能大幅度提升: postgres=# explain analyze SELECT * FROM test_trgm WHERE t LIKE '%abcd%'; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) (cost=48.67..882.67 rows=8000 width=32) (actual time=4.353..4.550 rows=431 loops=1) -> Bitmap Heap Scan on test_trgm (cost=48.67..776.00 rows=2667 width=32) (actual time=1.612..3.091 rows=146 loops=1) Recheck Cond: (t ~~ '%abcd%'::text) Rows Removed by Index Recheck: 10 -> Bitmap Index Scan on trgm_idx (cost=0.00..48.00 rows=2667 width=0) (actual time=1.536..1.537 rows=163 loops=1) Index Cond: (t ~~ '%abcd%'::text) Optimizer: Postgres-based planner Planning Time: 1.353 ms (slice0) Executor memory: 44K bytes. (slice1) Executor memory: 2438K bytes avg x 3 workers, 2438K bytes max (seg0). Memory used: 128000kB Execution Time: 5.385 ms (12 rows)附录GUC参数pg_trgm.similarity_threshold 设置%操作符使用的当前词相似度阈值。该阈值必须位于0~1之间,默认为0.3。 pg_trgm.word_similarity_threshold 设置操作符使用的当前词相似度阈值。该阈值必须位于0~1之间,默认为0.6。 函数函数 返回值 描述 similarity(text, text) real 返回一个数值表示两个参数的相似程度。该结果范围为0~1。 0:两个字符串完全不相似。 1:两个字符串完全一样。 show_trgm(text) text[] 返回一个给定字符串中所有Trigram组成的一个数组。实际更多用于调试。 word_similarity(text, text) real 返回一个数值,表示第一个字符串和第二个字符串中最相似的词的相似度。该函数在第二个字符串中是搜索最相似的词而不是最相似的子串。该结果范围为0~1。 0:两个字符串完全不相似。 1:第一个字符串和第二个字符串中的某个词相同。 操作符操作符 返回值 描述 text % text boolean 如果函数的计算结果大于pg_trgm.similarity_threshold参数设置的阈值,则返回true。 text text boolean |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |