首页 > 数据库 >postgresql json取值为何这么慢？

postgresql json取值为何这么慢？

时间：2023-06-19 23:34:03浏览次数：52  
标签：info rows postgresql length json time jihite 取值

一、缘起

慢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 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
        task.id,
        act.payload::json->'prod_type' as prod_type
        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)

 

标签：info,rows,postgresql,length,json,time,jihite,取值
From： https://www.cnblogs.com/kaituorensheng/p/17492413.html

相关文章

  • MySQL与PostgreSQL相比哪个更好？
    原文：https://www.51cto.com/article/535284.html PostgreSQL相对于MySQL的优势1)不仅仅是关系型数据库除了存储正常的数据类型外，还支持存储：array，不管是一位数组还是多为数组均支持json(hStore)和jsonb，相比使用text存储接送要高效很多json和jsonb之间的区别jsonb和json在......
  • 记录 Windows 下绿色版 PostgreSQL 部署使用
    使用官方的安装包，可能会在最后的步骤遇到各种有关服务运行的问题，绿色版就非常简单了，记录一下绿色版的下载部署。1、下载地址：https://www.enterprisedb.com/download-postgresql-binaries2、将文件解压到想放置的目录3、进入pgsql\bin目录，打开命令提示符执行以下命令::初......
  • 各种数据类型的取值范围对比
    char：-128--127  unsignedchar: 0--255 byte：-2^7~2^7-1，即-128~127。1字节。Byte。末尾加Bshort：-2^15~2^15-1，即-32768~32767。2字节。Short。末尾加S有符号int：-2^31~2^31-1，即-2147483648~2147483647。4字节。Integer。无符号int：0~2^32-1。long：-2^63~......
  • --Postgresql 建表疏忽导致的数据无法插入，发现奇怪的问题
    此前在其他的数据库并未注意到这点，POSTGRESQL建立字符字段的时候，可以大量使用TEXT的形式来存储字符。建表的时候粗心在建立表后，插入数据一直报错当时没有注意，认为是符号的错误导致的写入数据的问题，修改了半天insert的语句，报错也改变了最终发现不是insert语句的问题而是建表的时候......
  • POSTGRESQL UPDATE 如何提高I/O 能力
    POSTGRESQL的数据扫描，其实和其他的数据库也无差，无非就是数据块的扫描以及索引的扫描，这里POSTGRESQL数据扫描也叫TUPLESCAN。在POSTGRESQL8.3版本后再HEAP表的修改中，有一个概念叫HOT，通过新的概念提高了堆表的性能，减少了I/O。早起的POSTGRESQL更新的方式是修改索引中的数......
  • POSTGRESQL RC事务处理与ORACLE MYSQL 的区别 --对PGFANS 群里面的问题的分解
    有一个同学在PGFANS群里面提了一个问题，在他实验的某个操作中发现PG和ORACLE使用同样的操作流程后，得到的结果不一致。所以下面准备验证并找到一些可以解释的原因。测试库名test测试表test测试数据id  age 1   202   223   24首先我们要确认 PG的隔离 RC......
  • POSTGRESQL analyze table 到底做了什么与扩展统计
    PostgreSQL 中对表的状态是有单独的命令来进行状态的收集的，到底怎么对表来进行状态的收集，并且都做了什么，我们怎么来依靠这些信息来对查询进行有益的帮助。这些都将在这篇文章里面探讨。首先我们对PG12中，关于Analyze 的注释来仔细的阅读一遍ANALYZE collectsstatisticsabout......
  • POSTGRESQL PG_REWIND 从源代码看功能
    PG_REWIND是PG9.6开始提供的功能，主要的作用在于通过PG_REWIND让PG复制中的数据库快速的与预定的“主库”进行数据同步，而复制的方式是是文件块的方式，并且可以避过重复的数据块。所以复制的速度是快的，在不少的高可用方式中都被作为主库失败后的快速的将主库加入原有集群并作为从......
  • 分布式两大流派 POSTGRESQL -XC 了解一下
    分布式数据库有两大流派，NEWSQLVS POSTGRESQL-XC，NEWSQL的分布式主流的理论来源自GOOGLE的分布式数据库spanner，以及相关理论的白皮书，而令一派的分布式数据库来自于POSTGRESQL-XC，今天我们看看到底POSTGRESQL-XC这个流派的方式是什么，有什么特点，当下那些分布式数据库采用了......
  • POSTGRESQL Postgres-XL 了解一下
    上次分析的POSTGRES-XC的结构, 实际上POSTGRES-X系列一直在发展, POSTGRES除了XC还有XL的高可用的结构.Postgres-XL是一款Postgres-XC升级的产品，如果说PGXC是在PG添加了集群的功能主打OLTP的功能为卖点，PGXL是一款基于PGXC添加了OLAP功能的支持MPP架构的，但不是简单的PO......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99