优化 unnested jsonb 列上的 GROUP BY + COUNT DISTINCT

Question

我试图优化Postgres中的查询，但尝试失败。

这是我的表：

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);

这个表格有62000行。

我试图优化的请求如下：

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是一个类似于下面这个字典的对象:

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

我想获取每个键/值对应的完整列表以及每行的计数。以下是我的查询的EXPLAIN ANALYZE VERBOSE的结果：

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.*) 或使用子查询，结果分别变慢了10倍和30倍。我还考虑过使用单独的计数表来维护这些计数；但是由于需要过滤结果（比如，有时查询会在date 列或类似列上进行筛选），所以无法这样做。

我不知道该如何进一步优化，但是即使行数很少，它也非常缓慢 - 我预计稍后会有十倍于此的行数，如果速度与数量成比例增加，那会太慢了，就像前面的62k数据一样。

- Yann PIQUET

Answer 1

假设“id”不仅是由您的“UNIQUE INDEX”强制执行的“UNIQUE”，而且还是“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;

由于以下测试 WHERE meta_split.value IS NOT NULL 强制使用了 INNER JOIN，因此 LEFT [OUTER] JOIN 是无意义的。使用代替 CROSS JOIN。

另外，由于 jsonb 不允许同一级别上有重复的键（这意味着相同的 id 每个 (key, value) 只能出现一次），因此 DISTINCT 只是昂贵的噪音。 count(v.id) 执行效果更好且更便宜。假设顶部已经声明了 id 为 NOT NULL，则 count(*) 等效且更加便宜。 count(*) 有一个单独的实现，并且比 count(<value>) 稍微快一点。它与 count(v.*) 有微妙的不同。它计算所有行，而另一种形式不计算 NULL 值。

也就是说，只要 id 不能为 NULL - 正如顶部所述。 id 应该真正成为 PRIMARY KEY，这在内部通过唯一的 B-tree 索引实现，而所有列 - 在这里只是 id - 都隐含为 NOT NULL。或者至少是 NOT NULL。一个 UNIQUE INDEX 不完全作为替代，它仍然允许 NULL 值，这些值不被认为相等并且允许多次出现。请参见：

• 为什么我可以在可为空的列上创建 PRIMARY KEY 的表？

• 使用空列创建唯一约束

此外，在这里索引没有用处，因为所有行都必须读取。所以这永远不会非常便宜。但是对于任何意义而言，“62k 行”不是很多 - 除非您的 jsonb 列中有大量键。

加速它的剩余选项：

1. 规范化设计。展开 JSON 文档并非没有成本。

2. 维护一个物化视图。可行性和成本强烈取决于您的写入模式。

... 有时查询会对 date 列或类似列进行过滤。

这就是索引可能再次发挥作用的地方...

- Erwin Brandstetter