openGauss每日一练第20天 | 全文检索

1. 用 tsvector @@ tsquery 和 tsquery @@ tsvector 完成两个基本文本匹配

select 'Keep on going never give up'::tsvector @@ 'no | give'::tsquery;
select 'no | give'::tsquery @@ 'Keep on going never give up'::tsvector;

2. 创建表且至少有两个字段的类型为 text类型，在创建索引前进行全文检索

\timing
create table test (id int4, name text, addr text);
insert into test select r, r||'_name', r||'_addr' from generate_series(1,2000000) r;

select * from test where name like '%1000000_name';
select * from test where name like '%1000000_addr';

3. 创建GIN索引

create index test_name_idx on test using gin(to_tsvector('english',name));

走索引
select * from test where to_tsvector('english',name) @@ to_tsquery('english','1000000_name');

不走索引
select * from test where to_tsvector(name) @@ to_tsquery('1000000_name');

4. 清理数据

DROP TABLE test;