索引——对于json与jsonb

json

原封不动存到数据库，写入会很快，内存占用小，但检索很慢

用btree 对其中某个键值对创建索引

CREATE INDEX CONCURRENTLY json_params_id_idx 
ON t_message 
USING btree (json_extract_path_text(json_params, 'id'));

或者

CREATE INDEX CONCURRENTLY json_params_name_idx 
ON t_message 
USING btree ((json_params->>'name'))

这样查询的时候，就用相同的方式

SELECT *
FROM t_message
WHERE json_extract_path_text(json_params, 'id') = '123'

或者

SELECT *
FROM t_message
WHERE json_params->>'name' = 'jack'

jsonb
- 解析成二进制格式存储，写入较慢，内存占用大，但检索很快
- 用GIN 对所有键值对创建索引
  - 如果不指定操作符，默认会使用 jsonb_ops 方式创建
    - 占用空间大，会为每个key和value都创建索引项
      - 比如{"foo":{"bar":"baz"}}
      - 会给这三个单词，分别创建索引项
```
CREATE INDEX CONCURRENTLY jsonb_params_idxgin 
ON t_message 
USING GIN (jsonb_params);
```
  - 最好指定jsonb_path_ops
    - 这样会把foo、bar、baz组合成一个hash值作为索引项，节约空间
```
CREATE INDEX CONCURRENTLY jsonb_params_idxgin
ON t_message 
USING GIN (jsonb_params jsonb_path_ops);
```
    - 对于value是数组的项
      - 比如{"member":["less","more"]}
      - 会把 member、less 和 member、more 分别组成索引项，可以查数组中某一元素
- 对GIN索引查询的错误方式(不会走索引)
```
SELECT *
FROM t_message
WHERE jsonb_params->>'name' = 'jack'
```
  - 这样不会走索引!!!
- 正确方式
```
SELECT *
FROM t_message
WHERE jsonb_params @> '{"name":jack}'
```
  - 如果查的是数组中的某一元素
```
SELECT *
FROM t_message
WHERE jsonb_params @> '{"bar":["baz"]}'
```
- 另一种错误方式(不会走索引)
```
SELECT *
FROM t_message
WHERE jsonb_params->'name' ？'jack'
```
  - 但如果在创建的时候，如下指定name列创建GIN索引，还是可以走索引的
```
CREATE INDEX CONCURRENTLY jsonb_params_idxgin
ON t_message 
USING GIN (jsonb_params->'name');
```

Keyboard shortcuts

索引——对于json与jsonb