postgresql?json取值慢的原因分析
更新時間：2023年06月20日 08:27:54 作者：jihite
這篇文章主要介紹了postgresql json取值為何這么慢,本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
一、緣起
慢sql分析，總行數(shù)80w+，通過監(jiān)控分析慢SQL， 某個查詢耗時超1s。
比較特殊的是：其中有個字段info是jsonb類型，寫法：info::json->'length' as length
同樣的查詢條件查這個字段和不查這個字段相差3.3倍
那看來就是json取值拖垮了查詢的性能。
取jsonb中的字段有多種取法(如下)， 那他們有什么區(qū)別呢，對性能有啥影響呢？
- 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;
結(jié)果
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
分析小結(jié)
- ->> 輸出類型為text
- ->輸出到底為何得看調(diào)用它的數(shù)據(jù)類型，比如：info類型是jsonb, 那么info->'length'為jsonb類型
- ::json、::jsonb起到類型轉(zhuǎn)換的作用。
- info本來就是jsonb類型，info::jsonb算無效轉(zhuǎn)換，是否對性能有影響，待會驗(yàn)證
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)從執(zhí)行耗時(Execution time)分析小結(jié)
執(zhí)行了類型轉(zhuǎn)換 jsonb->json，轉(zhuǎn)換性能（0.46ms）顯然低出不轉(zhuǎn)換（0.3ms）
三、優(yōu)化
把查詢字段：info::json->'length' 改為info->>'length',減少類型轉(zhuǎn)換導(dǎo)致性能的損耗。
四、待調(diào)查
4.1 同類型轉(zhuǎn)換是否影響性能
字段本身是jsonb， 進(jìn)行強(qiáng)轉(zhuǎn)::jsonb 是否對性能造成影響，還是在執(zhí)行預(yù)編譯時就已被優(yōu)化
從大量數(shù)據(jù)的壓測看，轉(zhuǎn)換會對性能有影響，但是不大
4.2 如何分析函數(shù)的耗時
在explain analyze時，主要分析了索引對性能的影響，那函數(shù)的具體影響如何查看呢？
五、附
5.1 json、jsonb區(qū)別
- jsonb 性能優(yōu)于json
- jsonb 支持索引
- 【最大差異：效率】jsonb 寫入時會處理寫入數(shù)據(jù)，寫入相對較慢，json會保留原始數(shù)據(jù)（包括無用的空格）
推薦把JSON 數(shù)據(jù)存儲為jsonb
5.2 postgresql查看字段類型函數(shù)
pg_typeof()
5.3 性能分析指令
如果您有一條執(zhí)行很慢的 SQL 語句，您想知道發(fā)生了什么以及如何優(yōu)化它。
EXPLAIN ANALYSE 能夠獲取數(shù)據(jù)庫執(zhí)行 sql 語句，所經(jīng)歷的過程，以及耗費(fèi)的時間，可以協(xié)助優(yōu)化性能。
關(guān)鍵參數(shù)：
Execution time: *** ms 表明了實(shí)際的SQL 執(zhí)行時間，其中不包括查詢計劃的生成時間
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
);# 壓測數(shù)據(jù)
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)到此這篇關(guān)于postgresql json取值慢的原因分析的文章就介紹到這了,更多相關(guān)postgresql json取值內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
PostgreSQL數(shù)據(jù)庫timestamp數(shù)據(jù)類型精度進(jìn)位問題解析
PostgreSQL是一款功能強(qiáng)大的開源關(guān)系型數(shù)據(jù)庫管理系統(tǒng),起源于1986年的POSTGRES項目,它支持多種數(shù)據(jù)類型,包括數(shù)值類型、字符串類型、日期時間類型等,本文介紹PostgreSQL數(shù)據(jù)庫timestamp數(shù)據(jù)類型精度進(jìn)位問題,感興趣的朋友一起看看吧2024-11-11
postgresql 實(shí)現(xiàn)數(shù)據(jù)的導(dǎo)入導(dǎo)出
這篇文章主要介紹了postgresql 實(shí)現(xiàn)數(shù)據(jù)的導(dǎo)入導(dǎo)出，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
PostgreSQL教程(十二)：角色和權(quán)限管理介紹
這篇文章主要介紹了PostgreSQL教程(十二)：角色和權(quán)限管理介紹,本文講解了數(shù)據(jù)庫角色、角色屬性、權(quán)限、角色成員,需要的朋友可以參考下2015-05-05
這篇文章主要介紹了PostgreSQL之INDEX 索引詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-02-02
postgresql查詢自動將大寫的名稱轉(zhuǎn)換為小寫的案例
這篇文章主要介紹了postgresql查詢自動將大寫的名稱轉(zhuǎn)換為小寫的案例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了pgsql的UUID生成函數(shù)實(shí)例，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 實(shí)現(xiàn)更新序列的起始值
這篇文章主要介紹了postgresql 實(shí)現(xiàn)更新序列的起始值，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
postgreSQL 數(shù)字與字符串類型轉(zhuǎn)換操作
這篇文章主要介紹了postgreSQL 數(shù)字與字符串類型轉(zhuǎn)換操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2020-12-12
這篇文章主要介紹了PostgreSQL timestamp踩坑記錄與填坑指南，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
關(guān)于PostgreSQL JSONB的匹配和交集問題
這篇文章主要介紹了PostgreSQL JSONB的匹配和交集問題，本文給大家介紹的非常詳細(xì)，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-09-09