json - 在 PostgreSQL 9.3 中的 json 字段的嵌套属性上创建索引的多种方法

Question

在 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() 调用时，查询优化器应该(至少)将索引与 #>> 运算符一起使用——反之亦然.

如果我想在我的应用程序中使用更多这些表达式(不只是一个，f.ex. 坚持使用 -> 和 ->> 运算符)，我应该建立什么索引？ (我希望，不是全部。)

有没有可能，一些 future 的 Postgres 版本的优化器会理解这些表达式的等价性？

编辑:

当我为此创建一个额外的运算符时:

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

这个查询(上一个例子中的表)仍然没有使用它的索引:

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

奖励问题:

虽然 Postgres 将运算符扩展为函数调用(在幕后)，但为什么它仍然不使用其索引？

Answer 1

您必须为 JSON 和 JSONB 数据类型使用 GIN 索引。您可以为计划的查询使用运算符参数示例:

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);

其他选择是documented on postgresql site

我想你可以用这个:

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