索引 jsonb 以进行字段的数字比较

我定义了一个简单的表

create table resources (id serial primary key, fields jsonb);

它包含带有键（从一个大集合中提取）和 1 到 100 之间的值的数据，例如：

   id   |    fields                                                                                                 
--------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1 | {"tex": 23, "blair": 46, "cubic": 50, "raider": 57, "retard": 53, "hoariest": 78, "suturing": 25, "apostolic": 22, "unloosing": 37, "flagellated": 85}
      2 | {"egoist": 75, "poshest": 0, "annually": 19, "baptists": 29, "bicepses": 10, "eugenics": 9, "idolizes": 8, "spengler": 60, "scuppering": 13, "cliffhangers": 37}
      3 | {"entails": 27, "hideout": 22, "horsing": 98, "abortions": 88, "microsoft": 37, "spectrums": 26, "dilettante": 52, "ringmaster": 84, "floweriness": 72, "vivekananda": 24}
      4 | {"wraps": 6, "polled": 68, "coccyges": 63, "internes": 93, "unburden": 61, "aggregate": 76, "cavernous": 98, "stylizing": 65, "vamoosing": 35, "unoriginal": 40}
      5 | {"villon": 95, "monthly": 68, "puccini": 30, "samsung": 81, "branched": 33, "congeals": 6, "shriller": 47, "terracing": 27, "patriarchal": 86, "compassionately": 94}

我想搜索其值（与特定键相关）大于某个基准值的条目。我可以通过以下方式完成此任务：

with exploded as (
    select id, (jsonb_each_text(fields)).*
    from resources)
select distinct id
    from exploded
    where key='polled' and value::integer>50;

...但是当然这不使用索引，而是诉诸于表扫描。我想知道是否有：

1. 查询“polled”>50 的资源的更有效方法
2. 一种构建支持此类查询的索引的方法

你还没有具体说明是哪一种INDEX你期望被使用，但你还没有提供它的定义。

典型的INDEX for a jsonb字段将是GIN一，但在您的具体情况下，您实际上需要compare中包含的一些值polled key.

Maybe一个具体的INDEX（虽然不是一个GIN一个！）与一个表达可能有一定用处，但我对此表示怀疑，并且它可能会变得相当麻烦，因为您至少需要双精度类型转换才能获得整数值和自定义值IMMUTABLE函数来实际执行类型转换CREATE INDEX陈述。

在采取复杂的路线之前，该路线只能解决一些特定情况（如果您需要与不同的路线进行另一次比较怎么办）fields键？），您可以尝试利用 PostgreSQL 9.4 新功能来优化当前查询LATERAL能力和jsonb处理功能。 结果是查询的运行速度应比当前查询快 8 倍：

SELECT r.id 
    FROM resources AS r,
    LATERAL jsonb_to_record(r.fields) AS l(polled integer) 
    WHERE l.polled > 50;

EDIT :

我做了一个快速测试，将我的评论中的想法付诸实践，以使用GIN INDEX在实际比较值之前限制行数，事实证明您确实可以使用GIN INDEX即使在那种情况下。

The INDEX必须使用默认运算符类创建jsonb_ops (not更轻、性能更强jsonb_path_ops) :

CREATE INDEX ON resources USING GIN (fields);

现在您可以利用索引，只需包含一个存在?在查询中测试：

SELECT r.id
    FROM resources AS r,
    LATERAL jsonb_to_record(r.fields) AS l(polled integer) 
    WHERE r.fields ? 'polled' AND l.polled > 50;

现在查询执行大约快 3 倍 （比第一个 CTE 版本快约 20 倍）。我已经测试了多达 1M 行，性能增益始终相同。


请记住，正如预期的那样，行数起着重要作用：行数少于 1K 时，索引毫无用处，查询规划器可能不会使用它。

也不要忘记jsonb_ops与实际数据相比，索引可能会变得巨大。对于像您这样的数据样本，范围从 1K 到 1M 行，索引本身大约是170%比表中实际数据大，自己查一下：

SELECT pg_size_pretty(pg_total_relation_size('resources')) AS whole_table, 
       pg_size_pretty(pg_relation_size('resources')) AS data_only, 
       pg_size_pretty(pg_relation_size('resources_fields_idx')) AS gin_index_only;

只是为了给您一个想法，像您的数据示例一样，该表大约有 300K 行，该表大约有 250MB，其中包含 90MB 数据和 160MB 索引！ 就我个人而言，我会坚持（我确实这样做）用一个简单的LATERAL JOIN没有索引。