有些糊涂了。首先,我不是dba,也没有任何与Postgres相关的经验,除了我现在正在做的事情。
当你想返回一般的jsonb文档并且行数超过几百行时,Postgres似乎会出现问题。当你尝试返回成千上万的数据时,查询性能变得很差。如果你进一步尝试从各种表连接中返回多个jsonb文档,就更别说了。
以下是我的使用场景和代码:
我有3个表——所有表都有复杂的jsonb模型,其中2个表的大小相当可观(未经压缩的大小为8到12kb)。在这个特定的操作中,我需要展开jsonb元素数组然后进行处理,这给我带来了大约12k条记录。
然后每个记录包含一个ID,我使用它来连接另一个重要的表,我需要从这个表中检索出jsonb文档。从那里,我需要将该表连接到另一个(更小)的表,并根据另一个键从那里提取文档。
因此输出是几列加上三个jsonb文档,其大小从小于1kb到未压缩的大小约为12kb。
查询数据检索实际上是没有意义的——我还没有看到查询返回数据。一旦我删除json文档列,自然地,查询速度会提高到几秒钟或更少。在我的情况下,一个jsonb文档将检索时间提高到了40秒,添加第二个将花费2分钟,添加第三个则需要更长的时间。
我做错了什么?有没有一种可以高效检索jsonb文档的方法?
当你想返回一般的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)
explain(analyze,buffers,format text)
**生成的执行计划,而不仅仅是“简单”的解释。 - user330315