查询 json / jsonb 列超级慢。我可以使用索引吗？

我正在尝试加快对 PostgreSQL 数据库中存储的一些 json 数据的查询速度。我继承了一个查询 PostgreSQL 表的应用程序，名为data有一个名为value其中 value 是 json 类型的 blobjsonb.

它大约有 300 行，但需要 12 秒才能从 5 个 json 元素中选择此数据。 json blob 有点大，但我需要的数据都在 json 嵌套的顶层（如果有帮助的话）。

我尝试添加索引CREATE INDEX idx_tbl_data ON data USING gin (value);但这没有帮助。我应该使用不同的索引吗？长期愿景是重写应用程序以将数据移出 json，但由于应用程序其他部分的复杂性，这至少需要 30-40 个工作日的工作，所以我想看看是否可以可以在短期内加快速度。

不确定它是否有帮助，但构成此结果集的基础数据不会经常更改。经常发生变化的是 json blob 中更下方的数据。

SELECT
  value::json ->> 'name' AS name,
  value::json ->> 'mnemonic' AS mnemonic,
  value::json ->> 'urlName' AS "urlName",
  value::json ->> 'countryCode' AS "countryCode",
  value::json #>>'{team}' AS team
FROM
  data;

就像一匹马已经建议的那样（并且你提到了你自己），正确修复就是将这些属性提取到单独的列中，在一定程度上规范化您的设计。

索引有帮助吗？

Sadly, no（从 Postgres 14 开始）。

It could理论上工作。既然你的价值观是big，即使在检索所有行时（否则它会忽略索引），Postgres 也可以在仅索引扫描中拾取仅包含一些小属性的表达式索引。

手册： https://www.postgresql.org/docs/current/indexes-index-only-scans.html

然而，PostgreSQL 的规划器目前对于此类情况还不是很聪明。仅当查询所需的所有列均可从索引中获取时，它才认为查询可能通过仅索引扫描来执行。

所以你必须包括value本身在索引中，即使就像INCLUDE专栏-完全破坏了整个想法。不去。

你可能仍然可以做某物在短期内。两句关键的引言：

我想看看是否可以在短期内加快速度

json blob 有点大

数据类型

将演员表放到json从查询中。每次铸造都会增加无意义的成本。

压缩

一个主要的成本因素是压缩。 Postgres 必须“脱吐司” https://www.postgresql.org/docs/current/storage-toast.html整个大专栏，只是为了提取一些小属性。自从Postgres 14，您可以切换压缩算法（如果您的版本启用了支持！）。默认值由配置设置决定default_toast_compression https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION，设置为pglz默认情况下。目前唯一可用的替代方案是lz4。您可以对每列进行设置。任何时候。

LZ4 (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 https://www.postgresql.fastware.com/blog/what-is-the-new-lz4-toast-compression-in-postgresql-14

GENERATED columns

当坚持破损的设计时，您可能只需添加一些（小！）生成的列来覆盖您的查询：

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 at all.

SELECT name, mnemonic, ... FROM data;

这将绕过主要的性能问题。

See:

• PostgreSQL 中的计算/计算/虚拟/派生列 https://stackoverflow.com/questions/8250389/computed-calculated-virtual-derived-columns-in-postgresql/8250729#8250729

然而，您提到：

经常发生变化的是 json blob 中更下方的数据。

每次更改为value强制重新检查生成的列，从而增加写入成本。