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不允许对值进行全文模式匹配。细节:

• 在Postgres jsonb中查询数组结构的正确索引是什么?

最佳索引策略取决于您的具体情况。有很多选择。为了覆盖您提供的一个键，您可以使用函数式三元组索引。您已经测试了gin_trgm_ops，因此您已经熟悉了附加模块pg_trgm。对于那些没有:

• PostgreSQL LIKE查询性能变化

模块安装后:

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';

? 为jsonb包含操作符。
并将相同的谓词添加到应该使用此索引的每个查询:

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