JSON 上的 PostgreSQL 索引

使用 Postgres9.4，我想在 json 列上创建一个索引，该索引将在搜索列中的特定键时使用。

例如，我有一个“农场”表，其中包含 json 列“动物”。

Animals 列具有通用格式的 json 对象：

'{"cow": 2, "chicken": 11, "horse": 3}'

我已经尝试了一些索引（分别）：

1. create INDEX animal_index ON farm ((animal ->> 'cow'));
2. create INDEX animal_index ON farm using gin ((animal ->> 'cow'));
3. create INDEX animal_index ON farm using gist ((animal ->> 'cow'));

我想运行如下查询：

SELECT * FROM farm WHERE (animal ->> 'cow') > 3;

并让该查询使用索引。

当我运行此查询时：

SELECT * FROM farm WHERE (animal ->> 'cow') is null;

那么 (1) 索引有效，但我无法让任何索引适用于不等式。

这样的索引可能吗？

农场表仅包含约 5000 个农场，但其中一些包含数百只动物，对于我的用例来说，查询花费的时间太长。像这样的索引是我能想到的加快查询速度的唯一方法，但也许还有另一种选择。

您的其他两个索引将不起作用，因为->>操作员 https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-OP-TABLE回报text，而你显然有jsonb考虑到杜松子酒操作员类别。请注意，您只提到json，但你实际上需要jsonb https://www.postgresql.org/docs/current/datatype-json.html用于高级索引功能。

要制定最佳索引策略，您必须更仔细地定义要覆盖的查询。你只对牛感兴趣吗？或者所有动物/所有标签？哪些运营商是可能的？您的 JSON 文档是否还包含非动物密钥？那些该怎么办？您是否希望在索引中包含牛（或其他内容）根本不会出现在 JSON 文档中的行？

假设：

• 我们只对第一层筑巢的奶牛感兴趣。
• 该值始终有效integer.
• 我们对没有奶牛的行不感兴趣。

我建议使用功能性 btree 索引，就像您已经拥有的那样，但将值转换为integer。我认为您不希望将比较评估为text（其中“2”大于“1111”）。

CREATE INDEX animal_index ON farm (((animal ->> 'cow')::int));  -- !

强制转换速记需要额外的括号，以使索引表达式的语法明确。

在查询中使用相同的表达式让 Postgres 意识到索引是适用的：

SELECT * FROM farm WHERE (animal ->> 'cow')::int > 3;

如果您需要更通用的jsonb索引，考虑：

• 在 Postgres jsonb 中查询数组中的结构的正确索引是什么？ https://stackoverflow.com/questions/26499266/whats-the-proper-index-for-querying-structures-in-arrays-in-postgres-jsonb/27708358#27708358

For a 已知的、静态的、琐碎的动物的数量（就像你评论的那样），我建议部分索引，例如：

CREATE INDEX animal_index ON farm (((animal ->> 'cow')::int))
WHERE (animal ->> 'cow') IS NOT NULL;

CREATE INDEX animal_index ON farm (((animal ->> 'chicken')::int))
WHERE (animal ->> 'chicken') IS NOT NULL;

Etc.

您可能需要将索引条件添加到查询中：

SELECT * FROM farm
WHERE (animal ->> 'cow')::int > 3
AND   (animal ->> 'cow') IS NOT NULL; 

可能看起来多余，但可能是必要的。测试用ANALYZE!