PostgreSQL正则及模糊查询优化

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1、带前缀的模糊查询 ~'^abc'

可以使用btree索引优化

create index idx_info on table_name(info)

2、带后缀的模糊查询 ~'abc$'

可以使用reverse函数btree索引

create index idx_info1 on table_name(reverse(info));

3、不带前后缀的模糊查询和正则表达式查询

pg_trgm可以使用trgm的gin索引

CREATE EXTENSION pg_trgm;

CREATEINDEX idx_info2 ontable_name using gin(info gin_trgm_ops);

重点说一下涉及到中文匹配的优化方法，因为trgm不支持wchar,因此需要转换，本来相对TEXT( textsend(text))函数创建索引，但是报错提醒函数必须是IMMUTABLE，因此手中创建一个

函数

create or replace function textsend_i (text) returns bytea as

$$

  select textsend($1);

$$

language sql

strict immutable;

然后再创建索引：

 create index idx_name_1 on jd_daojia_product_1225 using gin(text(textsend_i(product_name)) gin_trgm_ops);

执行查询，发现已经走索引了

SELECT * FROM jd_daojia_product_1225 where text(textsend_i(product_name)) ~ text(textsend_i('苹果')) limit 10

Limit  (cost=22.60..63.55 rows=10 width=295)

  ->  Bitmap Heap Scan on jd_daojia_product_1225  (cost=22.60..1398.39 rows=336 width=295)

        Recheck Cond: ((textsend_i(product_name))::text ~ '\350\213\271\346\236\234'::text)

        ->  Bitmap Index Scan on idx_name_1  (cost=0.00..22.52 rows=336 width=0)

              Index Cond: ((textsend_i(product_name))::text ~ '\350\213\271\346\236\234'::text)