最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

postgresql?json取值慢的原因分析

 更新時間：2023年06月20日 08:27:54   作者：jihite  
這篇文章主要介紹了postgresql json取值為何這么慢,本文給大家介紹的非常詳細，對大家的學(xué)習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

一、緣起

慢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)換，是否對性能有影響，待會驗證

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， 進行強轉(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)歷的過程，以及耗費的時間，可以協(xié)助優(yōu)化性能。

關(guān)鍵參數(shù)：

Execution time: *** ms 表明了實際的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中查看當前時間和日期的幾種常用方法

    PostgreSQL中查看當前時間和日期的幾種常用方法

    在 PostgreSQL 中,有多個函數(shù)可以用來查看當前時間和日期,這些函數(shù)在處理時間戳、日期和時間的計算時非常有用,以下是幾種常用的查看當前時間和日期的函數(shù)及示例,需要的朋友可以參考下
    2024-10-10
  • PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法

    PostgreSQL將數(shù)據(jù)加載到buffer cache中操作方法

    這篇文章主要介紹了PostgreSQL將數(shù)據(jù)加載到buffer cache中，我們可以使用pg_prewarm插件來將指定的表加載到OS Buffer或者pg shared buffer中，具體操作方法跟隨小編一起看看吧
    2021-04-04
  • Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法

    Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法

    這篇文章主要介紹了Windows?系統(tǒng)?PostgreSQL?手工安裝配置方法,本文主要說一下在?Windows?系統(tǒng)中安裝?PostgreSQL?的方法，我這里沒有采用?exe?安裝包的形式去安裝，EDB?發(fā)布的那個?exe?安裝包形式的對于中文環(huán)境數(shù)據(jù)庫的排序規(guī)則設(shè)定有問題，需要的朋友可以參考下
    2022-09-09
  • Postgresql去重函數(shù)distinct的用法說明

    Postgresql去重函數(shù)distinct的用法說明

    這篇文章主要介紹了Postgresql去重函數(shù)distinct的用法說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01
  • PostgreSQL的日期時間差DATEDIFF實例詳解

    PostgreSQL的日期時間差DATEDIFF實例詳解

    PostgreSQL是一款簡介而又性能強大的數(shù)據(jù)庫應(yīng)用程序,其在日期時間數(shù)據(jù)方面所支持的功能也都非常給力,下面這篇文章主要給大家介紹了關(guān)于PostgreSQL的日期時間差DATEDIFF的相關(guān)資料,需要的朋友可以參考下
    2023-04-04
  • PostgreSQL?Log日志模塊原理及存在的問題詳解

    PostgreSQL?Log日志模塊原理及存在的問題詳解

    這篇文章主要給大家介紹了關(guān)于PostgreSQL?Log日志模塊原理及存在的問題的相關(guān)資料,先日志在我們開發(fā)過程中占據(jù)了一個非常重要的地位,是開發(fā)和運維管理之間的橋梁,文中通過代碼介紹的非常詳細,需要的朋友可以參考下
    2024-02-02
  • postgresql流復(fù)制原理以及流復(fù)制和邏輯復(fù)制的區(qū)別說明

    postgresql流復(fù)制原理以及流復(fù)制和邏輯復(fù)制的區(qū)別說明

    這篇文章主要介紹了postgresql流復(fù)制原理以及流復(fù)制和邏輯復(fù)制的區(qū)別說明，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2020-12-12
  • 深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法

    深入解讀PostgreSQL中的序列及其相關(guān)函數(shù)的用法

    這篇文章主要介紹了PostgreSQL中的序列及其相關(guān)函數(shù)的用法,包括序列的更新和刪除等重要知識,需要的朋友可以參考下
    2016-01-01
  • PostgreSQL通過mysql_fdw連通MySQL實戰(zhàn)

    PostgreSQL通過mysql_fdw連通MySQL實戰(zhàn)

    mysql_fdw就像是PostgreSQL和MySQL數(shù)據(jù)庫之間的一座雙向橋梁,本文就來詳細的介紹一下PostgreSQL通過mysql_fdw連通MySQL實戰(zhàn),感興趣的可以了解一下
    2026-03-03
  • PostgreSQL中的collations用法詳解

    PostgreSQL中的collations用法詳解

    這篇文章主要介紹了PostgreSQL中的collations用法詳解，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧
    2021-01-01

最新評論

陵水| 襄樊市| 乐安县| 东山县| 栖霞市| 余江县| 清新县| 昌江| 佛学| 无极县| 读书| 台中县| 固安县| 旌德县| 山东| 陕西省| 竹溪县| 荣成市| 临洮县| 旬邑县| 厦门市| 兴仁县| 合川市| 岫岩| 兰州市| 庆阳市| 西畴县| 乌拉特前旗| 宝丰县| 彰武县| 西和县| 通渭县| 清徐县| 颍上县| 乃东县| 类乌齐县| 含山县| 明溪县| 厦门市| 荣成市| 泗阳县|