开发者学堂课程【PostgreSQL 实战进阶：认识PostgreSQL 中与众不同的索引（二）】学习笔记，与课程紧密联系，让用户快速学习知识。
课程地址：https://developer.aliyun.com/learning/course/112/detail/1906
认识 PostgreSQL 中与众不同的索引（二）
内容介绍：
一、PostgreSQL 中索引的总体介绍
二、BRIN 索引的使用案例
三、数组上建立 GIN 索引
四、快速查找某 IP 所属地区
五、让 like“%XXX%”中的“XXX”进入索引
六、GIN+JSON 数据类型形成用户画像
五、让 like“%XXX%”中的“XXX”进入索引
1、创建表
create table test01(id int,t text);
insert into test01 select seq,seq from generate_ series (1,1000000) as seq;
//创建表，并插入100万条测试数据
2、收集统计信息
analyze test01;
3、查询
explain analyze select*from test01 where t like'%99999%';
查询结果显示：
由于表上没有索引，只能进行全表扫描，因此执行时间至少要100-300毫秒，且此时其访问的方式为B型，若把B型关掉，执行速度会更慢。
4、like“%XXX%”走索引的方式
由于要寻找like后面中间的数据，对于其他数据库而言，这是一件无法实现的事情，但 PostgreSQL 却可以完成。
create extension pg_trgm;
//首先 PostgreSQL 本身有一个模糊主件的插件，应先安装该插件
create index idx_test01_t on test01 using gin(t gin_trgm_ops);
//创建 GIN 索引。
建立索引时，gin 索引中有一个叫作 t gin_trgm_ops 的算法，该算法其实由前面安装的插件提供的
explain analyze select*from test01 where t like'%99999%';
再次查询，查询结果显示：
本次执行时间紧需要2ms。
六、GIN+JSON 数据类型形成用户画像
1、用户画像系统
（1）建立标签模型
以下是一个比较简单的标签模型：
其中分了四类：职业、爱好、学历、性格，职业包括农民、工人、IT 工程师、理发师等，爱好又简单分为游泳、乒乓球、网球等，学历分为小学、中学、大学、硕士、博士等，性格又分为外向、内向、谨慎、粗心、浮躁、自信等。
（2）建表
CREATE TABLE user_tag(uid serial primary key,tag jsonb);
//首先，第一个字段是 uid，即用户的 id，第二个字段是 tag（打标签），并将其设定为 jsonb 的数据类型
（3）造数据
①要看用户画像的效果，还需要造些数据，在造数据之前，需要编写一些辅助的函数来造数据，辅助函数如下：
CREATE OR REPLACE FUNCTION f_random_attr(attr text[]，max_attr int)
RETURNS text[ ] AS $$
DECLARE
i integer := 0;
r integer := 0;
res text[];
v text;
l integer;
num integer;
BEGIN
num :=(random()*max_attr)::int;
IF num < 1 THEN
num := 1;
END IF;
l :=array_length(attr，1);
WHILE i
r:=round(random()*l)::int +1;
v:=attr[r];
IF res @> array[v] THEN
continue;
ELSE
res :=array_append(res, v);
i := i + 1;
END IF;
END LOOP;
return res;
END;
②造数据:
INSERT INTO user_tag(uid,tag)
SELECT seq,
json_build_object(
'职业'，f_random_attr(array['农民’,'工人'，'IT工程师'，'理
发师'，'医生’，'老师'，'美工'，'律师'，'公务员'，"官员'].1),
'爱好'，f_random_attr(array['游泳’，'乒乓球'，'羽毛球'，'网球'，'爬山'，'高尔夫球'，'滑雪'，'爬山'，'旅游']，5),
'学历'，f_random_attr(array['无学历小学’，'初中'，'高中'，
'中专'，'专科'，'本科'，'硕士'，'博士'], 1),
'性格'，f_frandom_attr(array['外向'，'内向'，'谨慎'，'稳重'，'细心'，'粗心'，'浮躁'，'自信'],3))::jsonb
FROM generate_series(1, 10000) as t(seq);
造出10万条记录的数据，而由于是造的数据，因此标签数据是随机生成的，
可以发现人的性格、爱好可以有多种组合。
（4）创建 GIN索引
CREATE INDEX idx_user_tag_tag on user_tag using gin(tag);
（5）查询
①查询性格为“外向”和“细心”的老师
select*from user_tag where tag @> '{"性格":["外向","细心"]} and tag @> '{"职业":["老师"]};
即查询包含“性格”为“外向”和“细心”，“职业”是“老师”的用户，以下为部分查询结果：
②查询性格为“外向”和“细心”而又喜欢“滑雪”和“游泳”的医生
*select*from user tag where tag @> '{"性格”:["外向"，"细心"]}' and tag@> '{"职业":["医生"]}' and tag @> '{"爱好":["滑雪"，"游泳"]}';
以下为部分查询结果：
查询其执行计划结果如下：
也就是说，当给用户打了标签之后，输入一个 S QL 就可以查出具有我们需要的特征的那部分人来做数据分析。
这就是利用 GIN+JSON 数据类型形成用户画像的简单例子，但是这个例子并不特别高效，只是为了学习下 GIN+JSON 数据的使用。
2、更为实用的例子
阿里云官网上有更为使用的例子，
如 PostgreSQL 案例精选2-图像识别、人脸识别、相似特征检索、相似人群圈选，网址：
http://developer.aliyun.com/article/747642
这篇文章中的例子则是完成一些更为具体的任务，同样是使用 PostgreSQL 的一些索引，当然在阿里云中是通过安装 RDS PG 的 pase 插件实现对图像等内容的快速搜索。
再如阿里云一些 RDS for PostgreSQL varbitx 插件与实时画像有关的文章:
http://developer.aliyun.com/article/75335
http://help.aliyun.com/docunent_detail/142340.html
这些是非常大规模的数据，是具有很强的实战意义的使用画像的例子，阿里云为之提供了一些被运算的一些插件来辅助使得用画像更快。
阿里云 RDS PG 提供了很多插件，它们结合 PG 完成内置的索引，可以实现很多功能。
以下为阿里云 PostgreSQL 支持的部分插件：
