jsonb в Postgres 9.4+
Бинарный тип данных JSON jsonb значительно улучшает параметры индекса. Теперь вы можете иметь индекс GIN непосредственно в массиве jsonb:
CREATE TABLE tracks (id serial, artists jsonb); -- !
CREATE INDEX tracks_artists_gin_idx ON tracks USING gin (artists);
Нет необходимости в функции для преобразования массива. Это поддержит запрос:
SELECT * FROM tracks WHERE artists @> '[{"name": "The Dirty Heads"}]';
@> является jsonb содержит оператор, который может использовать индекс GIN. (Не для json, только jsonb!)
Или вы используете более специализированный класс операторов GIN, отличный от стандартного jsonb_path_ops для индекса:
CREATE INDEX tracks_artists_gin_idx ON tracks
USING gin (artists jsonb_path_ops); -- !
Тот же запрос.
В настоящее время jsonb_path_ops поддерживает только оператор @>. Но обычно он намного меньше и быстрее. Есть другие варианты индексации, подробности в руководстве .
Если столбец artists содержит только имена, показанные в примере, было бы более эффективно хранить только значения в виде текста JSON примитивов и избыточный ключ может быть именем столбца.
Обратите внимание на разницу между объектами JSON и примитивными типами:
CREATE TABLE tracks (id serial, artistnames jsonb);
INSERT INTO tracks VALUES (2, '["The Dirty Heads", "Louis Richards"]');
CREATE INDEX tracks_artistnames_gin_idx ON tracks USING gin (artistnames);
Запрос:
SELECT * FROM tracks WHERE artistnames ? 'The Dirty Heads';
? не работает для значений объекта, только для ключей и элементов массива.
Или:
CREATE INDEX tracks_artistnames_gin_idx ON tracks
USING gin (artistnames jsonb_path_ops);
Запрос:
SELECT * FROM tracks WHERE artistnames @> '"The Dirty Heads"'::jsonb;
Более эффективно, если имена сильно дублируются.
json в Postgres 9.3+
Это должно работать с IMMUTABLE функцией:
CREATE OR REPLACE FUNCTION json2arr(_j json, _key text)
RETURNS text[] LANGUAGE sql IMMUTABLE AS
'SELECT ARRAY(SELECT elem->>_key FROM json_array_elements(_j) elem)';
Создайте этот функциональный индекс:
CREATE INDEX tracks_artists_gin_idx ON tracks
USING gin (json2arr(artists, 'name'));
И используйте такой запрос. Выражение в предложении WHERE должно совпадать с выражением в индексе:
SELECT * FROM tracks
WHERE '{"The Dirty Heads"}'::text[] <@ (json2arr(artists, 'name'));
Обновлено с учетом отзывов в комментариях. Нам необходимо использовать операторы массива для поддержки индекс GIN.
содержится оператором <@ в этом случае.
Примечания по изменчивости функций
Вы можете объявить свою функцию IMMUTABLE, даже если json_array_elements() не не было.
Большинство JSON функций раньше были только STABLE, а не IMMUTABLE. Было обсуждение хакеров список, чтобы изменить это. Большинство из них сейчас IMMUTABLE. Проверить с:
SELECT p.proname, p.provolatile
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'pg_catalog'
AND p.proname ~~* '%json%';
Функциональные индексы работают только с IMMUTABLE функциями.
person
Erwin Brandstetter
schedule
23.08.2013
