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