Postgres JSONB检索非常缓慢

Question

有些糊涂了。首先，我不是dba，也没有任何与Postgres相关的经验，除了我现在正在做的事情。

当你想返回一般的jsonb文档并且行数超过几百行时，Postgres似乎会出现问题。当你尝试返回成千上万的数据时，查询性能变得很差。如果你进一步尝试从各种表连接中返回多个jsonb文档，就更别说了。

以下是我的使用场景和代码：

我有3个表——所有表都有复杂的jsonb模型，其中2个表的大小相当可观（未经压缩的大小为8到12kb）。在这个特定的操作中，我需要展开jsonb元素数组然后进行处理，这给我带来了大约12k条记录。

然后每个记录包含一个ID，我使用它来连接另一个重要的表，我需要从这个表中检索出jsonb文档。从那里，我需要将该表连接到另一个（更小）的表，并根据另一个键从那里提取文档。

因此输出是几列加上三个jsonb文档，其大小从小于1kb到未压缩的大小约为12kb。

查询数据检索实际上是没有意义的——我还没有看到查询返回数据。一旦我删除json文档列，自然地，查询速度会提高到几秒钟或更少。在我的情况下，一个jsonb文档将检索时间提高到了40秒，添加第二个将花费2分钟，添加第三个则需要更长的时间。

我做错了什么？有没有一种可以高效检索jsonb文档的方法？

SELECT x.id,
    a.doc1,
    b.doc2,
    c.doc3
   FROM ( SELECT id,
            (elements.elem ->> 'a'::text)::integer AS a,
            (elements.elem ->> 'b'::text)::integer AS b,
            (elements.elem ->> 'c'::text)::integer AS c,
            (elements.elem ->> 'd'::text)::integer AS d,
            (elements.elem ->> 'e'::text)::integer AS e
           FROM tab
             CROSS JOIN LATERAL jsonb_array_elements(tab.doc -> 'arrayList'::text) WITH ORDINALITY elements(elem, ordinality)) x
     LEFT JOIN table2 a ON x.id = a.id
     LEFT JOIN table3 b ON a.other_id = b.id
     LEFT JOIN table4 c ON b.other_id = c.id;

标签本身是相当标准的：

CREATE TABLE a ( 
  id (primary key), 
  other_id (foreign key), 
  doc jsonb 
)

这些表并没有什么特别之处，它们只是id和jsonb文档。

需要说明的是，我们使用Postgres有几个原因：我们需要PG的关系方面，但同时我们也需要将文档存储和检索的能力，以便之后的工作流程。

如果我没有提供足够的数据，请谅解，我可以根据您的任何评论尝试添加更多数据。

编辑：添加了解释：

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=465.79..96225.93 rows=11300 width=1843)
   Hash Cond: (pr.table_3_id = br.id)
   ->  Hash Left Join  (cost=451.25..95756.86 rows=11300 width=1149)
         Hash Cond: (((p.doc ->> 'secondary_id'::text))::integer = pr.id)
         ->  Nested Loop Left Join  (cost=0.44..95272.14 rows=11300 width=1029)
               ->  Nested Loop  (cost=0.01..239.13 rows=11300 width=40)
                     ->  Seq Scan on table_3  (cost=0.00..13.13 rows=113 width=710)
                     ->  Function Scan on jsonb_array_elements elements  (cost=0.01..1.00 rows=100 width=32)
               ->  Index Scan using table_1_pkey on table_1 p  (cost=0.43..8.41 rows=1 width=993)
                     Index Cond: (((elements.elem ->> 'field_id'::text))::integer = id)
         ->  Hash  (cost=325.36..325.36 rows=10036 width=124)
               ->  Seq Scan on table_2 pr  (cost=0.00..325.36 rows=10036 width=124)
   ->  Hash  (cost=13.13..13.13 rows=113 width=710)
         ->  Seq Scan on table_3 br  (cost=0.00..13.13 rows=113 width=710)
(14 rows)

编辑2：非常抱歉最近很忙，我会尽量详细解释。首先是完整的执行计划（我不知道还有额外的参数），我将保留实际表格（我不确定是否被允许）：

Hash Left Join  (cost=465.79..96225.93 rows=11300 width=1726) (actual time=4.669..278.781 rows=12522 loops=1)
   Hash Cond: (pr.brand_id = br.id)
   Buffers: shared hit=64813
   ->  Hash Left Join  (cost=451.25..95756.86 rows=11300 width=1032) (actual time=4.537..265.749 rows=12522 loops=1)
         Hash Cond: (((p.doc ->> 'productId'::text))::integer = pr.id)
         Buffers: shared hit=64801
         ->  Nested Loop Left Join  (cost=0.44..95272.14 rows=11300 width=912) (actual time=0.240..39.480 rows=12522 loops=1)
               Buffers: shared hit=49964
               ->  Nested Loop  (cost=0.01..239.13 rows=11300 width=40) (actual time=0.230..8.177 rows=12522 loops=1)
                     Buffers: shared hit=163
                     ->  Seq Scan on brand  (cost=0.00..13.13 rows=113 width=710) (actual time=0.003..0.038 rows=113 loops=1)
                           Buffers: shared hit=12
                     ->  Function Scan on jsonb_array_elements elements  (cost=0.01..1.00 rows=100 width=32) (actual time=0.045..0.057 rows=111 loops=113)
                           Buffers: shared hit=151
               ->  Index Scan using product_variant_pkey on product_variant p  (cost=0.43..8.41 rows=1 width=876) (actual time=0.002..0.002 rows=1 loops=12522)
                     Index Cond: (((elements.elem ->> 'productVariantId'::text))::integer = id)
                     Buffers: shared hit=49801
         ->  Hash  (cost=325.36..325.36 rows=10036 width=124) (actual time=4.174..4.174 rows=10036 loops=1)
               Buckets: 16384  Batches: 1  Memory Usage: 1684kB
               Buffers: shared hit=225
               ->  Seq Scan on product pr  (cost=0.00..325.36 rows=10036 width=124) (actual time=0.003..1.836 rows=10036 loops=1)
                     Buffers: shared hit=225
   ->  Hash  (cost=13.13..13.13 rows=113 width=710) (actual time=0.114..0.114 rows=113 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 90kB
         Buffers: shared hit=12
         ->  Seq Scan on brand br  (cost=0.00..13.13 rows=113 width=710) (actual time=0.003..0.043 rows=113 loops=1)
               Buffers: shared hit=12
 Planning Time: 0.731 ms
 Execution Time: 279.952 ms
(29 rows)

- Hamz4h_

---

请展示此查询的“EXPLAIN”计划。 - jjanes

请编辑您的问题，并添加使用**explain（analyze，buffers，format text）**生成的执行计划，而不仅仅是“简单”的解释。 - user330315

你的第二个执行计划清楚地显示，检索12522行仅花费了280毫秒（约0.25秒）。你看到的任何与实际查询时间不同的差异都是由于网络开销和/或你的SQL客户端“显示”这些12522行所致。 - user330315

@a_horse_with_no_name，说得好，我正在使用psql，但我也尝试过pgadmin，正如您所说-查询很快，就像帖子所述-这似乎是一个jsonb解压缩问题-即我正在尝试检索所有12522行的3个jsonb文档-如果我从查询中删除它们，输出就会非常快-所以回到我的问题，这是否是jsonb检索的预期性能？ - Hamz4h_

Answer 1

您的查询有几个难以理解的原因：

1. 您的表名为tab，table2，table3，`table4。
2. 您的子查询对表中的每一行都解析JSON，然后投影出一些值，但外部查询从未使用这些值。唯一似乎相关的是id。
3. 外连接必须按顺序执行，而内连接可以自由重新排列以提高性能。不知道此查询的目的，我无法确定是否适合使用外连接。
4. 执行计划中的表名和列名与查询不匹配，因此我不确定此计划是否准确。
5. 您没有提供模式。

话虽如此，我会尽力而为。

性能方面需要注意的事项

没有where子句

由于没有where子句，您的查询将对tab的每一行运行jsonb_array_elements，这就是正在发生的事情。除了从JSON中提取数据并将其存储到单独的列中之外，我无法想象还能做些什么来优化它。

索引不足以支撑连接

查询计划表明，连接可能会产生实际成本。除了table1之外，每个连接都由表的顺序扫描驱动，这意味着读取每个表的每一行。我怀疑在每个表上添加索引将有所帮助。看起来您正在连接id列，因此简单的主键约束将改善数据完整性和查询性能。

alter table tab add constraint primary key (id);
alter table table2 add constraint primary key (id);
alter table table3 add constraint primary key (id);
alter table table4 add constraint primary key (id);

类型转换

执行计划的这一部分显示了您第一个连接中的双精度类型转换：

Index Cond: (((elements.elem ->> 'field_id'::text))::integer = id)

这个谓词意味着将tab中的id值转换为文本，然后将文本转换为整数，以便与table2.id匹配。 这些转换可能会在计算时间上很昂贵，在某些情况下可能会阻止索引使用。 很难建议该怎么做，因为我不知道实际类型是什么。

- Brandon

---

嘿，Brandon - 我会尝试回答上面的一些问题：

1. 我已经添加了原始表的解释计划 - 我不确定是否可以发布它们，因此使用类似“tab1”等的内容。
2. 这些值是被使用的，只是不在上面的示例中 - 我想展示的关键部分是我想要返回的文档。
3. 在这个例子中，连接是有序的，不幸的是由于模式的原因，没有办法避免 - 产品变体是产品上的多对一，而产品又是品牌表上的多对一。

- Hamz4h_

我正在重新链接品牌表到外部左连接，因为我正在连接一个内部的jsonb文档值（我当然可以建立索引）。请查看更新后的解释计划-这可能会有所帮助。关于“where”子句-在这种情况下，是的，我想要全部内容，我知道这会有影响，但在这种情况下，我们正在谈论109行扩展到大约12k，现在还可以管理-将来我们可能会更改此设置。 - Hamz4h_

一个重要的注意事项，我现在要尝试一下 - 我刚刚意识到，在创建时，id列可能没有索引，正如你所指出的...让我来做一下，看看情况如何 - 我想这是我的疏忽。 - Hamz4h_

重新输入转换：我不明白这如何将tab.id转换为文本再转换为整数。 它难道不是从jsonb_array_elements中提取字段，将json转换为文本，然后转换为整数吗？ 然后这个整数似乎被用于在table2上进行索引扫描，所以我不明白它如何防止索引使用。 - Bergi