Несколько способов создания индекса для вложенного свойства поля json в PostgreSQL 9.3

В PostgreSQL 9.3 существует несколько способов построения выражения, которое указывает на вложенное свойство поля json:

data->'foo'->>'bar'
data#>>'{foo,bar}'
json_extract_path_text(data, 'foo', 'bar')

Поэтому PostgreSQL использует эти индексы только в том случае, если выражение запроса точно совпадает с выражением индекса.

CREATE TABLE json_test_index1(data json);
CREATE TABLE json_test_index2(data json);
CREATE TABLE json_test_index3(data json);

CREATE INDEX ON json_test_index1((data->'foo'->>'bar'));
CREATE INDEX ON json_test_index2((data#>>'{foo,bar}'));
CREATE INDEX ON json_test_index3((json_extract_path_text(data, 'foo', 'bar')));

-- these queries use an index, while all other combinations not:

EXPLAIN SELECT * FROM json_test_index1 WHERE data->'foo'->>'bar' = 'baz';
EXPLAIN SELECT * FROM json_test_index2 WHERE data#>>'{foo,bar}' = 'baz';
EXPLAIN SELECT * FROM json_test_index3 WHERE json_extract_path_text(data, 'foo', 'bar') = 'baz';

Мои вопросы:

Это поведение предназначено? Я думал, что оптимизатор запросов должен (как минимум) использовать индекс с оператором #>>, когда запрос содержит соответствующий вызов json_extract_path_text() -- и наоборот.

Если я хочу использовать больше этих выражений в своем приложении (не одно, например, придерживаться операторов -> и ->>), какие индексы мне следует построить? (надеюсь, не все).

Есть ли шанс, что некоторые оптимизаторы будущих версий Postgres поймут эквивалентность этих выражений?

ИЗМЕНИТЬ:

Когда я создаю для этого дополнительный оператор:

CREATE OPERATOR ===> (
    PROCEDURE = json_extract_path_text,
    LEFTARG = json,
    RIGHTARG = text[]
);

Этот запрос (таблица из предыдущего примера) по-прежнему не использует свой индекс:

EXPLAIN SELECT * FROM json_test_index3 WHERE data ===> '{foo,bar}' = 'baz';

Бонусный вопрос:

В то время как Postgres расширяет операторы до вызовов функций (за кулисами), почему он до сих пор не использует свой индекс?


postgresql json indexing postgresql-9.3 postgresql-json
person pozs    schedule 24.04.2014    source источник
comment
В текущей версии (9.3) это предусмотрено (т.е. просто не реализовано). 9.4 будет включать значительное улучшение внутреннего хранения JSON, что позволит использовать его для индексов GIN. И тогда оператор #>> должен иметь возможность использовать индекс (аналогично текущим возможностям при индексировании типа данных hstore). Если у вас есть плоские пары ключ/значение, которые вам нужно проиндексировать, в настоящее время единственным вариантом является hstore (или дождитесь версии 9.4).   -  person a_horse_with_no_name    schedule 24.04.2014
comment
@a_horse_with_no_name Хотя индексы GIN для jsonb полей дадут гораздо более гибкий индекс, это будет стоить производительности. Мне нужно запросить только несколько свойств внутри объекта json, а собственные индексы определенных свойств должны работать быстрее. Мне просто интересно, если есть несколько способов получить свойство, почему мне нужно создать индекс для всех из них, чтобы использовать один (в каждом случае). Или я что-то упускаю?   -  person pozs    schedule 24.04.2014
comment
Нет, вы ничего не упускаете. Индекс на основе функции используется только в том случае, если запрос содержит то же выражение, что и выражение, использованное в индексе. Что касается аргумента затрат: я думаю, что затраты на один индекс GIN в столбце jsonb, вероятно, будут меньше, чем объединенные затраты на несколько индексов (по одному для каждого свойства), которые могут использоваться только одним выражением (один именно для этого свойство). Но в настоящее время я не вижу способа избежать этого (при условии, что вам нужно придерживаться JSON).   -  person a_horse_with_no_name    schedule 24.04.2014
comment
@a_horse_with_no_name объединенная стоимость для нескольких индексов — справедливое замечание. Я могу дождаться индексов GIN.   -  person pozs    schedule 24.04.2014

Ответы (1)


arrow_upward
0
arrow_downward

Вы должны использовать индекс GIN для типов данных JSON и JSONB. Вы можете использовать параметры оператора для запланированного запроса Примеры:

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field);

Если вы планируете использовать только оператор @>, вы можете использовать параметр jsonb_path_ops.

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field jsonb_path_ops);

Другие варианты задокументированы на сайте postgresql

Я думаю, вы можете использовать это:

CREATE INDEX idx_tbl_example ON tbl_example USING GIN(your_jsonb_field json_extract_path_text);
person Ahmet Erkan ÇELİK    schedule 20.05.2019