时间戳

首页 > 解决方案 > Postgres JSONB - 子对象的所有动态（整数）属性的索引

postgresql - Postgres JSONB - 子对象的所有动态（整数）属性的索引

问题描述

我的数据库中有一个 JSONB 表，如下所示：

             data
-------------------------------
{
  "nestedObject": {
    "dynamic-key-1": 123,
    "dynamic-key-2": 456,
    "dynamic-key-3": 789,
    "and so on": 123
  },
  "rest of object": "goes here"
}
-- a few million more objects come here

我特别想知道是否可以对as integers的所有（现有）键进行索引。目前（据我了解）。我知道如果我提前知道钥匙，我可以做类似的事情data->'nestedObject' 

CREATE INDEX IF NOT EXISTS idx_gin_my_jsonb_integer_index ON table 
    USING BTREE (((data->'nestedObject'->>'integerKey')::integer));

但不幸的是，这是不可能的，因为我事先不知道密钥（嵌套对象的属性是在运行时根据时间戳等生成的）。许多nestedObjects 有可能拥有相同的键（例如，许多对象可能有data->'nestedObject'->'dynamic-key-1'），但a 不可能多次nestedObject拥有相同的键。

我想这样做的原因是（希望很明显）加快正在运行的查询。具体来说，有问题的查询是：

SELECT tableOne.data AS dataOne, tableTwo.data AS dataTwo FROM tableOne
    JOIN tableTwo ON tableTwo.data->>'someField' = tableOne.id
    WHERE tableOne.data->'nestedObject'->'dynamic-key-goes-here' IS NOT NULL
        AND (tableOne.data->'nestedObject'->>'dynamic-key-goes-here')::integer > 0
    ORDER BY (tableOne.data->'nestedObject'->>'dynamic-key-goes-here')::integer DESC 
LIMIT 100;

以第二个查询为例，我可以做EXPLAIN ANALYZE它。我看到它最终对from进行顺序扫描（而不是并行 seq 扫描），这需要大约 75% 的预期查询时间。((((data -> 'nestedObject'::text) ->> 'dynamic-key-goes-here'::text))::integer > 0)tableOne

我知道如果它“正常”存储，这将是微不足道的，即。作为典型的关系数据（并且此数据是关系数据），但不幸的是 1. 我从其他人那里继承了此代码，以及 2. 我目前无法进行数据库迁移，因此无法执行此操作。

因此，鉴于此，是否可以有效地在该数据上创建一个整数索引？

标签： postgresqljsonb

解决方案

如果您要查找的键仅存在于（相对）少量值中，则可以使用?("exists) 运算符将其过滤掉。该​​运算符可以使用 JSONB 值的索引。

例如：

create index on the_table using gin (data  -> 'nestedObject');

并使用如下条件：

where data->'nestedObject' ? 'dynamic-key-1' -- this could use the index if feasible
  and (data->'nestedObject'->> 'dynamic-key-1')::integer > 100

但是，如果该键存在于大多数“嵌套对象”中，这将无济于事。

如果您正在寻找一个特定的值（例如 dynamic-key = 123），这可以使用 GIN 索引和@>运算符来支持，例如，where data @> '{"nestedObject" : {"dynamic-key-1": 123}}'但是当您使用>它比较值时，很难索引。

推荐阅读