JSON上的PostgreSQL索引

Question

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

例如，我有一个带有json列“ animals”的“农场”表。

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个农场，但是其中一些包含100个动物，查询对于我的用例而言花费的时间太长。这样的索引是我可以想到的唯一加快查询速度的方法，但是也许还有另一种选择。

Answer 1

您的另外两个索引不能仅仅因为->>运算符 return text
而工作，而显然您要jsonb记住gin运算符类。请注意，您仅提及json，但实际上您需要
jsonb
高级索引功能。

为了制定出最佳的索引策略，您必须更仔细地定义要覆盖的查询。您只对奶牛感兴趣吗？还是所有动物/所有标签？哪些运营商是可能的？您的JSON文档是否还包含非动物密钥？这些怎么办？您是否要在索引中包括母牛（或其他人）根本不在JSON文档中显示的行？

假设：

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

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

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

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

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

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

对于 已知的，静态的，琐碎 的动物数量（如您所评论的），我建议使用部分索引，例如：

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;

等等。

您可能必须将索引条件添加到查询中：

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

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