一、全文索引¶

1.1 介绍¶

PostgreSQL内置了全文检索功能，但内置的功能只能检索英文。可以配置一些插件如zhparser（https://github.com/amutu/zhparser）也可以对中文进行全文检索。

1.2 全文检索初步使用¶

PostgreSQL把长文本分解成很多token的集合，这个token集合叫tsvector，代表了文档，搜索实际上是在 token集合中进行的

比如

select 'we love postgresql database'::tsvector; tsvector

把文本转换成tsvector类型，实际上就是一个分词的过程

PostgreSQL也提供了函数to_tsvector来实现这个分词过程：

select to_tsvector('we love postgresql database');

PostgreSQL增加了一个检索条件的类型“tsquery”，tsquery是一个由简单逻辑运行符号组成的字符串

select 'postgresql & love'::tsquery;
select to_tsquery('postgresql & love');

所以： PostgreSQL全文检索的搜索过程实际上使用一个tsvector和tsquery进行匹配，tsvector代表了文档，而 tsquery代表了检索条件，匹配的运算符是“@@”，示例如下

select 'we love postgresql database'::tsvector @@ 'postgresql & love'::tsquery; 
select 'we love postgresql database'::tsvector @@ 'mysql | love'::tsquery; select 'we love postgresql database'::tsvector @@ 'mysql & love'::tsquery;

1.3 使用全文检索实际案例¶

建一张存博客文章的表

create table myblog( id int primary key, content text, content_tsv tsvector ); 

表中的字段“content_tsv”是把博客内容分词后的tsvector类型的内容

造100万条记录

insert into myblog(id, content) select seq, 'PostgresQL'||seq|| ' MySQL'||(seq+10) from generate_series(1, 1000000) as seq;

把博客内容进行分词处理

update myblog set content_tsv = to_tsvector(content);

查询文本中存在“Postgresql1323”字符串

select * from myblog where content_tsv @@ 'postgresql1323'::tsquery;
explain select * from myblog where content_tsv @@ 'postgresql1323'::tsquery;

全表扫描,耗时会比较慢这样的全文检索就没有意义了。PostgreSQL 是通过建GIN索引来加快全文检索速度的，建GIN索引的命令如下

CREATE INDEX idx_myblog_content_tsv ON myblog USING GIN(content_tsv);
select * from myblog where content_tsv @@ 'postgresql1323'::tsquery;
explain select * from myblog where content_tsv @@ 'postgresql1323'::tsquery;

1.4 使用zhparser做中文全文检索¶

zhparser可以在GitHub上下载，目录为“https://github.com/amutu/zhparser”。

安装开发报：安装插件前一般需要安装PostgreSQL数据库的开发包,如对于PostgreSQL10版本 yum install postgresql15-devel

安装zhparser之前需要先安装scws，安装scws的方法如下

wget -q http://www.xunsearch.com/scws/down/scws- 1.2.3.tar.bz2 
tar xvf scws-1.2.3.tar.bz2
cd scws-1.2.3
./configure
make
make install

安装zhparser

git clone https://github.com/amutu/zhparser.git cd zhparser
make
make install

使用zhparser案例

CREATE EXTENSION zhparser;
CREATE TEXT SEARCH CONFIGURATION testzhcfg (PARSER = zhparser);
ALTER TEXT SEARCH CONFIGURATION testzhcfg ADD MAPPING FOR n,v,a,i,e,l WITH simple;

testzhcfg”，后续该配置会作为参数使用到函数

“n”“v”“a”“i”“e”“l”这几个字母分别表示一种通过\dFp+zhparser 可以查看含义博客的示例演示zhparser的使用方法“to_tsvector”和“to_tsquery”中\

Token策略，只启用了这几种Token Mapping

create table myblog( id int primary key, content text, content_tsv tsvector );
insert into myblog(id, content) values('1', '中国南京市长江 大桥');
insert into myblog(id, content) values('2', '是一种特性非常 齐全的自由软件的对象-关系型数据库管理 系统'); 
insert into myblog(id, content) values('3', '是以加州大学计 算机系开发的POSTGRES'); 
insert into myblog(id, content) values('4', '4.2版本为基础 的对象关系型数据库管理系统'); 
insert into myblog(id, content) values('5', 'PostgreSQL是 一个功能非常强大的、源代码开放的客户/服务器关系 型数据库管理系统 （RDBMS）'); 
insert into myblog(id, content) values('6', 'PostgreSQL是 一个非 常健壮的软件包，有很多在大型商业RDBMS中所具有的特性');
insert into myblog(id, content) values('7', '包括事务、子选 择、触发器、视图、外键引用完整性和复杂锁定功能');
insert into myblog(id, content) values('8', '全球开发组今天 宣布，世界上功能最为强大的开源数据库发 布');
update myblog set content_tsv = to_tsvector('testzhcfg', content); CREATE INDEX idx_myblog_content_tsv ON myblog USING GIN(content_tsv)

使用中文的全文检索

select * from myblog where content_tsv @@ to_tsquery('testzhcfg', '南京市');

中文的全文检索中也可以查询英文单词

select id,content from myblog where content_tsv @@ to_tsquery ('testzhcfg', 'postgresql');

二、并行查询功能¶

并行查询利用多CPU和多核的能力，可以大大缩短单个SQL对一些大数据的查询和处理时间。

2.1 并行查询相关的配置参数¶

PostgreSQL数据库是通过以下参数来控制并行查询的开启和关闭以及并行度的

dynamic_shared_memory_type:必须被设置为除none之外的值。并行查询要求动态共享内存以便在合作的进程之间传递数据

max_worker_processes:设置整个数据库实例层面允许支持的最大后台工作进程数，默认值为8 max_parallel_workers：设置整个数据库实例层面允许用做并行的后台工作进程数 max_parallel_workers_per_gather：设置某个并行操作允许并行度要小于max_parallel_workers min_parallel_table_scan_size：表的大小小于此值，则不会走并行。默认值为8MB min_parallel_index_scan_size：索引扫描的大小小于此值，则不会走并行。默认值为512KB。

一个SQL执行时是否使用并行计算，取决于CBO计算的结果，即选择成本最低的方法。主要用配置参数 parallel_setup_cost和parallel_tuple_cost估算成本，如果非并行的执行计划成本低于并行的成本，则不使用并行

可否强制使用并行？ force_parallel_mode：是否强制开启并行，一般用于并行测试目的。OLTP生产系统开启此参数需要慎重

2.2 支持的并行操作介绍¶

并行顺序扫描：即全表扫描（sequential scan）可以走到并行。
并行索引扫描：即索引扫描（index scan）可以走到并行。
并行index-only扫描：即index only扫描可以走到并行。
并行bitmap heap扫描：即bitmap heap扫描可以走到并行。
并行聚合：聚合操作如count()、sum等可以走到并行。
Nested loop并行：即Nested loop多表关联可以走并行。
Merge join并行：即Merge join多表关联可以走并行。
HashJoin并行：即Hash Join多表关联可以走并行。
并行create index：对Btree索引可以并行创建
CREATE TABLE...AS：可以支持并行。

PostgreSQL 全文检索与并行查询实践