postgresql分妺_中文模糊查询性能优化 by PostgreSQL trgm-阿里云开发者社区

前模糊，后模糊，前后模糊，正则匹配都属于文本搜索领域常见的需求。

PostgreSQL在文本搜索领域除了全文检索，还有trgm是一般数据库没有的，甚至可能很多人没有听说过。

对于前模糊和后模糊，PG则与其他数据库一样，可以使用btree来加速，后模糊可以使用反转函数的函数索引来加速。

对于前后模糊和正则匹配，则可以使用trgm，TRGM是一个非常强的插件，对这类文本搜索场景性能提升非常有效，100万左右的数据量，性能提升有500倍以上。

例子：

生成100万数据

postgres=# create table tbl (id int, info text);

CREATE TABLE

postgres=# insert into tbl select generate_series(1,1000000), md5(random()::text);

INSERT 0 1000000

postgres=# create index idx_tbl_1 on tbl using gin(info gin_trgm_ops);

CREATE INDEX

postgres=# select * from tbl limit 10;

id | info

----+----------------------------------

1 | dc369f84738f7fa4dc38c364cef817d0

2 | 4912b0b16670c4f2390d44ae790b9809

3 | eb442b00bf3b5bc6863d004a2c8fa3bb

4 | 0b4b8a8ad0cdf2e6870afbb94813eba4

5 | 661e895ee982ec4d9f944b10adffb897

6 | 09c4e7476d4bdfc1ccbdfe92ba0fdbdf

7 | 8b6e442faed938d066dda5e552100277

8 | e5cdeca599d5068a8d3bb6ce9f370827

9 | ddbbfbeaa9199219b7c909fb395d9a69

10 | 96f254f64df1ec43bb0cb4801222c919

(10 rows)

postgres=# select * from tbl where info ~ '670c4f2';

id | info

----+----------------------------------

2 | 4912b0b16670c4f2390d44ae790b9809

(1 row)

Time: 2.668 ms

postgres=# explain analyze select * from tbl where info ~ '670c4f2';

QUERY PLAN

---------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on tbl (cost=28.27..138.43 rows=100 width=37) (actual time=1.957..1.958 rows=1 loops=1)

Recheck Cond: (info ~ '670c4f2'::text)

Heap Blocks: exact=1

-> Bitm