小编典典

jsonb键/值上的模式匹配

json

我正在使用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,然后搜索所需的键/值。这种方法似乎行不通。


阅读 218

收藏
2020-07-27

共1个答案

小编典典

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

最佳索引策略取决于您的整体情况。有很多选择。要仅介绍您提供的一个键,可以使用 功能性三字母索引
。您已经测试过gin_trgm_ops,因此已经熟悉附加模块pg_trgm

安装模块后:

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.
2020-07-27