JSON上的PostgreSQL索引

本文介绍了JSON上的PostgreSQL索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

使用Postgres 9.4 ，我想在json列上创建索引，该索引将用于搜索列中的特定键.

Using Postgres 9.4, I want to create an index on a json column that will be used when searching on specific keys within the column.

例如，我有一个带有json列"animals"的农场"表.

For example I have an 'farm' table with a json column 'animals'.

动物"列具有通用格式的json对象:

The animals column has json objects of the general format:

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

我已经尝试了多个索引(分别):

I have tried a number of indexes (separately):

(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)索引有效，但我无法获得任何索引来解决不等式.

then the (1) index works, but I can't get any of the indexes to work for the inequality.

这样的索引可能吗?

服务器场表仅包含约5000个服务器场，但其中一些服务器包含100多个动物，而对于我的用例而言，查询仅花费太长时间.这样的索引是我想加快查询速度的唯一方法，但也许还有另一种选择.

The farm table contains only ~5000 farms, but some of them contain 100s of animals and the queries simply take too long for my use case. An index like this is the only method I can think of for speeding this query up, but perhaps there is another option.

推荐答案

您的其他两个索引仅因为 ->>运算符返回 text ，而您显然拥有jsonb gin运算符类心里.请注意，您仅提及json，但实际上您需要 jsonb 获取高级索引功能.

Your other two indexes won't work simply because the ->> operator returns text, while you obviously have the jsonb gin operator classes in mind. Note that you only mention json, but you actually need jsonb for advanced indexing capabilities.

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

To work out the best indexing strategy, you'd have to define more closely which queries to cover. Are you only interested in cows? Or all animals / all tags? Which operators are possible? Does your JSON document also include non-animal keys? What to do with those? Do you want to include rows in the index where cows (or whatever) don't show up in the JSON document at all?

假设:

Assuming:

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

我建议使用功能性btree索引，就像您已经拥有的一样，但是将值强制转换为整数.我不认为您希望比较的评估结果为text(其中"2"大于"1111").

I suggest a functional btree index, much like you already have, but cast the value to integer. I don't suppose you'd want the comparison evaluated as text (where '2' is greater than '1111').

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

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

The extra set of parentheses is required for the cast shorthand to make the syntax for the index expression unambiguous.

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

Use the same expression in your queries to make Postgres realize the index is applicable:

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

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

If you need a more generic jsonb index, consider:

• 是什么在Postgres jsonb中查询数组中结构的正确索引?

对于数量众多的，已知的，静态的，琐碎的动物(如您所评论的)，我建议使用部分索引，例如:

For a known, static, trivial number of animals (like you commented), I suggest partial indexes like:

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;

等等.

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

You may have to add the index condition to the query:

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

看似多余，但可能是必要的.用ANALYZE进行测试！

May seem redundant, but may be necessary. Test with ANALYZE!