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