json - jsonb 键/值上的模式匹配

Question

我正在使用 PostgreSQL 9.4。我的表有一个 jsonb 列:

CREATE TABLE "PreStage".transaction (
  transaction_id serial NOT NULL,
  transaction jsonb
  CONSTRAINT pk_transaction PRIMARY KEY (transaction_id)
);

CREATE INDEX idxgin ON "PreStage".transaction USING gin (transaction);

我在 JSONB 列中根据键/值存储交易。其中一项要求是从键值中搜索客户名称，因此我正在运行如下查询:

SELECT transaction as data FROM "PreStage".transaction
WHERE  transaction->>('HCP_FST_NM') ilike ('%neer%');

无论我做什么，查询似乎都不喜欢 GIN 索引。如何使查询使用不区分大小写的模式搜索的 GIN 索引？

我尝试将 jsonb 列更改为文本，使用 gin_trgm_ops 对其进行索引然后搜索所需的文本，然后将结果转换为 json 然后搜索所需的键/值。这种方法似乎行不通。

Answer 1

默认的 GIN 索引运算符类 jsonb_ops 不允许对值进行全文模式匹配。详情:

• What's the proper index for querying structures in arrays in Postgres jsonb?

最佳索引策略取决于您的整体情况。有很多选择。要仅覆盖您提供的一个键，您可以使用功能三元组索引。您已经测试过 gin_trgm_ops，因此您已经熟悉附加模块 pg_trgm .对于那些不是:

• PostgreSQL LIKE query performance variations

安装模块后:

CREATE INDEX idxgin ON "PreStage".transaction
USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops);

那么这个查询是支持的:

SELECT transaction AS data
FROM   "PreStage".transaction
WHERE  transaction->>'HCP_FST_NM' ILIKE '%neer%';

我还删除了一些不必要的括号。

根据未知的细节，有多种选项可以优化索引覆盖率。

例如，如果许多行根本没有键 'HCP_FST_NM'，请将其设为部分索引 排除不相关的行并保持索引较小:

CREATE INDEX idxgin ON "PreStage".transaction
USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops)
WHERE transaction ? 'HCP_FST_NM';

? being the jsonb containment operator.
并将相同的谓词添加到应该使用该索引的每个查询:

SELECT transaction AS data
FROM   "PreStage".transaction
WHERE  transaction->>'HCP_FST_NM' ILIKE '%neer%'
AND    transaction ? 'HCP_FST_NM';  -- even if that seems redundant.