postgresql json取值為何這麼慢？

一、緣起

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