时间戳

首页 > 解决方案 > 如何在 jsonb postgres 中的数组元素上创建 b-tree 索引

postgresql - 如何在 jsonb postgres 中的数组元素上创建 b-tree 索引

问题描述

我正在尝试将一些报告作为 jsonb 字段存储在 postgres 中。

下载：

CREATE TABLE reports (
    id INT, 
    report JSONB, 
    PRIMARY KEY (id)
)

从概念上讲，报告的结构如下：

{
    "metainfo": "infodata",
    "expense":{
         "rows": [
             {
                 "item": "Repair",
                 "cost": 15300.00,
                 "ts": "2021-04-24",
             },
             {
                 "item": "tractor",
                 "cost": 120000.00,
                 "ts": "2021-04-03",
             },
             ...
         }
    ]
}

报告之间的字段集不同，因此并非所有报告都具有例如“项目”字段。

假设在我们的示例中，我们有 2021 年 4 月的费用报告。所以现在我想从所有报告中选择所有项目，其中成本 > 100000.00

当生成 100 万份报告时，我发现提取这些数据大约需要 30 秒。是否可以创建 b-tree 索引，因此它涵盖了我的案例并加速了我的查询：

select id, arr.item->'cost'
from reports, jsonb_array_elements(report->'expense'->'rows') arr(row) 
where (arr.row->'cost')::numeric > 100000::numeric

使用explain analyze（我的表名是“jsonb1”，而不是“reports”）

Nested Loop  (cost=0.01..4795009.70 rows=66000132 width=36) (actual time=132.281..170719.239 rows=1959507 loops=1)
->  Seq Scan on jsonb1  (cost=0.00..470001.04 rows=2000004 width=1790) (actual time=0.098..44013.831 rows=2000004 loops=1)
->  Function Scan on jsonb_array_elements arr  (cost=0.01..1.76 rows=33 width=32) (actual time=0.021..0.030 rows=1 loops=2000004)
        Filter: (((item -> 'cost'::text))::numeric > '100000'::numeric)
        Rows Removed by Filter: 0
Planning Time: 0.077 ms
JIT:
  Functions: 6
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 1.027 ms, Inlining 49.986 ms, Optimization 57.205 ms, Emission 23.538 ms, Total 131.756 ms
Execution Time: 186026.874 ms

我尝试过其他类型的查询，例如 jsonpath，但所有这些都会导致 seq 扫描。索引创建：

CREATE INDEX costbtree ON reports USING BTREE (((data->'expense'->'rows'->'cost')::numeric));

Postgres 版本：PostgreSQL 12.2

标签： postgresql

解决方案

您可以索引报告的最大成本以加快速度。您需要先定义一个辅助函数：

CREATE OR REPLACE FUNCTION public.max(jsonb, text)
 RETURNS numeric
 LANGUAGE sql
 IMMUTABLE PARALLEL SAFE
AS $function$
    SELECT max((x->>$2)::numeric) from jsonb_array_elements($1) f(x) 
$function$;

create index ON  reports (max(report->'expense'->'rows','cost'));

select id, arr.row->'cost'
from reports, jsonb_array_elements(report->'expense'->'rows') arr(row) 
where (arr.row->'cost')::numeric > 100000::numeric
  and max(report->'expense'->'rows','cost') > 10000;

如果您想以降低函数通用性为代价使用法更简洁，则可以将常量“expense”、“row”和“cost”直接定义到函数中。

推荐阅读