在未测试的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是一个类似于以下的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倍。我还考虑过用那些伯爵单独摆一张桌子；但是我不能这样做，因为我需要过滤结果(比如，有时查询在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。

CCD_ 22有一个单独的实现，并且比CCD_。它与count(v.*)有着微妙的不同。它统计所有的行，不管怎样。而另一种形式不计算CCD_ 25值。

也就是说，只要id不能是NULL，如顶部所述。id实际上应该是PRIMARY KEY，它在内部使用唯一的B树索引来实现，并且所有列——这里只有id——都是隐式的NOT NULL。或者至少NOT NULL。UNIQUE INDEX不完全符合替换条件，它仍然允许NULL值，这些值被认为是不相等的，并且被允许多次。参见：

• 为什么我可以在可为null的列上创建一个具有PRIMARY KEY的表？

• 使用空列创建唯一约束

除此之外，索引在这里没有任何用处，因为无论如何都必须读取所有行。所以这永远不会很便宜。但是62k行无论如何都不是一个严重的行计数——除非jsonb列中有大量的键。

剩下的加速选项：

1. 规范化您的设计。取消JSON文档的目的并不是免费的。

2. 维护物化视图。可行性和成本很大程度上取决于你的写作模式。

。。。有时查询在CCD_ 36列等上具有过滤器。

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