postgresql jsonb + 索引 + 分区 + 测试

-- 创建表

DROP TABLE IF EXISTS tba;

CREATE TABLE tba (

id serial ,

peaktemp int,

js jsonb,

logdate date not null

) PARTITION BY RANGE (logdate);

 

-- 设置主键

ALTER TABLE tba ADD PRIMARY KEY (id,logdate);

 

-- 创建索引

create index tba_idx_gin_params_jsonb on tba using gin(js jsonb_path_ops);

 

-- 创建分区

CREATE TABLE tba_p202201 PARTITION OF tba FOR VALUES FROM (MINVALUE) TO ('2022-02-01');

CREATE TABLE tba_p202202 PARTITION OF tba FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');

CREATE TABLE tba_p202203 PARTITION OF tba FOR VALUES FROM ('2022-03-01') TO (MAXVALUE);

 

-- 插入测试数据

INSERT INTO tba (peaktemp,js,logdate)

SELECT

peaktemp,

js::JSON AS js,

logdate

FROM

(

SELECT

round(100000000*random()) AS peaktemp,

concat('{"sex": "男", "age": ',round(100*random()),', "city": "贡嘎", "ogran": "贡嘎中心医院", "office": "内科"}') AS js,

generate_series('2022-01-01'::DATE,'2022-12-31'::DATE,'1 minute') AS logdate

) AS T;

 

-- 查询分区记录

SELECT TABLEOID::REGCLASS,* FROM tba;

 

-- 等于匹配

-- 走索引

SELECT * FROM tba WHERE js @> '{"age": 10}';

-- 不走索引

SELECT * FROM tba WHERE "js" :: json ->> 'age'::text = '10';

 

-- 范围匹配

SELECT * FROM tba WHERE logdate >='2022-03-01' AND cast( js :: json ->> 'age' as decimal) > 20 AND cast( js :: json ->> 'age' as decimal) < 60 LIMIT 10;

 

-- 模糊匹配

SELECT * FROM tba WHERE "js" :: json ->> 'age'::text like '5%' LIMIT 10;

 

-- 精确匹配(带分区条件)

SELECT * FROM tba WHERE logdate <'2022-03-01' AND js @> '{"age": 10}';

 

-- 查询条数

SELECT count(0) FROM tba WHERE logdate <'2022-03-01'

 

-- 100W 数据带条件范围查询 1.772s

-- 1000W 数据带条件范围查询 9.874s

 

 

-- 100W 数据带精确匹配查询 0.914s

-- 1000W 数据带精确匹配查询 2.945s