我正在postgresql中测试jsonb数据类型的性能。每个文档将有大约1500个没有层次结构的密钥。该文档已展开。以下是表格和文档的外观。
create table ztable0
(
id serial primary key,
data jsonb
)
这是一个示例文档：
{ "0": 301, "90": 23, "61": 4001, "11": 929} ...
正如您所看到的，文档不包含层次结构，所有值都是整数。然而，有些将在未来成为文本。
- 行数：86000
- 列：2
- 文档中的密钥：1500+
当搜索键的特定值或按性能执行组时，速度非常慢。此查询：
select (data ->> '1')::integer, count(*) from ztable0
group by (data ->> '1')::integer
limit 100
花了大约2秒完成。有什么方法可以提高jsonb文档的性能吗。
这是9.4beta2中的一个已知问题，请看一下这篇博客文章，它包含一些详细信息和指向邮件线程的指针。
关于这个问题
PostgreSQL使用TOAST来存储数据值，这意味着大值（通常为2kB及以上）存储在单独的特殊类型的表中。PostgreSQL也尝试压缩数据，使用它的pglz方法（已经存在了很长时间）。所谓"尝试"，意味着在决定压缩数据之前，先探测1k字节。如果结果不令人满意，即压缩对所探测的数据没有好处，则决定不压缩。
因此，最初的JSONB格式在其值的开头存储了一个偏移量表。对于JSON中具有大量根键的值，这导致前1kB（甚至更多）被偏移量占用。这是一系列不同的数据，即不可能找到两个相等的相邻4字节序列。因此没有压缩。
请注意，如果通过偏移量表，则该值的其余部分完全可以压缩。因此，其中一种选择是明确告诉pglz代码压缩是否适用，以及在哪里进行探测（尤其是对于新引入的数据类型），但现有的基础设施不支持这一点。
修复
因此，决定更改数据在JSONB值中的存储方式，使其更适合pglz进行压缩。以下是Tom Lane的提交消息，其中包含实现新的磁盘上JSONB格式的更改。尽管格式发生了变化，但对随机元素的查找仍然是O（1）。
不过，它花了大约一个月的时间才修复。正如我所看到的，9.4beta3已经被标记了，所以在官方发布后，你很快就能重新测试它。
重要提示：您必须执行pg_dump/pg_restore练习或使用pg_upgrade工具切换到9.4beta3，以解决您确定的数据存储方式需要更改的问题，因此beta3与beta2不兼容。