JSON 局部相似 搜索例子

作者

digoal

日期

2022-03-23

标签

PostgreSQL , view , base relation , 权限检测 , invoker , owner , user , definer

获取局部相似的例子

• 一个JSON字段, 里面有若干KEY, 每一行的KEY的个数、内容、KEY本身都可能不一样.
• KEY的个数可能超过1000, 超出单行可容纳的最大列数.
• KEY可能在业务使用过程中动态增加.

获取局部相似:
- 1、要求包含某个KEY,
- 2、要求按这个KEY的VALUE(数值、时间等可比较类型)取出相近的记录,
- 3、按相似度排序(即离值近的优先输出, 需要<->操作符的支持, 用到GiST索引), 并limit N条.

1、因为每一行的KEY的个数、内容、KEY本身都可能不一样. 而且KEY的个数可能超过1000, 超出单行可容纳的最大列数. KEY可能在业务使用过程中动态增加. 所以类型只能选择JSON

2、要求包含某个KEY,
如果返回的记录数非常多, 后面涉及到排序的记录就会非常多, 排序耗费CPU.

3、要求按这个KEY的VALUE(数值、时间等可比较类型)取出相近的记录,
如果加索引, 每个可能的KEY都要加表达式索引, 索引可能会特别多. 如果写入频繁, 写入的延迟会因为每增加1个索引而变长, 写入性能大打折扣.
https://www.postgresql.org/docs/current/functions-json.html

create index idx_t_1 on t using btree ((jsb->>'key_name')::numeric) where jsb ? 'key_name';  
或者  
create extension btree_gist;  
create index idx_t_1 on t using gist ((jsb->>'key_name')::numeric) where jsb ? 'key_name';  
select * from t where jsb ? 'key_name' order by (jsb->>'key_name')::numeric <-> 1.234 limit 10;  
复制

4、按相似度排序(即离值近的优先输出, 需要<->操作符的支持, 用到GiST索引), 并limit N条.
可以使用btree_gist 插件GiST索引, 也可以正反各取N条再返回.

create table tbl (id int, info text);  
insert into tbl select generate_series(1,1000);  
create extension btree_gist;  
create index idx_tbl on tbl using gist(id);  
postgres=# select * from tbl order by id <-> 100 limit 5;  
 id  | info   
-----+------  
 100 |   
 101 |   
  99 |   
 102 |   
  98 |   
(5 rows)  
或者  
drop index idx_tbl;  
create index idx_tbl on tbl using btree(id);    
postgres=# with a as (select * from tbl where id>=100 order by id limit 5),   
b as (select * from tbl where id<100 order by id desc limit 5)   
select * from (select * from a union all select * from b) t   
order by abs(id-100)    
limit 5;  
 id  | info   
-----+------  
 100 |   
 101 |   
  99 |   
  98 |   
 102 |   
(5 rows)  
复制

期望 PostgreSQL 增加什么功能?

PolarDB for PostgreSQL云原生分布式开源数据库

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.