在postgresql上创建索引

cl25kdpy  于 2021-07-29  发布在  Java
关注(0)|答案(1)|浏览(351)

我有一个问题,我面临的困难,在创建索引。

TableName(My_Table)
ColumnName(Repo_id(INT),Data JSONB)

jsonb结构:

{
   "Property_1":'1',
   "Property_2":'2',
   "Property_3":'3',
   "Property_4":'4',
   "Property_5":'5'
}

对于一个查询:

select *
from my_table
where repo_id = 1
and Data ->> 'Property_1' = '1'

我添加了btree索引(repo\u id,(data->>'property\u 1'),它在这种情况下运行良好。
对于其他场景,如

select *
 from my_table
 where repo_id = 2
 and Data ->> 'Property_2' = '2'

它没有给我最佳的计划。为此,我必须将以前的索引修改为覆盖索引(repo\u id,(data->>'property\u 1',((data->>'property\u 2')),这给了我最佳的计划。
我在列中有100多个json属性,对于where条件下的每个repo\u id…json属性过滤器将不同。我不认为将所有这些列添加为覆盖索引是明智的,因为这样会增加索引的大小。
请建议如何在动态json属性过滤器上高效地创建索引。

vngu2lb8

vngu2lb81#

使用gin索引并更改where子句:

create index on the_table  using gin (data);

然后使用contains操作符 @> :

select *
from my_table
where data @> '{"Property_2": 2}';

条件 where data ->> 'Property_2' = '2' 不会使用该索引。必须使用一个支持的运算符类才能使用索引。
如果 @> 运算符将支持所有您想做的事情,使用不同的运算符类可以提高索引的效率:

create index on the_table  using gin (data jsonb_path_ops);

在这个操作符类中,操作符
? ?& 以及 ?| 不会利用那个索引。

相关问题