在大型表上用+ jsonb_each优化组

Question

我正在尝试使用jsonb字段优化表上的请求。

这是我的桌子结构：

CREATE TABLE data_insight (
  uuid UUID PRIMARY KEY NOT NULL,
  name VARCHAR(255),
  meta JSONB,
  data JSONB,
  insight_type_id UUID NOT NULL
);

CREATE INDEX ON data_insight ('insight_type_id');
CREATE INDEX ON data_insight ('meta');

在meta列中，我为这个表中的数据提供了过滤信息；当我编写代码时，这些过滤信息是未知的，并且可以根据不同的洞察力(取决于insight_type_id)而有所不同。

我需要从这个meta列获得(键，值)列表，以便让我的用户在它们之间进行选择(应用了一些过滤器)。目前，我的请求如下：

SELECT "key", "value" FROM "data_insight"
LEFT JOIN jsonb_each("data_insight"."meta") ON TRUE
WHERE  "data_insight"."insight_type_id" = '64ff223c-be7d-435c-b83b-3649fa017f17'
GROUP BY "value", "key";

这个请求需要30秒才能执行(表中大约有400万行，其中只有不到100万行是我的测试请求的insight_type_id的一部分)。以下是对其进行EXPLAIN ANALYZE查询的结果：

Group  (cost=934323.86..934373.75 rows=100 width=64) (actual time=5403.970..5472.750 rows=435 loops=1)
  Group Key: jsonb_each.value, jsonb_each.key
  ->  Gather Merge  (cost=934323.86..934371.75 rows=400 width=64) (actual time=5403.968..5470.881 rows=1938 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Sort  (cost=933323.80..933324.05 rows=100 width=64) (actual time=5190.508..5190.543 rows=388 loops=5)
              Sort Key: jsonb_each.value, jsonb_each.key
              Sort Method: quicksort  Memory: 60kB
              Worker 0:  Sort Method: quicksort  Memory: 57kB
              Worker 1:  Sort Method: quicksort  Memory: 61kB
              Worker 2:  Sort Method: quicksort  Memory: 58kB
              Worker 3:  Sort Method: quicksort  Memory: 58kB
              ->  Partial HashAggregate  (cost=933319.48..933320.48 rows=100 width=64) (actual time=5186.490..5186.782 rows=388 loops=5)
                    Group Key: jsonb_each.value, jsonb_each.key
                    ->  Nested Loop Left Join  (cost=0.81..823353.86 rows=21993125 width=64) (actual time=69.452..2962.023 rows=1399327 loops=5)
                          ->  Parallel Index Only Scan using data_insight_meta_insight_type_id_c54dbada_uniq on data_insight  (cost=0.81..383491.85 rows=219931 width=276) (actual time=69.278..751.725 rows=176904 loops=5)
                                Index Cond: (insight_type_id = '64ff223c-be7d-435c-b83b-3649fa017f17'::uuid)
                                Heap Fetches: 35500
                          ->  Function Scan on jsonb_each  (cost=0.00..1.00 rows=100 width=64) (actual time=0.008..0.009 rows=8 loops=884519)

我的问题：

在这个请求中，时间变化很大:有时需要5-4秒，有时需要30+秒。有什么事我应该检查一下才能明白原因吗？

[2]我怎样才能更快地提出这一要求？我无法删除jsonb本身；但是，我考虑创建一个MATERIALIZED VIEW来存储jsonb_each的结果(实际上，其中的多个，每个insight_type_id一个)，这会有帮助吗？或者简单的VIEW会更好吗？

谢谢

Answer 1

您正在查询大量数据以获得一个小的结果。这永远不会很快爆发。如果您确实需要这样做，那么物化视图可能是最好的。但是，您希望在组-by对数据进行了大规模重复之后实现。

create materialized view insight_type_choices as
SELECT "data_insight"."insight_type_id", "key", "value" FROM "data_insight"
LEFT JOIN jsonb_each("data_insight"."meta") ON TRUE
GROUP BY "data_insight"."insight_type_id", "value", "key";

你不太可能真的想让那个连接成为一个左连接，但我是这样离开的，因为你就是这么写的。我也会在那里抛出一个计数列，因为它基本上是免费的，如果它产生了数以千计的选项，您可能想要一种对它们进行排序的方法。