postgresql json取值为何这么慢？ 天天最新

一、缘起

慢sql分析，总行数80w+，通过监控分析慢SQL， 某个查询耗时超1s。

比较特殊的是：其中有个字段info是jsonb类型，写法：info::json->"length" as length

同样的查询条件查这个字段和不查这个字段相差3.3倍

(资料图片)

那看来就是json取值拖垮了查询的性能。

取jsonb中的字段有多种取法(如下)， 那他们有什么区别呢，对性能有啥影响呢？

info::json->"length"info::jsonb->"length"info::json->>"length"info::jsonb->>"length"info->"length"info->"length"info->>"length"info->>"length"二、对比2.1 输出类型对比

查询不同写法的类型：

select info::json->"length"  AS "info::json->", pg_typeof(info::json->"length" ) ,info::jsonb->"length" AS "info::jsonb->" , pg_typeof(info::jsonb->"length" ),info::json->>"length"  AS "info::json->>" , pg_typeof(info::json->>"length" ),info::jsonb->>"length" AS "info::jsonb->>"  , pg_typeof(info::jsonb->>"length"),info->"length" AS "info->"  , pg_typeof(info->"length" ),info->"length" AS "info->"  , pg_typeof(info->"length" ),info->>"length" AS "info->>"  , pg_typeof(info->>"length" ),info->>"length" AS "info->>"  , pg_typeof(info->>"length" )from t_test_json limit 1;

结果

info::json-> | pg_typeof | info::jsonb-> | pg_typeof | info::json->> | pg_typeof | info::jsonb->> | pg_typeof | info-> | pg_typeof | info-> | pg_typeof | info->> | pg_typeof | info->> | pg_typeof --------------+-----------+---------------+-----------+---------------+-----------+----------------+-----------+--------+-----------+--------+-----------+---------+-----------+---------+----------- 123.9        | json      | 123.9         | jsonb     | 123.9         | text      | 123.9          | text      | 123.9  | jsonb     | 123.9  | jsonb     | 123.9   | text      | 123.9   | textttui　

分析小结

->> 输出类型为text->输出到底为何得看调用它的数据类型，比如：info类型是jsonb, 那么info->"length"为jsonb类型::json、::jsonb起到类型转换的作用。info本来就是jsonb类型，info::jsonb算无效转换，是否对性能有影响，待会验证2.2 性能对比

jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info::json->"length"  AS "info::json->", pg_typeof(info::json->"length" )  jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.04 rows=1 width=36) (actual time=0.028..0.028 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..30.62 rows=750 width=36) (actual time=0.027..0.027 rows=1 loops=1) Planning time: 0.056 ms Execution time: 0.047 ms(4 rows)jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info::jsonb->"length" AS "info::jsonb->" , pg_typeof(info::jsonb->"length" )jihite-> from t_test_json limit 1jihite-> ;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.017..0.017 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.015..0.015 rows=1 loops=1) Planning time: 0.053 ms Execution time: 0.031 ms(4 rows)jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info::jsonb->"length" AS "info::jsonb->" , pg_typeof(info::jsonb->"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.010..0.010 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.009..0.009 rows=1 loops=1) Planning time: 0.037 ms Execution time: 0.022 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info::json->>"length"  AS "info::json->>" , pg_typeof(info::json->>"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.04 rows=1 width=36) (actual time=0.026..0.027 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..30.62 rows=750 width=36) (actual time=0.025..0.025 rows=1 loops=1) Planning time: 0.056 ms Execution time: 0.046 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info::jsonb->>"length" AS "info::jsonb->>"  , pg_typeof(info::jsonb->>"length")jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.012..0.012 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.011..0.011 rows=1 loops=1) Planning time: 0.053 ms Execution time: 0.029 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info->"length" AS "info->"  , pg_typeof(info->"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.014..0.014 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.013..0.013 rows=1 loops=1) Planning time: 0.052 ms Execution time: 0.030 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info->"length" AS "info->"  , pg_typeof(info->"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.013..0.013 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.012..0.012 rows=1 loops=1) Planning time: 0.051 ms Execution time: 0.029 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info->>"length" AS "info->>"  , pg_typeof(info->>"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.012..0.013 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.011..0.011 rows=1 loops=1) Planning time: 0.053 ms Execution time: 0.030 ms(4 rows)jihite=> jihite=> EXPLAIN ANALYSEjihite-> select jihite-> info->>"length" AS "info->>"  , pg_typeof(info->>"length" )jihite-> from t_test_json limit 1;                                                  QUERY PLAN                                                   --------------------------------------------------------------------------------------------------------------- Limit  (cost=0.00..0.03 rows=1 width=36) (actual time=0.012..0.013 rows=1 loops=1)   ->  Seq Scan on t_test_json  (cost=0.00..23.12 rows=750 width=36) (actual time=0.011..0.011 rows=1 loops=1) Planning time: 0.053 ms Execution time: 0.029 ms(4 rows)

从执行耗时(Execution time)分析小结

执行了类型转换 jsonb->json，转换性能（0.46ms）显然低出不转换（0.3ms）

三、优化

把查询字段：info::json->"length" 改为info->>"length",减少类型转换导致性能的损耗。

四、待调查4.1 同类型转换是否影响性能

字段本身是jsonb， 进行强转::jsonb 是否对性能造成影响，还是在执行预编译时就已被优化

从大量数据的压测看，转换会对性能有影响，但是不大

4.2 如何分析函数的耗时

在explain analyze时，主要分析了索引对性能的影响，那函数的具体影响如何查看呢？

五、附5.1 json、jsonb区别jsonb 性能优于jsonjsonb 支持索引【最大差异：效率】jsonb 写入时会处理写入数据，写入相对较慢，json会保留原始数据（包括无用的空格）

推荐把JSON 数据存储为jsonb

5.2 postgresql查看字段类型函数

pg_typeof()

5.3 性能分析指令

如果您有一条执行很慢的 SQL 语句，您想知道发生了什么以及如何优化它。EXPLAIN ANALYSE 能够获取数据库执行 sql 语句，所经历的过程，以及耗费的时间，可以协助优化性能。

关键参数：

Execution time: *** ms 表明了实际的SQL 执行时间，其中不包括查询计划的生成时间

5.4 示例中的建表语句

# 建表语句

create table t_test_json(    id          bigserial         not null PRIMARY KEY,    task        character varying not null,    info        jsonb             not null,    create_time timestamp         not null default current_timestamp);

# 压测数据

insert into t_test_json(task, info) values("1", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("2", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("3", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("4", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("5", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("6", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("7", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("8", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("9", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("10", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("11", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("12", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("13", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("14", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("15", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("16", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("17", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("18", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("19", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");insert into t_test_json(task, info) values("20", "{"length": 123.9, "avatar": "avatar_url", "tags": ["python", "golang", "db"]}");

5.5 示例中的压测脚本

import timeimport psycopgdbname, user, pwd, ip, port = "", "", "", "", "5432"connection = "dbname=%s user=%s password=%s host=%s port=%s" % (dbname, user, pwd, ip, port)db = psycopg.connect(connection)cur = db.cursor()ss = 0lens = 20for i in range(lens):    s = time.time()    sql = """ select        id,        info::json->"length" as length        from        t_test_json        order by id        offset %s limit 1000 """ % (i * 1000)    #print("sql:", sql)    cur.execute(sql)    rev = cur.fetchall()    e = time.time()    print("scan:", i, e - s)    ss += (e - s)print("avg", ss / lens)

关键词：