postgresql - 如何使用 PostgreSQL 高效地索引 JSONB 结构中的多个嵌套数字以进行高效的比较操作？（可选：SQLAlchemy）

Question

我想确保 PostgreSQL 索引在多个嵌套数字上的 JSONB 内正常工作（B-Tree，以允许对数字进行有效的大于/小于操作）。JSONB“数据”列如下所示：

data: {
    a: {n: 1000, str: 'blabla'},
    b: {n: 2000, str: 'blabla'},
    c: {n: 3000, str: 'blabla'},
    d: {n: 4000, str: 'blabla'},

    ...[we can assume 10 such nested dicts]
}

我会根据多个嵌套数字的组合选择行，例如：

WHERE data['a']['n'] == 1000
AND data['b']['n'] == 2000
AND data['c']['n'] >= 3000
AND data['d']['n'] <= 4000

并添加多个 ORDER BY，例如：

ORDER BY DESC(data['a']['n']) + DESC(data['b']['n']) etc.

实现基于a、b、c、d层次结构和嵌套数字'n'的升序或降序排序。

我在下面放了一些代码，但我不知道索引是否按预期工作，我想知道这是否是正确的方法，或者是否有更好的方法来实现这一点？（理想情况下使用 JSONB）

我正在使用 PostgreSQL 11（带有 SQLAlchemy ORM），因此表和索引声明如下所示：

class TableWithJSONB(db.Base):
    __tablename__ = 'tablewithjsonb'
    id = Column(Integer, primary_key=True)
    data = Column(NestedMutable.as_mutable(JSONB), nullable=False)

    __table_args__ = ( # Adding Indexes
        # GIN using jsonb_path_ops => are these indexes useful?
        Index(
            'ix_data_a_gin',
            text("(data->'a') jsonb_path_ops"),
            postgresql_using='gin',
        ),
        Index(
            'ix_data_b_gin',
            text("(data->'b') jsonb_path_ops"),
            postgresql_using='gin',
        ),
        Index(
            'ix_data_c_gin',
            text("(data->'c') jsonb_path_ops"),
            postgresql_using='gin',
        ),
        ...


        # BTree Indexes on nested numbers
        Index(
            'ix_data_a_bTree',
            text("((data #> '{a, n}')::INTEGER) int4_ops"),
        ),
        # BTree Indexes on nested numbers
        Index(
            'ix_data_b_bTree',
            text("((data #> '{b, n}')::INTEGER) int4_ops"),
        ),
        # BTree Indexes on nested numbers
        Index(
            'ix_data_c_bTree',
            text("((data #> '{c, n}')::INTEGER) int4_ops"),
        ),
        ...
    )

在阅读了关于该主题的内容后，我不确定对于 JSONB 中的每个嵌套数值，b-Tree 索引是否真的按预期工作。此外，对于上述用法，我无法判断 GIN jsonb_path_ops 索引对嵌套字典 a、b、c、d 是否有意义。这是正确的方法还是有更好的方法？

更新：我似乎已经回答了我自己的问题。在此处查看dbfiddle

在 JSONB 中索引嵌套数值（使用 b-Tree 索引）：

CREATE INDEX i_btree_a ON tablewithjsonb (((data #> '{a, n}')::INTEGER) int4_ops);

成功创建 JSONB 中数值data['a']['n']的索引。

该索引用于以下查询：

explain analyze select * from tablewithjsonb
    where (data #> '{a, n}')::INTEGER  <= 10000;

在同一个 JSONB 中为多个数值创建组合索引也可以（在这种特殊情况下，上面的索引（i_btree_a）将是多余的，搜索data['a']['n']将使用下面的索引i_btree_a_b ):

CREATE INDEX i_btree_a_b ON tablewithjsonb
    (((data #> '{a, n}')::INTEGER) int4_ops,
    ((data #> '{b, n}')::INTEGER) int4_ops);

..这将用于以下查询：

explain analyze select * from tablewithjsonb
    where (data #> '{a, n}')::INTEGER  <= 10000 AND
    where (data #> '{b, n}')::INTEGER  <= 10000;

在 JSONB 中索引嵌套字符串/文本值（使用 b-Tree 索引）：

CREATE INDEX i_btree_s_a ON tablewithjsonb ((data #>> '{a, s}'));

b-Tree 索引将用于相等 (=) 和 LIKE 操作（执行时间：0.048 ms）：

explain analyze select * from tablewithjsonb
    where (data #>> '{a, s}') = 'blabla';
explain analyze select * from tablewithjsonb
    where (data #>> '{a, s}') LIKE '%blabla%';

（更新：当我单独尝试时，它进行顺序扫描而不是索引。为什么？）

虽然我不明白为什么以下内容适用于顺序扫描（执行时间：53.712 ms）（为什么？）：

explain analyze select * from tablewithjsonb
    where (data #>> '{a, s}')  LIKE '%blabla 1 5%';

在 JSONB 中索引嵌套字符串/文本值（使用 GIN 全文搜索索引）：

CREATE INDEX i_gin_ts_s_a ON tablewithjsonb
    USING GIN (( to_tsvector('english', (data #>> '{a, s}')) ));

GIN 全文搜索索引将用于如下查询：

explain analyze select * from tablewithjsonb where
    to_tsvector('english', (data #>> '{a, s}')) @@ to_tsquery('blabla & 1 & 5:*');

（执行时间：34.845 毫秒）

我注意到最后一个查询（通过 GIN 全文搜索）非常慢（为什么？），与上述执行时间为 53.712 毫秒的顺序扫描相距不远。

标签： postgresqlindexingsqlalchemynestedjsonb