2.4、jsonb索引使用

TBase为文档jsonb提供了GIN索引，GIN 索引可以被用来有效地搜索在大量jsonb文档（数据）中出现 的键或者键值对。

2.4.1、创建立jsonb索引

  1. postgres=# create index t_jsonb_f_jsonb_idx on t_jsonb using gin(f_jsonb);
  2. CREATE INDEX
  4. postgres=# \d+ t_jsonb
  5.                                    Table "public.t_jsonb"
  6.  Column  |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description 
  7. ---------+---------+-----------+----------+---------+----------+--------------+-------------
  8.  id      | integer |           |          |         | plain    |              | 
  9.  f_jsonb | jsonb   |           |          |         | extended |              | 
  10. Indexes:
  11.     "t_jsonb_f_jsonb_idx" gin (f_jsonb)
  12. Distribute By: SHARD(id)
  13. Location Nodes: ALL DATANODES

2.4.2、测试查询的性能

  1. postgres=# select count(1) from t_jsonb;
  2.   count   
  3. ----------
  4.  10000000
  5. (1 row)
  7. postgres=# analyze t_jsonb;
  8. ANALYZE

—没有索引开销

  1. postgres=# select * from t_jsonb where f_jsonb @> '{"col1":9999}';
  2.   id  |            f_jsonb             
  3. ------+--------------------------------
  4.  9999 | {"col1": 9999, "col2": "9999"}
  5.  9999 | {"col1": 9999, "col2": "9999"}
  6.  9999 | {"col1": 9999, "col2": "9999"}
  7.  9999 | {"col1": 9999, "col2": "9999"}
  8.  9999 | {"col1": 9999, "col2": "9999"}
  9. (5 rows)
  11. Time: 2473.488 ms (00:02.473)

—有索引开销

  1. postgres=# select * from t_jsonb where f_jsonb @> '{"col1":9999}';
  2.   id  |            f_jsonb             
  3. ------+--------------------------------
  4.  9999 | {"col1": 9999, "col2": "9999"}
  5.  9999 | {"col1": 9999, "col2": "9999"}
  6.  9999 | {"col1": 9999, "col2": "9999"}
  7.  9999 | {"col1": 9999, "col2": "9999"}
  8.  9999 | {"col1": 9999, "col2": "9999"}
  9. (5 rows)
  11. Time: 217.968 ms