postgresql – 为模式匹配搜索索引jsonb数据

概述这是后续行动： Pattern matching on jsonb key/value 我有一张桌子如下 CREATE TABLE "PreStage".transaction ( transaction_id serial NOT NULL, transaction jsonb CONSTRAINT pk_transaction PRIMARY KEY (transaction_id 这是后续行动：
@L_502_0@

我有一张桌子如下

CREATE table "PreStage".transaction (  transaction_ID serial NOT NulL,transaction Jsonb  CONSTRAINT pk_transaction PRIMARY KEY (transaction_ID));

我的事务Jsonb列中的内容看起来像

{"ADDR": "abcd","CITY": "abcd","PROV": "","ADDR2": "","ADDR3": "","CNSNT": "Research-NA","CNTRY": "NL","EMAIL": "@.com","PHONE": "12345","HCO_NM": "HELLO","UNQ_ID": "","PSTL_CD": "1234","HCP_SR_NM": "","HCP_FST_NM": "","HCP_MID_NM": ""}

我需要搜索查询,如：

SELECT transaction AS data FROM   "PreStage".transactionWHERE  transaction->>'HCP_FST_NM' IliKE '%neer%';

但我需要让我的用户灵活地搜索任何键/值.

对上一个问题的回答建议将索引创建为：

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

哪个有效,但我也想索引其他键.因此尝试了类似的事情：

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

哪个不起作用.这里最好的索引方法是什么,或者我必须为每个键创建一个单独的索引,在这种情况下,如果将新的键/值对添加到数据中,该方法将不是通用的.

解决方法 抛弃 @jjanes pointed out的语法错误,
对于一些流行密钥的混合(包含在许多行和/或经常搜索)加上许多更罕见的密钥(包含在几行和/或很少搜索,新密钥可能会动态d出)我建议这个组合：

流行键的Trigram索引

您似乎不会经常在一次搜索中组合多个键,并且具有多个键的单个索引将变得非常大且缓慢.所以我会为每个热门密钥创建一个单独的索引.使其成为大多数行中未包含的键的部分索引：

CREATE INDEX trans_idxgin_HCP_FST_NM ON transaction  -- contained in most rowsUSING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops);CREATE INDEX trans_idxgin_ADDR ON transaction  -- not in most rowsUSING gin ((transaction->>'ADDR') gin_trgm_ops)WHERE transaction ? 'ADDR';

等等.在我之前的回答中详细说明：

> Pattern matching on jsonb key/value

基本的Jsonb GIN索引

如果您有许多不同的键和/或动态添加新键,您可以使用基本(默认)Jsonb_ops GIN索引覆盖其余的键：

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

除此之外,这支持搜索键.但是你不能将它用于值的模式匹配.

> What’s the proper index for querying structures in arrays in Postgres jsonb?

询问

结合处理两个索引的谓词：

SELECT transaction AS dataFROM   "PreStage".transactionWHERE  transaction->>'HCP_FST_NM' IliKE '%neer%'AND    transaction ? 'HCP_FST_NM';  -- even if that seems redundant.

第二个条件恰好匹配我们的部分索引.

因此,给定(常用/常用)键有一个特定的三元组索引,或者至少有一个索引来查找包含稀有键的(少数)行 – 然后过滤匹配值.相同的查询应该给你两全其美.

一定要运行最新版本的Postgres,最近有成本估算的各种更新. Postgres使用良好的估计和当前的表统计信息来选择最佳查询计划至关重要.

总结

以上是内存溢出为你收集整理的postgresql – 为模式匹配搜索索引jsonb数据全部内容，希望文章能够帮你解决postgresql – 为模式匹配搜索索引jsonb数据所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。