Postgres - alx696/share GitHub Wiki

注意

  1. 使用IDEA的数据库功能时，时区总是UTC(与数据库无关).除非在数据源属性高级中添加VM options: -Duser.timezone=Asia/Shanghai

函数

  • 类型变换 cast(列名 as 类型)
  • 变量转jsonb to_jsonb(变量)
  • 字符替换 replace(列名, '被替换字符', '替换后字符')
  • null转值 coalesce(asset.alarm_level, -2) 当asset.alarm_level为null时,值设为-2
  • null排序时最后 order by alarm_level desc nulls last
  • 时间格式化 select to_timestamp(current_date || ' 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  • 获取unix时间戳 select extract(epoch from now())

按特定关键字的顺序排序

比如类型为"温度"的排在第一位, "湿度"排在第二位, 则可使用 array_positions(array['温度', '湿度'], col)获取对应数值1和2, 然后对数值进行排序即可. 对于没有在数组中的值会返回null, 此时上面的null处理可以解决.

JSONB属性更新

update 表名 set 列名 = jsonb_set(列名, '{属性名}', '新的值')

新的值可以是字符(字符要用双引号,json语法), 数组, 数字等. 如需更新多个属性, 可以嵌套jsonb_set. 变量可用to_jsonb()转成jsonb.

JSONB数组中对象的属性

直接方法

select j from infrastructure_asset where j->'other_properties' @> '[{"name":"焦距", "value": "200"}]'::jsonb

间接方法

select j from (
  select j, jsonb_array_elements(j->'other_properties') as ps from infrastructure_asset
) as t where ps->>'name' = '焦距' and ps->>'value' = '200'

JSON对象数组是否包含对象数组中的任意一个

https://www.postgresql.org/docs/12/functions-json.html (jsonb_path_exists)

https://dba.stackexchange.com/questions/130699/postgresql-json-query-array-against-multiple-values?answertab=active#tab-top

JSON数组是否包含数组中的任意一个

-- JSON路径
select count(*) from organization_person where jsonb_path_exists(j, '$.role_ids[*] ? (@ == "guanli" || @ == "e847b1c9-7a0f-4185-a699-0d8c9b3eadf3")');
-- 普通
select count(*) from organization_person where j->'role_ids' ?| array['guanli', 'e847b1c9-7a0f-4185-a699-0d8c9b3eadf3'];

JSON数组是全部否包含某个数组

select count(*) from organization_person where j->'role_ids' ?& array['guanli', 'e847b1c9-7a0f-4185-a699-0d8c9b3eadf3'];

获取JOSN数组长度

jsonb_array_length(j->'asset_ids')

JSON数组属性转行

jsonb_array_elements_text(jsonb) 把一个jsonb列中的数组属性转成行,最终效果为数据被分为行.

数据:

{"id": "CBL_ACU001_DFC01_Flt", "name": "风机故障反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "", "expression_text": "x=0:无故障;x=1:故障", "expression_alarm": "", "expression_fault": "x=1", "virtual_interval": 2000, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}
{"id": "CBL_ACU001_DFC01_Run", "name": "风机运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:default", "control_stop_condition": "", "control_start_condition": ""}

语句:

select j, jsonb_array_elements_text(j->'asset_ids') as asset_id from infrastructure_detection_point;

结果:

{"id": "CBL_ACU001_DFC01_Flt", "name": "风机故障反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "", "expression_text": "x=0:无故障;x=1:故障", "expression_alarm": "", "expression_fault": "x=1", "virtual_interval": 2000, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}	0101001FJ001.mesh
{"id": "CBL_ACU001_DFC01_Run", "name": "风机运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:default", "control_stop_condition": "", "control_start_condition": ""}	0101001FJ001.mesh

JSON数组属性转行后通过某列分组,将数据合并到一个数组中

select asset_id, jsonb_agg(j) as ja from (select j, jsonb_array_elements_text(j->'asset_ids') as asset_id from infrastructure_detection_point) as t
group by asset_id order by asset_id;

(数据同上)结果:

0101001FJ001.mesh	[{"id": "CBL_ACU001_DFC01_Flt", "name": "风机故障反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "", "expression_text": "x=0:无故障;x=1:故障", "expression_alarm": "", "expression_fault": "x=1", "virtual_interval": 2000, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC01_Run", "name": "风机运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:default", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC01_RunF", "name": "风机高速运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:faster", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC01_RunH", "name": "风机累计运行时间", "type": "", "unit": "小时", "state": "正常", "assets": [], "asset_ids": ["0101001FJ001.mesh"], "control_stop": "", "virtual_rule": "1:5", "control_start": "", "expression_run": "", "expression_text": "", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}]
0101001FJ002.mesh	[{"id": "CBL_ACU001_DFC02_Flt", "name": "风机故障反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ002.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "", "expression_text": "x=0:无故障;x=1:故障", "expression_alarm": "", "expression_fault": "x=1", "virtual_interval": 2000, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC02_Run", "name": "风机运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ002.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:default", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC02_RunF", "name": "风机高速运行反馈", "type": "", "unit": "", "state": "正常", "assets": [], "asset_ids": ["0101001FJ002.mesh"], "control_stop": "", "virtual_rule": "0||1", "control_start": "", "expression_run": "x=1", "expression_text": "x=0:停止;x=1:运行", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "x=1:faster", "control_stop_condition": "", "control_start_condition": ""}, {"id": "CBL_ACU001_DFC02_RunH", "name": "风机累计运行时间", "type": "", "unit": "小时", "state": "正常", "assets": [], "asset_ids": ["0101001FJ002.mesh"], "control_stop": "", "virtual_rule": "1:5", "control_start": "", "expression_run": "", "expression_text": "", "expression_alarm": "", "expression_fault": "", "virtual_interval": 20, "follow_info_array": [], "expression_special": "", "control_stop_condition": "", "control_start_condition": ""}]

普通字段表直接转JSON表(整表转JSON数组)

https://hashrocket.com/blog/posts/faster-json-generation-with-postgresql

JSON查询优化实例

原sql

耗时10秒

select asset.*, coalesce(alarm_level, -2) as alarm_level
from (select j
      from infrastructure_asset as asset
      where exists(select 1 from (select distinct j->'asset_ids' as asset_ids from infrastructure_detection_point) point_asset where point_asset.asset_ids ? (asset.j->>'id'))
        and (j->>'section_id' in('') or j->>'special_id' in(''))) asset
       left join (select b.j->>'asset_id' as asset_id, cast(b.j->>'alarm_level' as int) as alarm_level
                  from (select j->>'asset_id' as asset_id, max(cast(j->>'ts' as bigint)) as ts
                        from infrastructure_detection_point_asset_data
                        group by j->>'asset_id') as a
                         left join infrastructure_detection_point_asset_data as b
                           on a.asset_id = b.j->>'asset_id' and a.ts = cast(b.j->>'ts' as bigint)) as asset_data
         on asset.j->>'id' = asset_data.asset_id
order by alarm_level desc;

优化后sql

耗时100毫秒

select asset.*, coalesce(asset_data.alarm_level, -2) as alarm_level from infrastructure_asset as asset left join
(select b.j->>'asset_id' as asset_id, cast(b.j->>'alarm_level' as int) as alarm_level
 from (select j->>'asset_id' as asset_id, max(cast(j->>'ts' as bigint)) as ts
       from infrastructure_detection_point_asset_data
       group by j->>'asset_id') as a
        left join infrastructure_detection_point_asset_data as b
          on a.asset_id = b.j->>'asset_id' and a.ts = cast(b.j->>'ts' as bigint)) as asset_data
on asset.j->>'id' = asset_data.asset_id
where asset.j->>'id' in (select distinct jsonb_array_elements_text(j->'asset_ids') from infrastructure_detection_point)
  and (j->>'section_id' in('') or j->>'special_id' in(''))
order by alarm_level desc

分析

infrastructure_asset的条件中使用了exists和?, 比用in的模式多耗时7倍, 在联合查询时直接造成多耗时100倍.

优化部分

优化前:

select j
from infrastructure_asset as asset
where exists(select 1 from (select distinct j->'asset_ids' as asset_ids from infrastructure_detection_point) point_asset where point_asset.asset_ids ? (asset.j->>'id'))
  and (j->>'section_id' in('') or j->>'special_id' in(''));

优化后:

select j
from infrastructure_asset as asset
where asset.j->>'id' in (select distinct jsonb_array_elements_text(j->'asset_ids') from infrastructure_detection_point)
  and (j->>'section_id' in('') or j->>'special_id' in(''));

优化

表分区

参考官方文档和JSONB数据类型文档，对JSONB数据类型的表分区。

创建表为分区表

CREATE TABLE point_data
(
    j jsonb NOT NULL
)
partition by range ((j->'ts'));

指定jsonb中ts属性范围作为分区依据。

创建表的分区

create table if not exists point_data_197 partition of point_data for values from ('1594742400000') to ('1594828799000');

为表创建名为point_data_197的分区，并指定该分区的ts起止范围为1594742400000到1594828799000。

创建表的分区的索引

create index if not exists point_data_197_ts_idx on point_data_197 using btree ((j->'ts'));

为表的分区的ts属性创建名为point_data_197_ts_idx的索引。

通过上面3个步骤即可完成分区表的创建，使用时直接对point_data即可。插入的数据会根据ts的值自动存入对应分区。

注意：分区和分区的索引必须先于数据创建！

修改I/O调度器为noop

见Ubuntu#修改io调度器为noop

参数(测试查询好像没有任何提升)

以docker镜像为例:

    docker run -d --restart=always \
      -p 35432:5432 \
      -v ${PWD}/postgres:/data \
      -v ${PWD}/init.sql:/docker-entrypoint-initdb.d/init.sql \
      -e PGDATA=/data -e TZ=Asia/Shanghai \
      -e POSTGRES_PASSWORD=${PASSWORD} \
      --sysctl net.core.somaxconn=1000 \
      --name "postgres" postgis/postgis:12-2.5 \
      -c 'max_connections=1000' \
      -c 'tcp_keepalives_idle=9' -c 'tcp_keepalives_interval=6' -c 'tcp_keepalives_count=3' \
      -c 'effective_io_concurrency=600' \
      -c 'random_page_cost=2' \
      -c 'maintenance_work_mem=4GB' -c 'work_mem=16MB' \
      -c 'shared_buffers=4GB' -c 'max_wal_size=2GB' \
      -c 'effective_cache_size=16GB'

参考: