学习笔记
tsvector、tsquery
tsvector 拥有位置的词汇甚至可以用一个权来标记，反映文档结构，这个权可以是A，B，C或D。默认的是D，因此输出中不会出现
tsvector ---------------------------- 'a':1A 'cat':5 'fat':2B,4C (1 row) omm=# SELECT 'fat & rat'::tsquery; tsquery --------------- 'fat' & 'rat' (1 row) 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=# \dF List of text search configurations Schema | Name | Description ------------+------------+--------------------------------------- pg_catalog | danish | configuration for danish language pg_catalog | dutch | configuration for dutch language pg_catalog | english | configuration for english language pg_catalog | finnish | configuration for finnish language pg_catalog | french | configuration for french language pg_catalog | german | configuration for german language pg_catalog | hungarian | configuration for hungarian language pg_catalog | italian | configuration for italian language pg_catalog | ngram | ngram configuration pg_catalog | norwegian | configuration for norwegian language pg_catalog | romanian | configuration for romanian language pg_catalog | russian | configuration for russian language pg_catalog | simple | simple configuration pg_catalog | spanish | configuration for spanish language pg_catalog | portuguese | configuration for portuguese language pg_catalog | pound | pound configuration pg_catalog | zhparser | zhparser configuration pg_catalog | swedish | configuration for swedish language pg_catalog | turkish | configuration for turkish language (19 rows) omm=# omm=# show default_text_search_config; default_text_search_config ---------------------------- pg_catalog.english (1 row)复制
GIN索引
连接列的索引
omm=# CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector(‘english’, body));
CREATE INDEX
omm=# CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector(‘english’, title || ’ ’ || body));
CREATE INDEX
omm=# \d+ tsearch.pgweb
Table “tsearch.pgweb”
Column | Type | Modifiers | Storage | Stats target | Description
---------------±--------±----------±---------±-------------±------------
id | integer | | plain | |
body | text | | extended | |
title | text | | extended | |
last_mod_date | date | | plain | |
Indexes:
“pgweb_idx_1” gin (to_tsvector(‘english’::regconfig, body)) TABLESPACE pg_default
“pgweb_idx_3” gin (to_tsvector(‘english’::regconfig, (title || ’ '::text) || body)) TABLESPACE pg_default
Has OIDs: no
Options: orientation=row, compression=no
课后作业
1.用tsvector @@ tsquery和tsquery @@ tsvector完成两个基本文本匹配
omm=# SELECT 'good good study day day up'::tsvector; tsvector --------------------------- 'day' 'good' 'study' 'up' (1 row) omm=# SELECT 'you & live'::tsquery; tsquery ---------------- 'you' & 'live' (1 row) omm=# SELECT 'good good study day day up'::tsvector @@ 'study & day'::tsquery AS RESULT; result -------- t (1 row)复制
omm=# SELECT 'you & live'::tsquery @@ 'Cease to struggle and you cease to live'::tsvector AS RESULT; result -------- f (1 row)复制
2.创建表且至少有两个字段的类型为 text类型，在创建索引前进行全文检索
omm=# create table c
omm-# (
omm(# id integer,
omm(# content1 text,
omm(# content2 text
omm(# );
CREATE TABLE
omm=# insert into c values
omm-# (1,‘good good study day day up’,‘hello’),
omm-# (2,‘Cease to struggle and you cease to live’,‘world’);
INSERT 0 2
omm=# select * from c;
id | content1 | content2
----±----------------------------------------±---------
1 | good good study day day up | hello
2 | Cease to struggle and you cease to live | world
(2 rows)
omm=# select * from c where to_tsvector(content1) @@ to_tsquery(‘good’);
id | content1 | content2
----±---------------------------±---------
1 | good good study day day up | hello
(1 row)
omm=# select * from c where to_tsvector(content1 || ’ ’ || content2) @@ to_tsquery(‘cease & world’);
id | content1 | content2
----±----------------------------------------±---------
2 | Cease to struggle and you cease to live | world
(1 row)
3.创建GIN索引
omm=# create index content1_idx on c using gin(to_tsvector(‘english’, content1));
CREATE INDEX
omm=# \d c
Table “public.c”
Column | Type | Modifiers
----------±--------±----------
id | integer |
content1 | text |
content2 | text |
Indexes:
“content1_idx” gin (to_tsvector(‘english’::regconfig, content1)) TABLESPACE pg_default
4.清理数据
omm=# drop table c;
DROP TABLE
