資訊專欄INFORMATION COLUMN
摘要：類型說明根據中的說明，數據類型是用來存儲數據的。它們幾乎接受完全相同的值集合作為輸入。該結構是非強制的，但是有一個可預測的結構會使集合的查詢更容易。如中，表示在和這兩個位置出現過，在中這些位置實際上就是元組的行號，包括數據塊以及。
json 類型 說明
根據RFC 7159中的說明，JSON 數據類型是用來存儲 JSON（JavaScript Object Notation）數據的。這種數據也可以被存儲為text，但是 JSON 數據類型的優勢在于能強制要求每個被存儲的值符合 JSON 規則。也有很多 JSON 相關的函數和操作符可以用于存儲在這些數據類型中的數據
PostgreSQL支持兩種 JSON 數據類型：json 和 jsonb。它們幾乎接受完全相同的值集合作為輸入。兩者最大的區別是效率。json數據類型存儲輸入文本的精準拷貝，處理函數必須在每 次執行時必須重新解析該數據。而jsonb數據被存儲在一種分解好的二進制格式中，因為需要做附加的轉換，它在輸入時要稍慢一些。但是 jsonb在處理時要快很多，因為不需要重新解析。
重點：jsonb支持索引
由于json類型存儲的是輸入文本的準確拷貝，存儲時會空格和JSON 對象內部的鍵的順序。如果一個值中的 JSON 對象包含同一個鍵超過一次，所有的鍵/值對都會被保留（ 處理函數會把最后的值當作有效值）。
jsonb不保留空格、不保留對象鍵的順序并且不保留重復的對象鍵。如果在輸入中指定了重復的鍵，只有最后一個值會被保留。
推薦把JSON 數據存儲為jsonb
在把文本 JSON 輸入轉換成jsonb時，JSON的基本類型（RFC 7159 ）會被映射到原生的 PostgreSQL類型。因此，jsonb數據有一些次要額外約束。
比如：jsonb將拒絕除 PostgreSQL numeric數據類型范圍之外的數字，而json則不會。
JSON 基本類型和相應的PostgreSQL類型
| JSON 基本類型 | PostgreSQL類型 | 注釋 |
|---|---|---|
| string | text | 不允許u0000，如果數據庫編碼不是 UTF8，非 ASCII Unicode 轉義也是這樣 |
| number | numeric | 不允許NaN 和 infinity值 |
| boolean | boolean | 只接受小寫true和false拼寫 |
| null | (無) | SQL NULL是一個不同的概念 |
-- 簡單標量/基本值
-- 基本值可以是數字、帶引號的字符串、true、false或者null
SELECT "5"::json;
-- 有零個或者更多元素的數組（元素不需要為同一類型）
SELECT "[1, 2, "foo", null]"::json;
-- 包含鍵值對的對象
-- 注意對象鍵必須總是帶引號的字符串
SELECT "{"bar": "baz", "balance": 7.77, "active": false}"::json;
-- 數組和對象可以被任意嵌套
SELECT "{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}"::json;
-- "->" 通過鍵獲得 JSON 對象域 結果為json對象
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" as nickname;
nickname
-------------
"goodspeed"
-- "->>" 通過鍵獲得 JSON 對象域 結果為text
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" as nickname;
nickname
-----------
goodspeed
-- "->" 通過鍵獲得 JSON 對象域 結果為json對象
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" as nickname;
nickname
-------------
"goodspeed"
-- "->>" 通過鍵獲得 JSON 對象域 結果為text
select "{"nickname": "goodspeed", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" as nickname;
nickname
-----------
goodspeed
當一個 JSON 值被輸入并且接著不做任何附加處理就輸出時， json會輸出和輸入完全相同的文本，而jsonb 則不會保留語義上沒有意義的細節
SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::json;
json
-------------------------------------------------
{"bar": "baz", "balance": 7.77, "active":false}
-- jsonb 不會保留語義上的細節，key 的順序也和原始數據不一致
SELECT "{"bar": "baz", "balance": 7.77, "active":false}"::jsonb;
jsonb
--------------------------------------------------
{"bar": "baz", "active": false, "balance": 7.77}
json 查詢語法
在使用JSON文檔時，推薦 將JSON 文檔存儲為固定的結構。（該結構是非強制的，但是有一個可預測的結構會使集合的查詢更容易。 ）
設計JSON文檔建議：任何更新都在整行上要求一個行級鎖。為了減少鎖爭奪，JSON 文檔應該每個表示 一個原子數據（業務規則上的不可拆分，可獨立修改的數據）。這些常用的比較操作符只對jsonb 有效，而不適用于json
常用的比較操作符
| 操作符 | 描述 |
|---|---|
| < | 小于 |
| > | 大于 |
| <= | 小于等于 |
| >= | 大于等于 |
| = | 等于 |
| <> or != | 不等于 |
json和jsonb 操作符
使用 ->> 查出的數據為text
使用 -> 查出的數據為json 對象
-- nickname 為 gs 的用戶 這里使用 ->> 查出的數據為text，所以匹配項也應該是text
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->>"nickname" = "gs";
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";
-- 使用 -> 查詢，會拋出錯誤，這里無論匹配項是text類型的 "gs" 還是 json 類型的 ""gs""::json都會拋出異常，json 類型不支持 等號（=）操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs"";
ERROR: operator does not exist: json = unknown
-- json 類型不支持 "=" 操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json->"nickname" = ""gs""::json;
ERROR: operator does not exist: json = json
-- jsonb 格式是可以查詢成功的，這里使用 -> 查出的數據為json 對象，所以匹配項也應該是json 對象
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";
使用 #>> 查出的數據為text
使用 #> 查出的數據為json 對象
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" as tag;
tag
----------
"python"
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" as tag;
tag
--------
python
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>"{tags,0}" = ""python"";
?column?
----------
t
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb#>>"{tags,0}" = "python";
?column?
----------
t
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>>"{tags,0}" = "python";
?column?
----------
t
-- 會拋出錯誤，這里無論匹配項是text類型的 "python" 還是 json 類型的 ""python""::json都會拋出異常，json 類型不支持 等號（=）操作符
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::json#>"{tags,0}" = ""python"";
ERROR: operator does not exist: json = unknown
額外的jsonb操作符
-- nickname 為 nickname 的用戶
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"nickname": "gs"}"::jsonb;
-- 等同于以下查詢
-- 這里使用 -> 查出的數據為json 對象，所以匹配項也應該是json 對象
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"nickname" = ""gs"";
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->>"nickname" = "gs";
-- 查詢有 python 和 golang 標簽的數據
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb @> "{"tags": ["python", "golang"]}";
?column?
----------
t
-- 查詢有 avatar 屬性的用戶
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb ? "avatar";
-- 查詢有 avatar 屬性 并且avatar 數據不為空的數據
select "{"nickname": "gs", "avatar": null, "tags": ["python", "golang", "db"]}"::jsonb->>"avatar" is not null;
-- 查詢 有 avatar 或 tags 的數據
select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?| array["avatar", "tags"];
?column?
----------
t
-- 查詢 既有 avatar 又有 tags 的用戶
select "{"nickname": "gs", "tags": ["python", "golang", "db"]}"::jsonb ?& array["avatar", "tags"];
?column?
----------
f
-- 查詢 tags 中包含 python 標簽的數據
select "{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}"::jsonb->"tags" ? "python";
?column?
----------
t
json 更新
-- 更新 account content 字段（覆蓋式更新）
update account set content = jsonb_set(content, "{}", "{"nickname": "gs", "tags": ["python", "golang", "db"]}", false);
-- 修改nickanme為nickanme 的用戶標簽
update account set content = jsonb_set(content, "{tags}", "["test", "心理"]", true) where content @> "{"nickname": "nickname"}"::jsonb;
update account set content = jsonb_set(content, "{tags}", "["test", "心理", "醫療"]", true) where content @> "{"nickname": "nickname"}"::jsonb;
-- 更新account content字段中 weixin_mp 的值（如果沒有會創建）
update account set content = jsonb_set(content, "{weixin_mp}", ""weixin_mp5522bd28-ed4d-11e8-949c-7200014964f0"", true) where id="5522bd28-ed4d-11e8-949c-7200014964f0";
-- 更新account 去除content 中weixin 字段（如果沒有weixin 字段也不會拋出異常）
update account set content= content - "weixin" where id="5522bd28-ed4d-11e8-949c-7200014964f0";
json 函數
jsonb_pretty
作為縮進JSON文本返回from_json。
select jsonb_pretty("[{"f1":1,"f2":null},2,null,3]");
jsonb_pretty
--------------------
[ +
{ +
"f1": 1, +
"f2": null+
}, +
2, +
null, +
3 +
]
(1 row)
jsonb_set
jsonb_set() 函數參數如下：
jsonb_set(target jsonb, // 需要修改的數據
path text[], // 數據路徑
new_value jsonb, // 新數據
create_missing boolean default true)
如果create_missing 是true （缺省是true），并且path指定的路徑在target 中不存在，那么target將包含path指定部分， new_value替換部分， 或者new_value添加部分。
-- target 結構
select jsonb_pretty("[{"f1":1,"f2":null},2]");
jsonb_pretty
--------------------
[ +
{ +
"f1": 1, +
"f2": null+
}, +
2 +
]
-- 更新 target 第0 個元素 key 為 f1 的值，如果f1 不存在 忽略
select jsonb_set("[{"f1":1,"f2":null},2,null,3]", "{0,f1}","[2,3,4]", false);
jsonb_set
---------------------------------------------
[{"f1": [2, 3, 4], "f2": null}, 2, null, 3]
-- 更新 target 第0 個元素 key 為 f3 的值，如果f3 不存在 創建
select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]");
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null, "f3": [2, 3, 4]}, 2]
-- 更新 target 第0 個元素 key 為 f3 的值，如果f3 不存在 忽略
select jsonb_set("[{"f1":1,"f2":null},2]", "{0,f3}","[2,3,4]", false);
jsonb_set
---------------------------------------------
[{"f1": 1, "f2": null}, 2]
詳細的json 函數和操作符可以參考文檔：JSON 函數和操作符
jsonb 性能分析我們使用下面的例子來說明一下json 的查詢性能
表結構-- account 表 id 使用uuid 類型，需要先添加uuid-ossp模塊。
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- create table
create table account (id UUID NOT NULL PRIMARY KEY default uuid_generate_v1(), content jsonb, created_at timestamptz DEFAULT CURRENT_TIMESTAMP, updated_at timestamptz DEFAULT CURRENT_TIMESTAMP);
json=> d account
Table "public.account"
Column | Type | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+--------------------
id | uuid | | not null |uuid_generate_v1()
content | jsonb | | |
created_at | timestamp with time zone | | | CURRENT_TIMESTAMP
updated_at | timestamp with time zone | | | CURRENT_TIMESTAMP
Indexes:
"account_pkey" PRIMARY KEY, btree (id)
一個好的實踐是把 created_at和 updated_at 也放入jsonb 字段，這里只是示例
content 數據結構為：
content = {
"nickname": {"type": "string"},
"avatar": {"type": "string"},
"weixin": {"type": "string"},
"tags": {"type": "array", "items": {"type": "string"}},
}
準備數據
批量插入數據
-- 插入100w條有 nickname avatar tags 為["python", "golang", "c"]的數據
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*20000000) || "", "avatar": "avatar_url", "tags": ["python", "golang", "c"]}")::jsonb from (select * from generate_series(1,100000)) as tmp;
-- 插入100w條有 nickname tags 為["python", "golang"]的數據
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*2000000) || "", "tags": ["python", "golang"]}")::jsonb from (select * from generate_series(1,1000000)) as tmp;
-- 插入100w條有 nickname tags 為["python"]的數據
insert into account select uuid_generate_v1(), ("{"nickname": "nn-" || round(random()*2000000) || "", "tags": ["python"]}")::jsonb from (select * from generate_series(1,1000000)) as tmp;
測試查詢
EXPLAIN：顯示PostgreSQL計劃程序為提供的語句生成的執行計劃。
ANALYZE：收集有關數據庫中表的內容的統計信息。
--content 中有avatar key 的數據條數 count(*) 查詢不是一個好的測試語句，就算是有索引，也只能起到過濾的作用，如果結果集比較大，查詢速度還是會很慢
explain analyze select count(*) from account where content::jsonb ? "avatar";
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=29280.40..29280.41 rows=1 width=8) (actual time=170.366..170.366 rows=1 loops=1)
-> Gather (cost=29280.19..29280.40 rows=2 width=8) (actual time=170.119..174.451 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=28280.19..28280.20 rows=1 width=8) (actual time=166.034..166.034 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=0) (actual time=0.022..161.937 rows=33333 loops=3)
Filter: (content ? "avatar"::text)
Rows Removed by Filter: 400000
Planning Time: 0.048 ms
Execution Time: 174.486 ms
-- content 中沒有avatar key 的數據條數
explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.770..207.770 rows=1 loops=1)
-> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.681..212.357 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.565..203.565 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.050..163.629 rows=400000 loops=3)
Filter: (NOT (content ? "avatar"::text))
Rows Removed by Filter: 33333
Planning Time: 0.050 ms
Execution Time: 212.393 ms
--查詢content 中nickname 為nn-194318的數據
explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
QUERY PLAN
----------------------------------------------------------------------------------------
Gather (cost=1000.00..29408.83 rows=1300 width=100) (actual time=0.159..206.990 rows=1 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=542 width=100) (actual time=130.867..198.081 rows=0 loops=3)
Filter: (content @> "{"nickname": "nn-194318"}"::jsonb)
Rows Removed by Filter: 433333
Planning Time: 0.047 ms
Execution Time: 207.007 ms
-- 對應的查詢id 為 "b5b3ed06-7d35-11e9-b3ea-00909e9dab1d" 的數據
explain analyze select * from account where id="b5b3ed06-7d35-11e9-b3ea-00909e9dab1d";
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using account_pkey on account (cost=0.43..8.45 rows=1 width=100) (actual time=0.912..0.914 rows=1 loops=1)
Index Cond: (id = "b5b3ed06-7d35-11e9-b3ea-00909e9dab1d"::uuid)
Planning Time: 0.348 ms
Execution Time: 0.931 ms
通過結果可以看到 使用 jsonb 查詢和使用主鍵查詢速度差異巨大，通過看查詢分析記錄可以看到，這兩個語句最大的差別在于使用主鍵的查詢用到了索引，而content nickname 的查詢沒有索引可以使用。
接下來測試一下使用索引時的查詢速度。
JSONB 最常用的是GIN 索引，GIN 索引可以被用來有效地搜索在大量jsonb文檔（數據）中出現 的鍵或者鍵值對。
GIN(Generalized Inverted Index, 通用倒排索引) 是一個存儲對(key, posting list)集合的索引結構，其中key是一個鍵值，而posting list 是一組出現過key的位置。如(‘hello", "14:2 23:4")中，表示hello在14:2和23:4這兩個位置出現過，在PG中這些位置實際上就是元組的tid(行號，包括數據塊ID（32bit）,以及item point(16 bit) )。在表中的每一個屬性，在建立索引時，都可能會被解析為多個鍵值，所以同一個元組的tid可能會出現在多個key的posting list中。
通過這種索引結構可以快速的查找到包含指定關鍵字的元組，因此GIN索引特別適用于多值類型的元素搜索，比如支持全文搜索，數組中元素的搜索，而PG的GIN索引模塊最初也是為了支持全文搜索而開發的。
jsonb的默認 GIN 操作符類支持使用頂層鍵存在運算符?、?&以及?| 操作符和路徑/值存在運算符@>的查詢。
-- 創建默認索引 CREATE INDEX idxgin ON api USING GIN (jdoc);
非默認的 GIN 操作符類jsonb_path_ops只支持索引@>操作符。
-- 創建指定路徑的索引 CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); -- create index ix_account_content_nickname_gin on account using gin (content, (content->"nickname")); -- create index ix_account_content_tags_gin on account using gin (content, (content->"nickname")); -- create index ix_account_content_tags_gin on account using gin ((content->"tags"));多索引支持
PostgreSQL 擁有開放的索引接口，使得PG支持非常豐富的索引方法，例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum extend)，用戶可以根據不同的數據類型，以及查詢的場景，選擇不同的索引。查詢優化
創建默認索引
-- 創建簡單索引 create index ix_account_content on account USING GIN (content);
現在下面這樣的查詢就能使用該索引：
-- content 中有avatar key 的數據條數
explain analyze select count(*) from account where content::jsonb ? "avatar";
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=4180.49..4180.50 rows=1 width=8) (actual time=43.462..43.462 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=30.07..4177.24 rows=1300 width=0) (actual time=8.362..36.048 rows=100000 loops=1)
Recheck Cond: (content ? "avatar"::text)
Heap Blocks: exact=2032
-> Bitmap Index Scan on ix_account_content (cost=0.00..29.75 rows=1300 width=0) (actual time=8.125..8.125 rows=100000 loops=1)
Index Cond: (content ? "avatar"::text)
Planning Time: 0.078 ms
Execution Time: 43.503 ms
和之前沒有添加索引時速度提升了3倍。
-- 查詢content 中nickname 為nn-194318的數據
explain analyze select * from account where content@>"{"nickname": "nn-194318"}";
QUERY PLAN
----------------------------------------------------------------------------------------
Bitmap Heap Scan on account (cost=46.08..4193.24 rows=1300 width=100) (actual time=0.097..0.097 rows=1 loops=1)
Recheck Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
Heap Blocks: exact=1
-> Bitmap Index Scan on ix_account_content (cost=0.00..45.75 rows=1300 width=0) (actual time=0.091..0.091 rows=1 loops=1)
Index Cond: (content @> "{"nickname": "nn-194318"}"::jsonb)
Planning Time: 0.075 ms
Execution Time: 0.132 ms
這個查詢效率提升更明顯，竟然比使用主鍵還要高效。
但是下面這種查詢并不能使用索引：
-- 查詢content 中不存在 avatar key 的數據條數
explain analyze select count(*) from account where content::jsonb ? "avatar" = false;
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30631.86..30631.87 rows=1 width=8) (actual time=207.641..207.641 rows=1 loops=1)
-> Gather (cost=30631.65..30631.86 rows=2 width=8) (actual time=207.510..211.062 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29631.65..29631.66 rows=1 width=8) (actual time=203.739..203.739 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..28278.83 rows=541125 width=0) (actual time=0.024..163.444 rows=400000 loops=3)
Filter: (NOT (content ? "avatar"::text))
Rows Removed by Filter: 33333
Planning Time: 0.068 ms
Execution Time: 211.097 ms
該索引也不能被用于下面這樣的查詢，因為盡管操作符? 是可索引的，但它不能直接被應用于被索引列content：
explain analyze select count(1) from account where content -> "tags" ? "c";
QUERY PLAN
----------------------------------------------------------------------------------------
Finalize Aggregate (cost=30634.57..30634.58 rows=1 width=8) (actual time=184.864..184.864 rows=1 loops=1)
-> Gather (cost=30634.35..30634.56 rows=2 width=8) (actual time=184.754..189.652 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=29634.35..29634.36 rows=1 width=8) (actual time=180.755..180.755 rows=1 loops=3)
-> Parallel Seq Scan on account (cost=0.00..29633.00 rows=542 width=0) (actual time=0.022..177.051 rows=33333 loops=3)
Filter: ((content -> "tags"::text) ? "c"::text)
Rows Removed by Filter: 400000
Planning Time: 0.074 ms
Execution Time: 189.716 ms
使用表達式索引
-- 創建路徑索引 create index ix_account_content_tags on account USING GIN ((content->"tags"));
-- 測試查詢性能
explain analyze select count(1) from account where content -> "tags" ? "c";
QUERY PLAN
----------------------------------------------------------------------------------------
Aggregate (cost=4631.74..4631.75 rows=1 width=8) (actual time=49.274..49.275 rows=1 loops=1)
-> Bitmap Heap Scan on account (cost=478.07..4628.49 rows=1300 width=0) (actual time=8.655..42.074 rows=100000 loops=1)
Recheck Cond: ((content -> "tags"::text) ? "c"::text)
Heap Blocks: exact=2032
-> Bitmap Index Scan on ix_account_content_tags (cost=0.00..477.75 rows=1300 width=0) (actual time=8.417..8.417 rows=100000 loops=1)
Index Cond: ((content -> "tags"::text) ? "c"::text)
Planning Time: 0.216 ms
Execution Time: 49.309 ms
現在，WHERE 子句content -> "tags" ? "c" 將被識別為可索引操作符?在索引表達式content -> "tags" 上的應用。
也可以利用包含查詢的方式，例如：
-- 查尋 "tags" 包含數組元素 "c" 的數據的個數
select count(1) from account where content @> "{"tags": ["c"]}";
content 列上的簡單 GIN 索引（默認索引）就能支持索引查詢。 但是索引將會存儲content列中每一個鍵 和值的拷貝，
表達式索引只存儲tags 鍵下找到的數據。
雖然簡單索引的方法更加靈活（因為它支持有關任意鍵的查詢），但定向的表達式索引更小并且搜索速度比簡單索引更快。總結
盡管jsonb_path_ops操作符類只支持用 @>操作符的查詢，但它比起默認的操作符類 jsonb_ops有更客觀的性能優勢。一個 jsonb_path_ops索引通常也比一個相同數據上的 jsonb_ops要小得多，并且搜索的專一性更好，特 別是當查詢包含頻繁出現在該數據中的鍵時。因此，其上的搜索操作 通常比使用默認操作符類的搜索表現更好。
PG 有兩種 JSON 數據類型：json 和 jsonb，jsonb 性能優于json，且jsonb 支持索引。
jsonb 寫入時會處理寫入數據，寫入相對較慢，json會保留原始數據（包括無用的空格）
jsonb 查詢優化時一個好的方式是添加GIN 索引
簡單索引和路徑索引相比更靈活，但是占用空間多
路徑索引比簡單索引更高效，占用空間更小
參考鏈接RFC 7159 The JavaScript Object Notation (JSON) Data Interchange Format
PostgreSQL 文檔： JSON 類型
JSON 函數和操作符
How do I modify fields inside the new PostgreSQL JSON datatype?
PostgreSQL 9種索引的原理和應用場景
PostgreSQL GIN索引實現原理
PostgreSQL internals: JSONB type and its indexes
倒排索引
最后，感謝女朋友支持和包容，比??
也可以在公號輸入以下關鍵字獲取歷史文章：公號&小程序 | 設計模式 | 并發&協程
文章版權歸作者所有，未經允許請勿轉載,若此文章存在違規行為，您可以聯系管理員刪除。
轉載請注明本文地址：http://specialneedsforspecialkids.com/yun/39043.html
摘要：類型說明根據中的說明，數據類型是用來存儲數據的。它們幾乎接受完全相同的值集合作為輸入。該結構是非強制的，但是有一個可預測的結構會使集合的查詢更容易。如中，表示在和這兩個位置出現過，在中這些位置實際上就是元組的行號，包括數據塊以及。 json 類型 說明 根據RFC 7159中的說明，JSON 數據類型是用來存儲 JSON（JavaScript Object Notation）數據的。這...
摘要：最近在公司接觸到了的框架，研究了一下官方文檔，做了以下整理其他定義方法字段類型是否允許為字段是否自定義表名是否需要增加字段不需要字段將字段改個名將字段改名同時需要設置為此種模式下，刪除數據時不會進行物理刪除，而是設置為當前時間 最近在公司接觸到了sequelize(Nodejs的ORM框架)，研究了一下官方文檔，做了以下整理 Models Definition let DeviceIn...
摘要：但這些不會發生列中，這種查找使用了索引，卻并沒有像使用表達式索引那樣將速度優化的很好。這也使得數據驗證和創建表單更加簡單。在每一個新版本中，使用和都比過去更加容易，變得更加出色。因此，嘗試使用最新的版本，付出總是會很快得到回報的。 轉載翻譯自http://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails Pos...
閱讀 2878·2021-09-22 15:54
閱讀 1886·2019-08-30 15:53
閱讀 2240·2019-08-29 16:33
閱讀 1417·2019-08-29 12:29
閱讀 1387·2019-08-26 11:41
閱讀 2367·2019-08-26 11:34
閱讀 2947·2019-08-23 16:12
閱讀 1421·2019-08-23 15:56
