Индекс PostgreSQL на JSON

Используя Postgres 9.4, я хочу создать индекс в столбце json, который будет использоваться при поиске по определенным клавишам в столбце.

Например, у меня есть таблица "farm" с json-столбцом "животные".

В столбце "Животные" есть 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 животных, и запросы просто слишком долго для моего варианта использования. Индекс, подобный этому, является единственным методом, который я могу придумать для ускорения этого запроса, но, возможно, есть и другой вариант.

Ответы

Ответ 1

Два других индекса не будут работать просто потому, что оператор ->> возвращает 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;

Если вам нужен более общий индекс 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;

Etc.

Возможно, вам придется добавить условие условия к запросу:

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

Может показаться излишним, но может оказаться необходимым. Проверить с помощью ANALYZE!