sql - 在未嵌套的 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);

此表中有 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 是一个像这样的字典:

{
"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 时间变慢。我还考虑过用这些计数维护一个单独的表；但是我不能这样做，因为我需要过滤结果(例如，有时查询在 date 列上有一个过滤器等)。
我真的不知道如何进一步优化它，但是如此小的行数会很慢 - 我希望以后有十倍于这个数字，如果速度与数字成比例，那就太慢了，就像它所做的那样与第一个 62k。

Answer 1

假设 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(*) has a separate implementation并且比 count(<value>) 稍快.它与 count(v.*) 略有不同.无论如何，它都会计算所有行。而另一种形式不计 NULL值。
即只要 id不能是 NULL - 如顶部所述。 id真的应该是 PRIMARY KEY ，无论如何，它在内部使用唯一的 B 树索引实现，所有列 - 只是 id这里 - 是 NOT NULL含蓄地。或者至少 NOT NULL .一个 UNIQUE INDEX不完全符合替换条件，它仍然允许 NULL被认为不相等并且允许多次的值。看:

Why can I create a table with PRIMARY KEY on a nullable column?

Create unique constraint with null columns

除此之外，索引在这里没有用，因为无论如何都必须读取所有行。所以这永远不会很便宜。但是 62k 行无论如何都不是一个严重的行数 - 除非您在 jsonb 中有大量的键。柱子。
加快速度的其余选项:

规范化您的设计。取消嵌套 JSON 文档不是免费的。

维护物化 View 。可行性和成本在很大程度上取决于您的写入模式。

... sometimes the query has a filter on the date column or the like.

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