一．gin索引需要安装第三方插件
yum install postgresql96-contrib -- 安装插件
find / -name extension --可以看到btree_gin.control存在
create extension btree_gin; -- 添加索引
二.测试数据基本属性介绍
总共使用3个表，表结构和数据量完全一致。
表数据量：10522369
表字段：id ,basic_acc_no,id_card,name,sex,telephone,json_t
1）索引的配置情况：
basic_account_info_al -- btree
basic_account_info_al2 --gin
basic_account_info_al3 -- btree multi
basic_account_info_al 单列索引 id，basic_acc_no，name,json_t
basic_account_info_al2 gin索引 (id,basic_acc_no,id_card,name),(json_t)
basic_account_info_al3 复合索引 (id,basic_acc_no),(name,id)(json_t,id)
basic_account_info_al 表达式索引 (json_t->>id)
basic_account_info_al2表达式索引 ((json_t->>'id'))
三．测试结果
1.唯一值属性：索引字段都是唯一 id，basic_acc_no
查询语句
explain analyse select * from basic_account_info_al2 where id = 29699221 ;
explain analyse select * from basic_account_info_al where id = 29699221 ;
explain analyse select * from basic_account_info_al3 where id = 29699221 ;
explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK2990134' ;
explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK2990134' ;
explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK2990134' ;
explain analyse select * from basic_account_info_al2 where basic_acc_no = 'XFK9780134' and id = 29699221;
explain analyse select * from basic_account_info_al where basic_acc_no = 'XFK9780134' and id = 29699221;
explain analyse select * from basic_account_info_al3 where basic_acc_no = 'XFK9780134' and id = 29699221;
explain analyse select * from basic_account_info_al2 where id = 29699221 and basic_acc_no = 'XFK9780134' ;
explain analyse select * from basic_account_info_al where id = 29699221 and basic_acc_no = 'XFK9780134' ;
explain analyse select * from basic_account_info_al3 where id = 29699221 and basic_acc_no = 'XFK9780134' ;
2.重复值属性： name是有重复值的。
explain analyse select * from basic_account_info_al where name ='张燕洪';
explain analyse select * from basic_account_info_al3 where name ='张燕洪';
explain analyse select *from basic_account_info_al2 where name ='张燕洪';
explain analyse select * from basic_account_info_al2 where id = 24426014 and name = '周杨' ;
explain analyse select * from basic_account_info_al where id = 24426014 and name = '周杨' ;
explain analyse select * from basic_account_info_al3 where id = 24426014 and name = '周杨' ;
explain analyse select * from basic_account_info_al2 where name = '周杨' and id = 24426014 ;
explain analyse select * from basic_account_info_al where name = '周杨' and id = 24426014 ;
explain analyse select * from basic_account_info_al3 where name = '周杨' and id = 24426014 ;
3.jsonb属性
create index inx_gin_json on basic_account_info_al2 using gin (json_t);
create index inx_btree_json on basic_account_info_al (json_t);
create index inx_btree_2_js on basic_account_info_al3 (json_t,id );
explain analyse select * from basic_account_info_al where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';
explain analyse select * from basic_account_info_al2 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';
explain analyse select * from basic_account_info_al3 where json_t ='{"id": 21782879, "sex": 0, "name": "刘乐典"}';
explain analyse select * from basic_account_info_al WHERE json_t @> '{"id": 21782879}';
explain analyse select * from basic_account_info_al2 WHERE json_t @> '{"id": 21782879}';
explain analyse select * from basic_account_info_al3 WHERE json_t @> '{"id": 21782879}';
explain analyse select * from basic_account_info_al where (json_t->>id)= '24426014' ;
explain analyse select * from basic_account_info_al2 where (json_t->>id)= '24426014' ;
explain analyse select * from basic_account_info_al3 where (json_t->>id)='24426014' ;
4.jsonb表达式索引
查询条件 表名 查询时使用的索引名称 查询时间（5次平均）/ms
(json_t->>id)= '24426014' basic_account_info_al inx_json_id 0.040
basic_account_info_al3 inx_json_id_2 0.039
explain analyse select * from basic_account_info_al where (json_t->>id)= '24426014' ;
explain analyse select * from basic_account_info_al2 where (json_t->>id)= '24426014' ;
四．获相同的结果使用Jsonb与btree对比
jsonb支持两种特有的GIN索引jsonb_ops和jsonb_path_ops。 jsonb_ops调用gin_extract_jsonb函数生成key，每个键和值都作为一个单独的索引项。而jsonb_path_ops使用函数gin_extract_jsonb_path抽取：只为每个值创建一个索引项。{“foo”:{“bar”,”baz”}}, jsonb_ops生成3个索引项，jsonb_path_ops由foo,bar,baz组合一个hash值作为一个索引项。jsonb_path_ops索引要比jsonb_ops的小很多，性能上也会有所提升。
create index inx_gin_patn_json ON public.basic_account_info_al4 USING gin (json_t jsonb_path_ops); -- jsonb_path_ops
create index inx_gin_json on basic_account_info_al2 using gin (json_t); --jsonb_ops
1.精确查询
2.范围查询
下表显示了gin索引对于jsonb数据类型可使用的操作符。
名称 索引数据类型 可索引操作符
jsonb_ops jsonb ? ?& ?| @>
json_path_ops jsonb @>
注：? ?& ?| 索引key是否包含在jsonb中
对于范围(json_t->>'id')< 20000079，这样的条件 gin索引不起作用， 这里采用表达式索引方式，查询条件的两边数据类型相同才可以做索引查询，否则全表扫描。
CREATE INDEX inx_json_id_2 ON public.basic_account_info_al2 USING btree (((json_t->>'id')::int));
总结： 当仅有一个条件查询时，gin索引与btree索引的性能差异不大，但有多个条件查询时，gin,btree单
列索引没有btree复合索引的性能高。jsonb是以二进制格式存储且不保证键的顺序。可以使用表达式索引指定到jsonb的具体键值，但是如果不能提前知道查询数据中的哪个键，确定定义GIN索引和使用@>（或者其他有利于索引的操作符）查询。
五．jsonb添加数据属性
例如：
{"id":20000241,"name":"陈敏","sex":1} -> {"age":"18","id":20000241,"name":"陈敏","sex":1}
一旦创建了索引，就不需要进一步的干预：当表被修改时，系统将更新索引，当执行计划认为使用索引比顺序的表扫描更有效的时候，它会使用索引。
UPDATE basic_account_info_al4 SET json_t = json_t || '{"age":"18"}'::jsonb; -- 更新语句
gin索引名称 索引方式 修改前大小 修改后大小 带索引更新时间
inx_gin_patn_json jsonb_path_ops 574M 615M 643561.004 ms
inx_gin_json jsonb_ops 665M 695M 时间过长超过1h
jsonb_ops方式建立的索引大量更新时，执行时间太长。当插入更新时gin索引比较慢，如果要向一张大表中插入大量数据时，最好先把gin索引删除，插入数据后再重建索引。
当json_t为{"id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间
130372.955 ms
当json_t为{"age":"18","id":20000241,"name":"陈敏","sex":1} 数据量为10522369 创建gin索引时间
148971.011 ms
