openGauss每日一练第20天 | 学习心得体会

学习目标

学习openGauss全文检索

openGauss提供了两种数据类型用于支持全文检索。tsvector类型表示为文本搜索优化的文件格式，tsquery类型表示文本查询

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

omm=# SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector AS RESULT;
 result 
--------
 f
(1 row)


omm=# SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat') AS RESULT;
 result 
--------
 t
(1 row)

omm=# SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & cow') AS RESULT;
omm=#  result 
--------
 f
(1 row)

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

omm=# CREATE SCHEMA cj;
CREATE SCHEMA
omm=# CREATE TABLE cj.t1(id int, body text, title text, last_mod_date date);
CREATE TABLE
 in Asia, is the world''s most populous state.', 'China', '2010-1-1');ublic of China(PRC), located 
INSERT 0 1
rumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1');70 by multi-instr
omm=# INSERT 0 1
res land borders with Scotland to the north and Wales to the west.', 'England','2010-1-1');ar
INSERT 0 1


omm=# SELECT id, body, title FROM cj.t1 WHERE to_tsvector(body) @@ to_tsquery('america');
 id |                                                          body                               
                            |  title  
----+---------------------------------------------------------------------------------------------
----------------------------+---------
  2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, D
an Peek, and Gerry Beckley. | America
(1 row)


omm=# SELECT title FROM cj.t1 WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('china & asia')
 title 
-------
 China
(1 row)

omm=# 

3.创建GIN索引

omm=# CREATE INDEX pgweb_idx_1 ON cj.t1 USING gin(to_tsvector('english', body));
CREATE INDEX


omm=#explain SELECT title FROM cj.t1 WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('china  & asia');
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..1.06 rows=1 width=32)
   Filter: (to_tsvector(((title || ' '::text) || body)) @@ '''china'' & ''asia'''::tsquery)
(2 rows)


omm=# \d+ cj.t1 
                                Table "cj.t1"
    Column     |  Type   | Modifiers | Storage  | Stats target | Description 
---------------+---------+-----------+----------+--------------+-------------
 id            | integer |           | plain    |              | 
 body          | text    |           | extended |              | 
omm=#  title         | text    |           | extended |              | 
 last_mod_date | date    |           | plain    |              | 
Indexes:
    "pgweb_idx_1" gin (to_tsvector('english'::regconfig, body)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no

4.清理数据

omm=# drop table cj.t1;
DROP TABLE
omm=# drop schema cj cascade;
DROP SCHEMA