pg中模糊查询的功能十分强大,甚至支持前后模糊查询。我们可以通过pg自带的pg_trgm插件来进行正则表达式~和like的模糊查询,这两种方式语义是一样的。
例如这两个语句效果是相同的:
select * from test where col like ‘%xxxxxx%’;
select * from test where col ~ ‘xxxxxx’;
但是性能上存在一定的差异,like的性能会好很多。因为两者使用的处理方式不同,详情见两者的源码:
src/backend/utils/adt/like.c
src/backend/utils/adt/regexp.c
例子:
–创建测试表
bill=# create table test(id int,info text);
CREATE TABLE
bill=# insert into test select generate_series(1,100000),md5(random()::text);
INSERT 0 100000
bill=# create index idx_test on test using gin(info gin_trgm_ops);
CREATE INDEX
bill=# vacuum ANALYZE test;
VACUUM
–正则表达式~:
bill=# explain (analyze ,verbose,timing,costs,buffers) select * from test where info ~ 'abc';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bill.test (cost=5.28..18.07 rows=10 width=37) (actual time=0.473..2.464 rows=701 loops=1)
Output: id, info
Recheck Cond: (test.info ~ 'abc'::text)
Heap Blocks: exact=473
Buffers: shared hit=477
-> Bitmap Index Scan on idx_test (cost=0.00..5.28 rows=10 width=0) (actual time=0.386..0.386 rows=701 loops=1)
Index Cond: (test.info ~ 'abc'::text)
Buffers: shared hit=4
Planning Time: 0.574 ms
Execution Time: 2.591 ms
(10 rows)
–like查询
bill=# explain (analyze ,verbose,timing,costs,buffers) select * from test where info like '%abc%';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on bill.test (cost=5.28..18.07 rows=10 width=37) (actual time=0.272..1.236 rows=701 loops=1)
Output: id, info
Recheck Cond: (test.info ~~ '%abc%'::text)
Heap Blocks: exact=473
Buffers: shared hit=477
-> Bitmap Index Scan on idx_test (cost=0.00..5.28 rows=10 width=0) (actual time=0.193..0.193 rows=701 loops=1)
Index Cond: (test.info ~~ '%abc%'::text)
Buffers: shared hit=4
Planning Time: 0.369 ms
Execution Time: 1.340 ms
(10 rows)