PostgreSQL教程 -》数据类型 -》jsonb 索引

8.14.4. jsonb 索引

GIN 索引可以被用来有效地搜索在大量jsonb文档(数据)中出现 的键或者键值对。提供了两种 GIN “操作符类”，它们在性能和灵活 性方面做出了不同的平衡。

jsonb的默认 GIN 操作符类支持使用@>、 ?、?&以及?|操作符的查询(这些 操作符实现的详细语义请见表 9.45)。 使用这种操作符类创建一个索引的例子：

CREATE INDEX idxgin ON api USING gin (jdoc);

非默认的 GIN 操作符类jsonb_path_ops只支持索引 @>操作符。使用这种操作符类创建一个索引的例子：

CREATE INDEX idxginp ON api USING gin (jdoc jsonb_path_ops);

考虑这样一个例子：一个表存储了从一个第三方 Web 服务检索到的 JSON 文档，并且有一个模式定义。一个典型的文档：

{

"guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",

"name": "Angela Barton",

"is_active": true,

"company": "Magnafone",

"address": "178 Howard Place, Gulf, Washington, 702",

"registered": "2009-11-07T08:53:22 +08:00",

"latitude": 19.793713,

"longitude": 86.513373,

"tags": [

"enim",

"aliquip",

"qui"

]

}

我们把这些文档存储在一个名为api的表的名为 jdoc的jsonb列中。如果在这个列上创建一个 GIN 索引，下面这样的查询就能利用该索引：

-- 寻找键 "company" 有值 "Magnafone" 的文档

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

不过，该索引不能被用于下面这样的查询，因为尽管操作符? 是可索引的，但它不能直接被应用于被索引列jdoc：

-- 寻找这样的文档：其中的键 "tags" 包含键或数组元素 "qui"

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

但是，通过适当地使用表达式索引，上述查询也能使用一个索引。 如果对"tags"键中的特定项的查询很常见，可能值得 定义一个这样的索引：

CREATE INDEX idxgintags ON api USING gin ((jdoc -> 'tags'));

现在，WHERE 子句 jdoc -> 'tags' ? 'qui' 将被识别为可索引操作符?在索引表达式jdoc -> 'tags' 上的应用(更多有关表达式索引的信息可见第 11.7 节)。

此外, GIN 索引支持 @@ 和 @?运算符, 以执行 jsonpath 匹配。

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @? '$.tags[*] ? (@ == "qui")';

GIN 索引从jsonpath中提取如下格式的语句： accessors_chain = const。 存取器链可能由.key[*], 和 [index] 存取器组成。 jsonb_ops 此外还支持 .* 和 .** 存取器。

另一种查询的方法是利用包含，例如：

-- 寻找这样的文档：其中键 "tags" 包含数组元素 "qui"

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

jdoc列上的一个简单 GIN 索引就能支持这个查询。 但是注意这样一个索引将会存储jdoc列中每一个键 和值的拷贝，然而前一个例子的表达式索引只存储tags 键下找到的数据。虽然简单索引的方法更加灵活(因为它支持有关任 意键的查询)，定向的表达式索引更小并且搜索速度比简单索引更快。

尽管jsonb_path_ops操作符类只支持用 @>，@@和@?操作符的查询，但它比起默认的操作符类 jsonb_ops有更客观的性能优势。一个 jsonb_path_ops索引通常也比一个相同数据上的 jsonb_ops要小得多，并且搜索的专一性更好，特 别是当查询包含频繁出现在该数据中的键时。因此，其上的搜索操作 通常比使用默认操作符类的搜索表现更好。

jsonb_ops和jsonb_path_ops GIN 索引之间的技术区别是前者为数据中的每一个键和值创建独立的索引项， 而后者值为该数据中的每个值创建索引项。 [6] 基本上，每一个jsonb_path_ops索引项是其所对应的值和 键的哈希。例如要索引{"foo": {"bar": "baz"}}，将创建一个 单一的索引项，它把所有三个foo、bar、 和baz合并到哈希值中。因此一个查找这个结构的包含查询可能 导致极度详细的索引搜索。但是根本没有办法找到foo是否作为 一个键出现。在另一方面，一个jsonb_ops会创建三个索引 项分别表示foo、bar和baz。那么要 做同样的包含查询，它将会查找包含所有三个项的行。虽然 GIN 索引能够相当 有效地执行这种 AND 搜索，它仍然不如等效的 jsonb_path_ops搜索那样详细和快速(特别是如果有大量 行包含三个索引项中的任意一个时)。

jsonb_path_ops方法的一个不足是它不会为不包含任何值 的 JSON 结构创建索引项，例如{"a": {}}。如果需要搜索包 含这样一种结构的文档，它将要求一次全索引扫描，那就非常慢。 因此jsonb_path_ops不适合经常执行这类搜索的应用。

jsonb也支持btree和hash索引。 这通常值用于检查完整 JSON 文档等值非常重要的场合。jsonb 数据的btree顺序很少有人关系，但是为了完整性其顺序是：

对象 > 数组 > 布尔 > 数字 > 字符串 > 空值

带有 n 对的对象 > 带有 n - 1 对的对象

带有 n 个元素的数组 > 带有 n - 1 个元素的数组

具有相同数量对的对象这样比较：

key-1, value-1, key-2 ...

注意对象键被按照它们的存储顺序进行比较，特别是由于较短的键被存储在 较长的键之前，这可能导致结果不直观，例如：

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

相似地，具有相同元素数量的数组按照以下顺序比较：

element-1, element-2 ...

基本 JSON 值的比较会使用低层PostgreSQL 数据类型相同的比较规则进行。字符串的比较会使用默认的数据库排序规则。