优化group by+count distinct on unnested jsonb列

ttvkxqim 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(195)

我试图在postgres中优化一个查询，但没有成功。
这是我的table：

CREATE TABLE IF NOT EXISTS voc_cc348779bdc84f8aab483f662a798a6a (
  id SERIAL,
  date TIMESTAMP,
  text TEXT,
  themes JSONB,
  meta JSONB,
  canal VARCHAR(255),
  source VARCHAR(255),
  file VARCHAR(255)
);

我有索引 id 以及 meta 柱：

CREATE UNIQUE INDEX voc_cc348779bdc84f8aab483f662a798a6a_id ON voc_cc348779bdc84f8aab483f662a798a6a USING btree(id);
CREATE INDEX voc_cc348779bdc84f8aab483f662a798a6a_meta ON voc_cc348779bdc84f8aab483f662a798a6a USING btree(meta);

这个表有62k行。
我要优化的请求是：

SELECT meta_split.key, meta_split.value, COUNT(DISTINCT(id))
    FROM voc_cc348779bdc84f8aab483f662a798a6a
    LEFT JOIN LATERAL jsonb_each(voc_cc348779bdc84f8aab483f662a798a6a.meta)
    AS meta_split ON TRUE
    WHERE meta_split.value IS NOT NULL
    GROUP BY meta_split.key, meta_split.value;

在这个查询中，meta是这样一个dict：

{
"Age":"50 to 59 yo",
"Kids":"No kid",
"Gender":"Male"
}

我想得到键/值的完整列表+每个键/值的行数。以下是我的请求的详细解释分析结果：

GroupAggregate  (cost=1138526.13..1201099.13 rows=100 width=72) (actual time=2016.984..2753.058 rows=568 loops=1)
  Output: meta_split.key, meta_split.value, count(DISTINCT voc_cc348779bdc84f8aab483f662a798a6a.id)
  Group Key: meta_split.key, meta_split.value
  ->  Sort  (cost=1138526.13..1154169.13 rows=6257200 width=68) (actual time=2015.501..2471.027 rows=563148 loops=1)
        Output: meta_split.key, meta_split.value, voc_cc348779bdc84f8aab483f662a798a6a.id
        Sort Key: meta_split.key, meta_split.value
        Sort Method: external merge  Disk: 26672kB
        ->  Nested Loop  (cost=0.00..131538.72 rows=6257200 width=68) (actual time=0.029..435.456 rows=563148 loops=1)
              Output: meta_split.key, meta_split.value, voc_cc348779bdc84f8aab483f662a798a6a.id
              ->  Seq Scan on public.voc_cc348779bdc84f8aab483f662a798a6a  (cost=0.00..6394.72 rows=62572 width=294) (actual time=0.007..16.588 rows=62572 loops=1)
                    Output: voc_cc348779bdc84f8aab483f662a798a6a.id, voc_cc348779bdc84f8aab483f662a798a6a.date, voc_cc348779bdc84f8aab483f662a798a6a.text, voc_cc348779bdc84f8aab483f662a798a6a.themes, voc_cc348779bdc84f8aab483f662a798a6a.meta, voc_cc348779bdc84f8aab483f662a798a6a.canal, voc_cc348779bdc84f8aab483f662a798a6a.source, voc_cc348779bdc84f8aab483f662a798a6a.file
              ->  Function Scan on pg_catalog.jsonb_each meta_split  (cost=0.00..1.00 rows=100 width=64) (actual time=0.005..0.005 rows=9 loops=62572)
                    Output: meta_split.key, meta_split.value
                    Function Call: jsonb_each(voc_cc348779bdc84f8aab483f662a798a6a.meta)
                    Filter: (meta_split.value IS NOT NULL)
Planning Time: 1.502 ms
Execution Time: 2763.309 ms

我试着改变 COUNT(DISTINCT(id)) 至 COUNT(DISTINCT voc_cc348779bdc84f8aab483f662a798a6a.*) 或者使用子查询，分别导致x10和x30时间变慢。我还想和那些伯爵保持一张单独的table；但是我不能这样做，因为我需要过滤结果（比如，有时查询在 date 柱或类似物）。
我真的不知道如何进一步优化它，但它的速度相当慢，这样一个小的行计数-我希望有十倍这个数字以后，这将是太慢，如果速度与数字比例，因为它与第一个62k。

sql postgresql JSON query-optimization postgresql-performance

来源：https://stackoverflow.com/questions/63038210/optimizing-group-by-count-distinct-on-unnested-jsonb-column

1条答案

按热度按时间

eeq64g8w1#

假设 id 不仅如此 UNIQUE -由你的 UNIQUE INDEX -但也 NOT NULL . (表定义中缺少的。）

SELECT meta_split.key, meta_split.value, count(*)
FROM   voc_cc348779bdc84f8aab483f662a798a6a v
CROSS  JOIN LATERAL jsonb_each(v.meta) AS meta_split
GROUP  BY meta_split.key, meta_split.value;

较短的等效值：

SELECT meta_split.key, meta_split.value, count(*)
FROM   voc_cc348779bdc84f8aab483f662a798a6a v, jsonb_each(v.meta) AS meta_split
GROUP  BY 1, 2;

这个 LEFT [OUTER] JOIN 因为下面的测试 WHERE meta_split.value IS NOT NULL 强制 INNER JOIN 不管怎样。使用 CROSS JOIN 相反。
还有，自从 jsonb 无论如何，不允许在同一级别上重复键（表示相同 id 每次只能弹出一次 (key, value) ), DISTINCT 只是昂贵的噪音。 count(v.id) 同样也便宜。以及 count(*) 是同等的，更便宜，但假设 id 是 NOT NULL 如顶部所述。 count(*) 有一个单独的实现，比 count(<value>) . 这和 count(v.*) . 它计算所有行，不管是什么。而另一种形式不算 NULL 价值观。
也就是说，只要 id 不能是 NULL -如顶部所述。 id 真的应该是 PRIMARY KEY ，它在内部使用唯一的b树索引实现，所有列-仅 id 给-你 NOT NULL 含蓄地。或者至少 NOT NULL . 一 UNIQUE INDEX 不完全符合替换条件，它仍然允许 NULL 不相等且允许多次使用的值。请参见：
为什么要在可为空的列上创建主键为的表？
创建具有空列的唯一约束
除此之外，索引在这里没有任何用处，因为无论如何都必须读取所有行。所以这永远不会很便宜。但是62k行并不是一个严重的行数，除非您的数据库中有大量的键 jsonb 列。
其他加速选项包括：
规范化您的设计。取消测试json文档并不是免费的。
保持物化视图。可行性和成本很大程度上取决于你的写作模式。
... 有时查询在 date 柱子之类的。
这就是索引可能再次发挥作用的地方。。。

赞(0）回复(0）举报 2021-07-24

我来回答

优化group by+count distinct on unnested jsonb列

1条答案

相关问题

热门标签

最新问答