首页 > 数据库 >lightdb/postgresql----JSON/JSONB类型、函数及全文检索的支持以及性能表现

lightdb/postgresql----JSON/JSONB类型、函数及全文检索的支持以及性能表现

时间:2022-12-10 16:36:27浏览次数:72  
标签:postgresql lightdb name random ceil 全文检索 table GIN jsonb

create table test_ft(id int4,arry VARCHAR[],content1 jsonb,body text);
insert into test_ft values(1,ARRAY [ 'x', 'y' ],'{
"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46balabala",
"name": "james",
"is_active": true,
"company": "Oracle",
"address": "178 Howard Place, Gulf, Washington, 702",
"registered": "2009-11-07T08:53:22 +08:00",
"latitude": 19.793713,
"longitude": 86.513373,
"tags": [
"enim",
"aliquip",
"qui"
]}','postgresql支持两种json数据类型:json和jsonb,而两者唯一的区别在于效率,json是对输入的完整拷贝,使用时再去解析,所以它会保留输入的空格,重复键以及顺序等。而jsonb是解析输入后保存的二进制,它在解析时会删除不必要的空格和重复的键,顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别:json类型存储快,使用慢,jsonb类型存储稍慢,使用较快。');

查询JSON的某个key,如下:

select id,arry, content1->'name',substr(body,1,100) from test_ft t
where t.arry @>'{"x"}'
and t.content1 @>'{"name":"james"}'

查询JSON中某个数组类型属性的某个索引等于n的记录:

select id,arry, content1->'name',substr(body,1,100) from test_ft t
where t.arry @>'{"x"}'
and t.content1 @>'{"name":"james"}' and t.content1->'tags'->>1='aliquip'

如此以来,所有的key都可以查询了。


index on jsonb

  gin提供了对jsonb的原生支持。 什么时候需要在json字段上建立索引呢?全文检索的时候。如下所示:

  

lightdb/postgresql----JSON/JSONB类型、函数及全文检索的支持以及性能表现_ide

   每套房子在每个标签上都有一个属性,这些属性既可以单独每列存储,也可以存储在JSON中(mongodb和es的通常做法)。同时,搜索文本框作为全文检索字段,为了高精确率(因为很多术语、地点、企业名并非标准分词,见​​scws​​),一般需要维护可配置的词法库。

  关于GIN的实现以及原理,可以参考​​postgresql查询优化之GIN(Generalized Inverted Index)索引与全文检索​​。怎么样在JSONB上创建GIN索引呢?

  需要先安装uuid插件,​​LightDB​​内置集成了uuid。

create table wide_table(id bigserial primary key,val jsonb,c_name varchar(50),c_desc varchar(50),c_en_name varchar(100));

insert into wide_table_5000w select x,('{"d1":' || ceil(random()*(11-1)+0) || ',' ||'"d2":' || ceil(random()*(11-1)+0) || ',' ||'"d3":' || ceil(random()*(9-1)+0) || ',' ||'"d4":' || ceil(random()*(21-1)+0) || ',' ||'"d5":' || ceil(random()*(31-1)+0) || ',' ||'"d6":' || ceil(random()*(4-1)+0) || ',' ||'"d7":' || ceil(random()*(8-1)+0) || ',' ||'"d8":' || ceil(random()*(5-1)+0) || ',' ||'"d9":' || ceil(random()*(11-1)+0) || ',' ||'"d10":' || ceil(random()*(4-1)+0) || ',' ||'"d11":' || ceil(random()*(5-1)+0) || ',' ||'"d12":' || ceil(random()*(7-1)+0) || ',' ||'"d13":' || ceil(random()*(6-1)+0) || ',' ||'"d14":' || ceil(random()*(5-1)+0) || ',' ||'"d15":' || ceil(random()*(21-1)+0) || ',' ||'"d16":' || ceil(random()*(16-1)+0) || ',' ||'"d17":' || ceil(random()*(51-1)+0) || ',' ||'"d18":' || ceil(random()*(33-1)+0) || ',' ||'"d19":' || ceil(random()*(31-1)+0) || '}'::text)::jsonb,
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(16-1)+0) as int)),
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(32-1)+0) as int)),
substr(uuid_generate_v4()::text,1,cast(ceil(random()*(50-1)+0) as int)) from generate_series(1,50000000) x;
CREATE INDEX gin_idx_wide_table ON wide_table USING GIN (jsonb);

走主键索引的执行计划: 

lightdb/postgresql----JSON/JSONB类型、函数及全文检索的支持以及性能表现_ide_02

走GIN索引的执行计划:

 

lightdb/postgresql----JSON/JSONB类型、函数及全文检索的支持以及性能表现_json_03

但是如果查询条件里面某个值要符合多个,比如d15 in ('3','2','1'),GIN索引就爱莫能助了。这在即席查询中几乎使得GIN过于局限,进而发挥空间受限。

标签:postgresql,lightdb,name,random,ceil,全文检索,table,GIN,jsonb
From: https://blog.51cto.com/zhjh256/5927777

相关文章