postgresql json取值为何这么慢？

最新推荐文章于 2024-06-04 23:36:21 发布
最新推荐文章于 2024-06-04 23:36:21 发布
阅读量486 收藏 3
点赞数 3
文章标签： postgresql json 数据库
原文链接：https://www.cnblogs.com/kaituorensheng/p/17492413.html
版权

一、缘起

慢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;

结果

1

2

3

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 性能对比

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

jihite=> EXPLAIN ANALYSE

jihite-> 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 ANALYSE

jihite-> select

jihite-> info::jsonb->'length' AS "info::jsonb->" , pg_typeof(info::jsonb->'length' )

jihite-> from t_test_json limit 1

jihite-> ;

                                                  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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 ANALYSE

jihite-> 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 性能优于json
  • jsonb 支持索引
  • 【最大差异：效率】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 time
import psycopg


dbname, 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 = 0
lens = 20
for 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)
毕业课程设计
关注
  • 3
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
PostgreSQL 操作json/jsonb
一碗清深的博客
07-01 9500
PostgreSQL 9.2 中引入了对JSON类型的支持，经过几个大版本的进化，目前对JSON数字类型的支持已经比较完善。在PG中对JSON格式信息的CRUD操作，针对具体的节点建立索引，这些都可以很容易的实现。
如何使用Postgres的JSONB数据类型进行高效查询
程序员墨松
04-17 1188
PostgreSQL的JSONB数据类型提供了一种灵活的方式来存储和查询JSON格式的数据。JSONB不仅允许你在PostgreSQL数据库中存储JSON文档，而且还对这些文档提供了二进制格式的存储和索引支持，使得查询更加高效。下面我们将详细讨论如何使用JSONB数据类型进行高效查询，并提供相应的解决方案和示例代码。
Postgresql数据库中的Json类型字段官方文档说明
XiaoCai
02-08 3880
JSON数据类型用于存储JSON (JavaScript对象符号)数据，如RFC 71594中所述。这样的数据也可以存储为文本，但是JSON数据类型的优点是强制每个存储值根据JSON规则是有效的。对于存储在这些数据类型中的数据，还可以使用各种特定于json的函数和操作符;参见9.16节。PostgreSQL提供了两种类型的JSON数据存储:JSON和jsonb。为了对这些数据类型实现高效的查询机制，PostgreSQL还提供了章节8.14.7中描述的jsonpath数据类型。
SQL调优之遇到json数组性能提升了12倍
最新发布
小宝潜行的博客
06-04 904
本篇文章主要是在SQL调优方面关于解决思路的简单分享
PostgreSQL数据库的json操作
qq_26112725的博客
01-01 599
【代码】PostgreSQL数据库的json操作。
PostgreSQL操作JSON数据
qq_42715450的博客
11-16 2万+
前言 PostgreSQL提供了两种存储JSON数据的类型：json和jsonb； jsonb是json的二进制形式。 json格式写入快，但读取慢； jsonb格式写入慢，但读取快； 常用语法 // -> 返回json select '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'::json->2 // 输出 {"c":"baz"} select '{"a": {"b":"foo"}, "c":{"a": "aaa"}}'::json->'a' //
关于pgsql 的json 和jsonb 的数据查询操作笔记整理
wjy397的专栏
04-07 7456
http://www.cnblogs.com/mywebnumber/p/5551092.html 关于pgsql 的json 和jsonb 的数据处理笔记 1. json 和jsonb 区别 两者从用户操作的角度来说没有区别，区别主要是存储和读取的系统处理（预处理）和耗时方面有区别。json写入快，读取慢，jsonb写入慢，读取快。 2. 常用的操作符 操作符：
PostgreSQL WAL日志解析工具: wal2json
07-06
`wal2json` 的核心功能在于其能够将 PostgreSQL 的原始 WAL 信息转换为结构化的 JSON 文档，这对于监控、审计、备份和恢复等场景非常有用。通过将 WAL 数据解析成 JSON，用户可以轻松地理解和分析数据库的变化，同时...
PyPI 官网下载 | sqlalchemy-postgresql-json-0.4.7.tar.gz
01-30
SQLAlchemy-PostgreSQL-JSON是Python中一个非常实用的库，它为SQLAlchemy（Python的ORM框架）扩展了对PostgreSQL数据库中JSON类型的支持。在PostgreSQL 9.3版本及以上，JSON类型被引入，允许存储和查询JSON格式的...
postgres-json-schema, PostgreSQL的JSON模式验证.zip
09-17
postgres-json-schema, PostgreSQL的JSON模式验证 postgres-json-schema postgres-json-schema允许在PostgreSQL中验证 JSON模式。 它作为 Pl/PgSQL 函数实现，你可以使用它作为检查约束来验证JSON列的格式。postgre
mongery:将MongoDB查询转换为PostgreSQL JSON的Arel AST
05-23
Mongery使用JSON列将MongoDB查询转换为Postgres的Arel AST，用于ActiveRecord连接。 这在以下情况下很有用：在不接触应用程序代码的情况下从MongoDB后端迁移到Postgres的情况，或者将MongoDB查询语法作为JSON API...
ar_pg_json:使用PostgreSQL json聚合函数包装Active Record查询直接输出为json
07-06
`ar_pg_json` 是一个针对PostgreSQL数据库的工具，它允许开发者使用Active Record查询直接生成JSON格式的结果，而无需经过额外的序列化步骤。这个库特别适合那些需要高效地返回JSON格式数据的API或Web服务。 `ar_pg...
每日学习Redis——拿捏Redis中的通用指令（键和数据库通用指令）_m_redis-&gt；del(keyresult)； m_redis-&gt；del(keytask)
2401_84170623的博客
04-29 440
网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。需要这份系统化资料的朋友，可以戳这里获取一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！pics/618545628)**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、
PG-NoSQL特性：json和 jsonb 读写性能测试
qq961573863的博客
10-23 1683
json 、 jsonb 读写性能简单对比
postgresql 之row_to_json
dtryl的专栏
10-07 4401
PostgreSQL 9.2新增加了一个新的特性--JSON支持,具体包含一个JSON数据类型和两个json函数,这些techniques可以从数据库直接获取json格式的数据 How To vkott_livecde=# select * from live_area_info ; area_id | area_name | description ---------+------------+----------------------...
postgresql----JSON类型和函数
weixin_34253126的博客
07-12 2625
postgresql支持两种json数据类型：json和jsonb，而两者唯一的区别在于效率,json是对输入的完整拷贝，使用时再去解析，所以它会保留输入的空格，重复键以及顺序等。而jsonb是解析输入后保存的二进制，它在解析时会删除不必要的空格和重复的键，顺序和输入可能也不相同。使用时不用再次解析。两者对重复键的处理都是保留最后一个键值对。效率的差别：json类型存储快，使用慢，jsonb类型存...
sql中 int转 numeric_PostgreSQL 中的 JSONB 性能测试
weixin_39633807的博客
11-26 580
PostgreSQL 中的 JSONB 性能测试Postgres 从 9.3 就开始支持 JSON 了，mysql 也在 5.7 加入了 JSON。对于存储非结构化数据，如果大部分的操作都是整段存储和读取，那么完全不需要构建复杂的表或者模式。很多场景下非常适合选择 JSON 数据结构。我一直非常好奇 PostgreSQL 的 JSONB 性能如何。假定我们要在一个 http 请求内查询含有 JSO...
PostgreSQL JSON查询
热门推荐
eff666的博客
04-01 2万+
JSON 代表 JavaScript Object Notation。JSON是开放的标准格式，由key-value对组成。JSON的主要用于在服务器与web应用之间传输数据。与其他格式不同，JSON是人类可读的文本格式。 PostgreSQL从9.2版本开始支持JSON数据类型，并提供很多函数和操作符维护json数据。下面我们创建一张新表用于说明json数据类型： CREATE TABLE or...
postgresql 主表分区（数据量3千万）解决查询慢速度提升
weixin_45003528的博客
12-11 612
上面替换自己表名即可，可以解决数据问题。2.创建分区表与主表一致，并建索引。6.为主表创建触发器。
postgresql json
09-13
PostgreSQL是一种强大的关系型数据库管理系统，它支持处理和查询JSON数据类型。在PostgreSQL中，可以使用JSONB数据类型来存储和查询JSON数据。 根据引用[1]，我们可以使用`@>`操作符来判断一个JSONB值是否包含另一个JSONB值。例如，`'{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb`表示判断JSONB值`'{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'`是否包含JSONB值`'{"version": 9.4}'`。这个查询语句将返回true，因为被判断的JSONB值包含了要匹配的JSONB值。 根据引用，我们可以看到另一个使用`@>`操作符的例子。`'{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb`表示判断JSONB值`'{"foo": {"bar": "baz"}}'`是否包含JSONB值`'{"foo": {}}'`。这个查询语句将返回true，因为被判断的JSONB值包含了要匹配的JSONB值。 最后，根据引用，我们可以使用`@>`操作符来判断一个JSONB数组是否包含另一个JSONB数组。例如，`'[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb`表示判断JSONB数组`'[1, 2, [1, 3]]'`是否包含JSONB数组`'[[1, 3]]'`。这个查询语句将返回true，因为被判断的JSONB数组包含了要匹配的JSONB数组。 综上所述，PostgreSQL提供了强大的功能来处理和查询JSON数据类型，通过使用`@>`操作符，可以方便地判断JSONB值或数组是否包含指定的JSONB值或数组。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值