使用 Postgres 9.4,我想在 json 列上创建一个索引,该索引将在搜索列中的特定键时使用。
例如,我有一个带有 json 列"动物"的"农场"表。
动物列具有常规格式的 json 对象:
'{"cow": 2, "chicken": 11, "horse": 3}'
我已经尝试了许多索引(分别):
-
create INDEX animal_index ON farm ((animal ->> 'cow'));
-
create INDEX animal_index ON farm using gin ((animal ->> 'cow'));
-
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 只动物,查询对于我的用例来说太长了。像这样的索引是我能想到的加快此查询速度的唯一方法,但也许还有另一种选择。
你的其他两个索引不会工作,因为->>
运算符返回text
,而你显然想到了jsonb
杜松子酒运算符类。请注意,您只提到了json
,但实际上需要jsonb
才能获得高级索引功能。
制定最佳索引策略,您必须更紧密地定义要涵盖的查询。你只对奶牛感兴趣吗?还是所有动物/所有标签?哪些运算符是可能的?您的 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中查询数组中的结构的正确索引是什么?
对于已知的、静态的、微不足道的动物数量(如您所评论的),我建议使用部分索引,例如:
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
测试!