VastbaseG100

表和索引

搜索表

在不使用索引的情况下也可以进行全文检索。

• 一个简单查询：将body字段中包含america的每一行打印出来。

  vastbase=# DROP SCHEMA IF EXISTS tsearch CASCADE; 
   
  vastbase=# CREATE SCHEMA tsearch; 
   
  vastbase=# CREATE TABLE tsearch.pgweb(id int, body text, title text, last_mod_date date); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(1, 'China, officially the People''s Republic of China (PRC), located in Asia, is the world''s most populous state.', 'China', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(2, 'America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley.', 'America', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(3, 'England is a country that is part of the United Kingdom. It shares land borders with Scotland to the north and Wales to the west.', 'England', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(4, 'Australia, officially the Commonwealth of Australia, is a country comprising the mainland of the Australian continent, the island of Tasmania, and numerous smaller islands.', 'Australia', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(5, 'Russia, also officially known as the Russian Federation, is a sovereign state in northern Eurasia.', 'Russia', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(6, 'Japan is an island country in East Asia.', 'Japan', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(7, 'Germany, officially the Federal Republic of Germany, is a sovereign state and federal parliamentary republic in central-western Europe.', 'Germany', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(8, 'France, is a sovereign state comprising territory in western Europe and several overseas regions and territories.', 'France', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(9, 'Italy officially the Italian Republic, is a unitary parliamentary republic in Europe.', 'Italy', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(10, 'India, officially the Republic of India, is a country in South Asia.', 'India', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(11, 'Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.', 'Brazil', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(12, 'Canada is a country in the northern half of North America.', 'Canada', '2010-1-1'); 
   
  vastbase=# INSERT INTO tsearch.pgweb VALUES(13, 'Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.', 'Mexico', '2010-1-1'); 
   
  vastbase=# SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector('english', body) @@ to_tsquery('english', 'america'); 
  id |                                                          body                                                           |  title   
  ----+-------------------------------------------------------------------------------------------------------------------------+--------- 
  2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley. | America 
  12 | Canada is a country in the northern half of North America.                                                              | Canada 
  13 | Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.              | Mexico 
  11 | Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.   | Brazil 
  (4 rows)
  

  像America这样的相关词也会被找到，因为这些词都被处理成了相同标准的词条。

  上面的查询指定english配置来解析和规范化字符串。当然也可以省略此配置，通过default_text_search_config进行配置设置：

  vastbase=# SHOW default_text_search_config; 
  default_text_search_config  
  ---------------------------- 
  pg_catalog.english 
  (1 row) 
   
  vastbase=# SELECT id, body, title FROM tsearch.pgweb WHERE to_tsvector(body) @@ to_tsquery('america'); 
  id |                                                          body                                                           |  title   
  ----+-------------------------------------------------------------------------------------------------------------------------+--------- 
  11 | Brazil, officially the Federative Republic of Brazil, is the largest country in both South America and Latin America.   | Brazil 
  2 | America is a rock band, formed in England in 1970 by multi-instrumentalists Dewey Bunnell, Dan Peek, and Gerry Beckley. | America 
  12 | Canada is a country in the northern half of North America.                                                              | Canada 
  13 | Mexico, officially the United Mexican States, is a federal republic in the southern part of North America.              | Mexico 
  (4 rows)
  

• 一个复杂查询：检索出在title或者body字段中包含north和america的最近10篇文档：

  vastbase=# SELECT title FROM tsearch.pgweb WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('north & america') ORDER BY last_mod_date DESC LIMIT 10; 
  title   
  -------- 
  Mexico 
  Canada 
  (2 rows)
  

  为了清晰，举例中没有调用coalesce函数在两个字段中查找包含NULL的行。

  以上例子均在没有索引的情况下进行查询。对于大多数应用程序来说，这个方法很慢。因此除了偶尔的特定搜索，文本搜索在实际使用中通常需要创建索引。

创建索引

为了加速文本搜索，可以创建GIN索引。

vastbase=# CREATE INDEX pgweb_idx_1 ON tsearch.pgweb USING gin(to_tsvector('english', body));

to_tsvector()函数有两个版本。只输一个参数的版本和输两个参数的版本。只输一个参数时，系统默认采用default_text_search_config所指定的分词器。

请注意：创建索引时必须使用to_tsvector的两参数版本。只有指定了分词器名称的全文检索函数才可以在索引表达式中使用。这是因为索引的内容必须不受default_text_search_config的影响，否则索引内容可能不一致。由于default_text_search_config的值可以随时调整，从而导致不同条目生成的tsvector采用了不同的分词器，并且没有办法区分究竟使用了哪个分词器。正确地转储和恢复这样的索引也是不可能的。

因为在上述创建索引中to_tsvector使用了两个参数，只有当查询时也使用了两个参数，且参数值与索引中相同时，才会使用该索引。也就是说，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用索引，但WHERE to_tsvector(body) @@ 'a & b'不能使用索引。这确保只使用这样的索引——索引各条目是使用相同的分词器创建的。

索引中的分词器名称由另一列指定时可以建立更复杂的表达式索引。例如：

vastbase=# CREATE INDEX pgweb_idx_2 ON tsearch.pgweb USING gin(to_tsvector('ngram', body));

其中body是pgweb表中的一列。当对索引的各条目使用了哪个分词器进行记录时，允许在同一索引中存在混合分词器。在某些场景下这将是有用的。例如，文档集合中包含不同语言的文档时。再次强调，打算使用索引的查询必须措辞匹配，例如，WHERE to_tsvector(config_name, body) @@ 'a & b'与索引中的to_tsvector措辞匹配。

索引甚至可以连接列：

vastbase=# CREATE INDEX pgweb_idx_3 ON tsearch.pgweb USING gin(to_tsvector('english', title || ' ' || body));

另一个方法是创建一个单独的tsvector列控制to_tsvector的输出。下面的例子是title和body的连接，当其它是NULL的时候，使用coalesce确保一个字段仍然会被索引：

vastbase=# ALTER TABLE tsearch.pgweb ADD COLUMN textsearchable_index_col tsvector; 
vastbase=# UPDATE tsearch.pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

然后为加速搜索创建一个GIN索引：

vastbase=# CREATE INDEX textsearch_idx_4 ON tsearch.pgweb USING gin(textsearchable_index_col);

现在，就可以执行一个快速全文搜索了：

vastbase=# SELECT title  
FROM tsearch.pgweb  
WHERE textsearchable_index_col @@ to_tsquery('north & america')  
ORDER BY last_mod_date DESC  
LIMIT 10;  
 
 title   
-------- 
 Canada 
 Mexico 
(2 rows)

相比于一个表达式索引，单独列方法的一个优势是：它没有必要在查询时明确指定分词器以便能使用索引。正如上面例子所示，查询可以依赖于default_text_search_config。另一个优势是搜索比较快速，因为它没有必要重新利用to_tsvector调用来验证索引匹配。表达式索引方法更容易建立，且它需要较少的磁盘空间，因为tsvector形式没有明确存储。

索引使用约束

下面是一个使用索引的例子：

vastbase=# create table table1 (c_int int,c_bigint bigint,c_varchar varchar,c_text text) with(orientation=row); 
 
vastbase=# create text search configuration ts_conf_1(parser=POUND); 
vastbase=# create text search configuration ts_conf_2(parser=POUND) with(split_flag='%'); 
 
vastbase=# set default_text_search_config='ts_conf_1'; 
vastbase=# create index idx1 on table1 using gin(to_tsvector(c_text)); 
 
vastbase=# set default_text_search_config='ts_conf_2'; 
vastbase=# create index idx2 on table1 using gin(to_tsvector(c_text)); 
 
vastbase=# select c_varchar,to_tsvector(c_varchar) from table1 where to_tsvector(c_text) @@ plainto_tsquery('￥#@……&**')   and to_tsvector(c_text) @@  plainto_tsquery('某公司 ')   and c_varchar is not null order by 1 desc limit 3;

该例子的关键点是表table1的同一个列c_text上建立了两个gin索引：idx1和idx2，但这两个索引是在不同default_text_search_config的设置下建立的。该例子和同一张表的同一个列上建立普通索引的不同之处在于：

• gin索引使用了不同的parser（即分隔符不同），那么idx1和idx2的索引数据是不同的；

• 在同一张表的同一个列上建立的多个普通索引的索引数据是相同的。

因此当执行同一个查询时，使用idx1和idx2查询出的结果是不同的。

使用约束

通过上面的例子，索引使用满足如下条件时：

• 在同一个表的同一个列上建立了多个gin索引；

• 这些gin索引使用了不同的parser（即分隔符不同）；

• 在查询中使用了该列，且执行计划中使用索引进行扫描；

为了避免使用不同gin索引导致查询结果不同的问题，需要保证在物理表的一列上只有一个gin索引可用。

pg_zhtrgm

创建扩展pg_zhtrgm，可以简化全文索引的使用方式，并且该扩展不仅支持对英文字符全文检索，还允许对中文字符进行全文检索。

注意，若未使用安装程序安装Vastbase，为自己初始化实例，initdb时指定的 -E 和–locale参数对应的编码规则必须一致，否则将无法使用pg_zhtrgm插件。

vastbase=# create extension pg_zhtrgm;

创建中文数据表如下：

vastbase=# create table t_full_text(id int, info text);
vastbase=# insert into t_full_text values (1, ' Vastbase是一种特性非常齐全的自由软件的对象-关系型数据库管理系统（ORDBMS），Vastbase支持大部分的SQL标准并且提供了很多其他现代特性，如复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等。同样，PostgreSQL也可以用许多方法扩展，例如通过增加新的数据类型、函数、操作符、聚集函数、索引方法、过程语言等。另外，因为许可证的灵活，任何人都可以以任何目的免费使用、修改和分发PostgreSQL。');

在此表上创建以pg_zhgrtm提供的gin_zhtrgm_ops、gist_zhtrgm_ops操作符类所支持的gin索引或gist索引，该索引创建不再需要借助to_tsvector函数，同样地，使用该索引进行查询时也不再需要借助to_tsquery函数。

create index idx_full_text on t_full_text using gin(info gin_zhtrgm_ops);
create index idx_full_text on t_full_text using gist(info gist_zhtrgm_ops);

此类全文索引仅支持模糊查询，不支持等值匹配。对应地操作符如下：

那么，可以很方便地进行单字、词语、短句的全文搜索：

select * from t_full_text where info like '%函%';
select * from t_full_text where info like '%视图%';
select * from t_full_text where info like '%查询%' and info not like '%外键%';
select * from t_full_text where info like '%Vastbase是一种特性非常齐全的自由软件的对象%';