就像你自己提到的，正确的解决办法是将这些属性提取到单独的列中，这在一定程度上规范了你的设计。

索引能帮上忙吗？

很遗憾，没有（截至Postgres 14）。
它在理论上可以工作。因为你的值很大，一个只有一些小属性的表达式索引可以被Postgres在一个只扫描索引的扫描中拾取，即使在检索所有行时（否则它会忽略索引）。
The manual:
然而，PostgreSQL的规划器目前对这种情况不是很聪明，它认为只有当查询所需的所有列都可以从索引中获得时，查询才可能通过仅索引扫描执行。
所以你必须在索引中包含value本身，即使只是作为INCLUDE列-完全破坏了整个想法。
在短期内，你可能仍然可以做一些**事情。两个关键的报价：
我期待着看看我是否可以使这在短期内更快
json blob有点大
数据类型
从查询中删除json的强制转换。每次强制转换都会增加无意义的成本。

压缩

一个主要的成本因素将是压缩。Postgres必须"de-toast"整个大列，只是为了提取一些小属性。从Postgres 14开始，您可以切换压缩算法（如果您的版本中启用了支持！）。默认值由配置设置default_toast_compression定义，默认为pglz。目前唯一可用的替代方案是**lz4**。您可以随时按列设置。
LZ 4（lz4）速度快得多，但压缩速度通常稍低。大约是两倍快，但存储量增加了10%左右（取决于！）。如果性能不是问题，最好坚持使用默认LZ算法（pglz）的更强压缩。将来可能会有更多压缩算法可供选择。
执行：

ALTER TABLE data
  ALTER COLUMN value SET COMPRESSION lz4;

字符串
为列设置一个新的COMPRESSION不会自动重新压缩。Postgres会记住压缩方法，只有在强制解压缩的情况下才会重新压缩。你可能想强制重新压缩现有的值。你可以检查：

SELECT pg_column_compression(value) FROM data LIMIT 10;

型
相关博客文章：

• https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14的

GENERATED列

当你被这种糟糕的设计所困时，你可以添加一些（小的！）生成列来覆盖你的查询：

ALTER TABLE data
  ADD COLUMN name text GENERATED ALWAYS AS (value::json ->> 'name') STORED
, ADD COLUMN mnemonic text GENERATED ALWAYS AS (value::json ->> 'mnemonic') STORED
...

型
然后只针对那些生成的列，根本不涉及大的value。

SELECT name, mnemonic, ... FROM data;

型
这将绕过主要的性能问题。
参见：

• PostgreSQL中的计算/计算/虚拟/派生/生成列

但是，你提到：
json blob中更底层的数据经常发生变化。
对value的每一次更改都会强制重新检查生成的列，因此会增加写入成本。

我也有类似的性能问题，不幸的是我没有运行PostgreSQL 12+，所以不能使用generated列。
你有两个解决方案：
1.)表中的每一列都应该是硬编码的，解析器应该插入到这些列中。（理想的解决方案）
2.)创建查询的materialized view，并将索引添加到此视图的主键列。然后，您可以使用refresh materialized view concurrently刷新此视图，而不会对其他用户/应用程序查询此视图时的性能产生任何影响。