PostgreSQL multirange 范围检索, 支持按时段、按对象 多维度搜索视频内容例子

作者

digoal

日期

2022-04-12

标签

PostgreSQL , GiST , range , multirange , 范围检索

PostgreSQL 14开始支持multirange类型, 例如一个视频按内容结构化之后, 将对象与出现在视频时间分段的关系建模.

内容结构化 + 时间段映射

例如
- 和人物结合, 哪些时段出现了哪些人?
- 和地点结合, 哪些时间段在什么地方?
- 监控视频, 结合人物、地点、物品、车辆等, 映射到时间段.

table  
id: video ID  
k: object  
v: multirange  
复制

create extension btree_gist;  
create index idx on table using gist (k,v);  
复制

某个时间点出现了xx人物的视频

select id from table where k=? and v @> ?  
复制

https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INDEXING

GiST and SP-GiST indexes can be created for table columns of range types. GiST indexes can be also created for table columns of multirange types. For instance, to create a GiST index:

CREATE INDEX reservation_idx ON reservation USING GIST (during);  
复制

A GiST or SP-GiST index on ranges can accelerate queries involving these range operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>.
A GiST index on multiranges can accelerate queries involving the same set of multirange operators.
A GiST index on ranges and GiST index on multiranges can also accelerate queries involving these cross-type range to multirange and multirange to range operators correspondingly: &&, <@, @>, <<, >>, -|-, &<, and &>.

https://www.postgresql.org/docs/current/functions-range.html#RANGE-OPERATORS-TABLE

注意multirange的gist索引也可能存在bound box的放大问题, 先按multirange最大边界进行过滤, 然后在multirange的多个range内进行recheck.

建议存储边界不要太大, 容易造成搜索放大.

postgres=# select int4multirange(int4range(1,10),int4range(1,100));
 int4multirange 
----------------
 {[1,100)}
(1 row)
postgres=# select int4multirange(int4range(1,10),int4range(11,100));
  int4multirange   
-------------------
 {[1,10),[11,100)}
(1 row)
postgres=# select int4multirange(int4range(1,10),int4range(10,100));
 int4multirange 
----------------
 {[1,100)}
(1 row)
postgres=# select int4multirange(int4range(1,10),int4range(11,100));
  int4multirange   
-------------------
 {[1,10),[11,100)}
(1 row)
postgres=# select int4multirange(int4range(1,10),int4range(91,100));
  int4multirange   
-------------------
 {[1,10),[91,100)}
(1 row)
复制

《PostgreSQL multipolygon 空间索引查询过滤精简优化 - IO，CPU放大优化》

《PostgreSQL 空间切割(st_split, ST_Subdivide)功能扩展 - 空间对象网格化 (多边形GiST优化)》

《PostgreSQL 空间st_contains，st_within空间包含搜索优化 - 降IO和降CPU(bound box) (多边形GiST优化)》

期望 PostgreSQL 增加什么功能?

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

PostgreSQL 解决方案集合

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