位图计算 pg_roaringbitmap 插件

Recent Pages

位图计算 pg_roaringbitmap 插件

最后更新时间：2024-01-22 16:22:57

云数据库 PostgreSQL 提供 pg_roaringbitmap 插件，可以使用位图计算功能，提高查询性能。
前提条件
实例为云数据库 PostgreSQL 10、11、12、13、14、15 全新版本。
背景信息
Roaring Bitmap 算法是将32位的 INT 类型数据划分为216个数据块（Chunk），每一个数据块对应整数的高16位，并使用一个容器（Container）来存放一个数值的低16位。
Roaring Bitmap 将这些容器保存在一个动态数组中，作为一级索引。容器使用两种不同的结构：数组容器（Array Container）和位图容器（Bitmap Container）。数组容器存放稀疏的数据，位图容器存放稠密的数据。如果一个容器里面的整数数量小于4096，就用数组容器来存储值。若大于4096，就用位图容器来存储值。
采用这种存储结构，Roaring Bitmap 可以快速检索一个特定的值。在做位图计算（AND、OR、XOR）时，Roaring Bitmap 提供了相应的算法来高效地实现在两种容器之间的运算。使得 Roaring Bitmap 无论在存储和计算性能上都表现优秀。
操作步骤
1. 创建插件。示例如下：
CREATE EXTENSION roaringbitmap;
2. 创建带有 RoaringBitmap 数据类型的表。示例如下：
CREATE TABLE t1 (id integer, bitmap roaringbitmap);
3. 使用 rb_build 函数插入 roaringbitmap 的数据。示例如下：
--数组位置对应的 BIT 值为1
INSERT INTO t1 SELECT 1,RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);
--将输入的多条记录的值对应位置的 BIT 值设置为1，最后聚合为一个 roaringbitmap  
INSERT INTO t1 SELECT 2,RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;
4. 进行 Bitmap 计算（OR、AND、XOR、ANDNOT）。示例如下：
--数组位置对应的 BIT 值为1
SELECT RB_OR(a.bitmap,b.bitmap) FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,(SELECT bitmap FROM t1 WHERE id = 2) AS b;
5. 进行 Bitmap 聚合计算（OR、AND、XOR、BUILD），并生成新的 roaringbitmap 类型。示例如下：
SELECT RB_OR_AGG(bitmap) FROM t1;
SELECT RB_AND_AGG(bitmap) FROM t1;
SELECT RB_XOR_AGG(bitmap) FROM t1;
SELECT RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;
6. 统计基数（Cardinality），即统计 roaringbitmap 中包含多少个位置为1的 BIT 位。示例如下：
SELECT RB_CARDINALITY(bitmap) FROM t1;
7. 从 roaringbitmap 中返回位置为1的 BIT 下标（即位置值）。示例如下：
SELECT RB_ITERATE(bitmap) FROM t1 WHERE id = 1;
功能函数列表
函数名
输入
输出
描述
示例
结果
rb_build
integer[]
roaringbitmap
Create roaringbitmap from integer array
rb_build('{1,2,3,4,5}')
{1,2,3,4,5}
rb_index
roaringbitmap,integer
bigint
Return the 0-based index of element in this roaringbitmap, or -1 if do not exsits
rb_index('{1,2,3}',3)
2
rb_cardinality
roaringbitmap
bigint
Return cardinality of the roaringbitmap
rb_cardinality('{1,2,3,4,5}')
5
rb_and_cardinality
roaringbitmap,roaringbitmap
bigint
Return cardinality of the AND of two roaringbitmaps
rb_or_cardinality('{1,2,3}','{3,4,5}')
1
rb_xor_cardinality
roaringbitmap,roaringbitmap
bigint
Return cardinality of the XOR of two roaringbitmaps
rb_xor_cardinality('{1,2,3}','{3,4,5}')
4
rb_andnot_cardinality
roaringbitmap,roaringbitmap
bigint
Return cardinality of the ANDNOT of two roaringbitmaps
rb_andnot_cardinality('{1,2,3}','{3,4,5}')
2
rb_is_empty
roaringbitmap
boolean
Check if roaringbitmap is empty.
rb_is_empty('{1,2,3,4,5}')
t
rb_fill
roaringbitmap,range_start bigint,range_end bigint
roaringbitmap
Fill the specified range (not include the range_end)
rb_fill('{1,2,3}',5,7)
{1,2,3,5,6}
rb_clear
roaringbitmap,range_start bigint,range_end bigint
roaringbitmap
Clear the specified range (not include the range_end)
rb_clear('{1,2,3}',2,3)
{1,3}
rb_flip
roaringbitmap,range_start bigint,range_end bigint
roaringbitmap
Negative the specified range (not include the range_end)
rb_flip('{1,2,3}',2,10)
{1,4,5,6,7,8,9}
rb_range
roaringbitmap,range_start bigint,range_end bigint
roaringbitmap
Return new set with specified range (not include the range_end)
rb_range('{1,2,3}',2,3)
{2}
rb_range_cardinality
roaringbitmap,range_start bigint,range_end bigint
bigint
Return the cardinality of specified range (not include the range_end)
rb_range_cardinality('{1,2,3}',2,3)
1
rb_min
roaringbitmap
integer
Return the smallest offset in roaringbitmap. Return NULL if the bitmap is empty
rb_min('{1,2,3}')
1
rb_max
roaringbitmap
integer
Return the greatest offset in roaringbitmap. Return NULL if the bitmap is empty
rb_max('{1,2,3}')
3
rb_rank
roaringbitmap,integer
bigint
Return the number of elements that are smaller or equal to the specified offset
rb_rank('{1,2,3}',3)
3
rb_jaccard_dist
roaringbitmap,roaringbitmap
double precision
Return the jaccard distance(or the Jaccard similarity coefficient) of two bitmaps
rb_jaccard_dist('{1,2,3}','{3,4}')
0.25
rb_select
roaringbitmap,bitset_limit bigint,bitset_offset bigint=0,reverse boolean=false,range_start bigint=0,range_end bigint=4294967296
roaringbitmap
Return subset [bitset_offset,bitset_offset+bitset_limit) of bitmap between range [range_start,range_end)
rb_select('{1,2,3,4,5,6,7,8,9}',5,2)
{3,4,5,6,7}
rb_to_array
roaringbitmap
integer[]
Convert roaringbitmap to integer array
rb_to_array(roaringbitmap('{1,2,3}'))
{1,2,3}
rb_iterate
roaringbitmap
SET of integer
Return set of integer from a roaringbitmap data.
SELECT rb_iterate(rb_build('{1,2,3}'))
1
2
3
聚合函数列表
聚合函数名
输入
输出
描述
示例
结果
rb_build_agg
integer
roaringbitmap
Build a roaringbitmap from a integer set
select rb_build_agg(id) from (values (1),(2),(3)) t(id)
{1,2,3}
rb_or_agg
roaringbitmap
roaringbitmap
AND Aggregate calculations from a roaringbitmap set
select rb_or_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)
{1,2,3,4}
rb_and_agg
roaringbitmap
roaringbitmap
AND Aggregate calculations from a roaringbitmap set
select rb_and_agg(bitmap)  from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)
{2,3}
rb_xor_agg
roaringbitmap
roaringbitmap
XOR Aggregate calculations from a roaringbitmap set
select rb_xor_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)
{1,4}
rb_or_cardinality_agg
roaringbitmap
bigint
OR Aggregate calculations from a roaringbitmap set, return cardinality.
select rb_or_cardinality_agg(bitmap)  from (values (roaringbitmap('{1,2,3}')),(roaringbitmap('{2,3,4}')) ) t(bitmap)
4
rb_and_cardinality_agg
roaringbitmap
bigint
AND Aggregate calculations from a roaringbitmap set, return cardinality
select rb_and_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)
2
rb_xor_cardinality_agg
roaringbitmap
bigint
XOR Aggregate calculations from a roaringbitmap set, return cardinality
select rb_xor_cardinality_agg(bitmap)  from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)
2
﻿

联系我们

联系我们，为您的业务提供专属服务。

技术支持

如果你想寻求进一步的帮助，通过工单与我们进行联络。我们提供7x24的工单服务。

7x24 电话支持

tencent cloud

Recent Pages

位图计算 pg_roaringbitmap 插件

前提条件

背景信息

操作步骤

功能函数列表

聚合函数列表

本页内容是否解决了您的问题？

本页内容是否解决了您的问题？

函数名	输入	输出	描述	示例	结果
rb_build	integer[]	roaringbitmap	Create roaringbitmap from integer array	`rb_build('{1,2,3,4,5}')`	{1,2,3,4,5}
rb_index	roaringbitmap,integer	bigint	Return the 0-based index of element in this roaringbitmap, or -1 if do not exsits	`rb_index('{1,2,3}',3)`	2
rb_cardinality	roaringbitmap	bigint	Return cardinality of the roaringbitmap	`rb_cardinality('{1,2,3,4,5}')`	5
rb_and_cardinality	roaringbitmap,roaringbitmap	bigint	Return cardinality of the AND of two roaringbitmaps	`rb_or_cardinality('{1,2,3}','{3,4,5}')`	1
rb_xor_cardinality	roaringbitmap,roaringbitmap	bigint	Return cardinality of the XOR of two roaringbitmaps	`rb_xor_cardinality('{1,2,3}','{3,4,5}')`	4
rb_andnot_cardinality	roaringbitmap,roaringbitmap	bigint	Return cardinality of the ANDNOT of two roaringbitmaps	`rb_andnot_cardinality('{1,2,3}','{3,4,5}')`	2
rb_is_empty	roaringbitmap	boolean	Check if roaringbitmap is empty.	`rb_is_empty('{1,2,3,4,5}')`	t
rb_fill	roaringbitmap,range_start bigint,range_end bigint	roaringbitmap	Fill the specified range (not include the range_end)	`rb_fill('{1,2,3}',5,7)`	{1,2,3,5,6}
rb_clear	roaringbitmap,range_start bigint,range_end bigint	roaringbitmap	Clear the specified range (not include the range_end)	`rb_clear('{1,2,3}',2,3)`	{1,3}
rb_flip	roaringbitmap,range_start bigint,range_end bigint	roaringbitmap	Negative the specified range (not include the range_end)	`rb_flip('{1,2,3}',2,10)`	{1,4,5,6,7,8,9}
rb_range	roaringbitmap,range_start bigint,range_end bigint	roaringbitmap	Return new set with specified range (not include the range_end)	`rb_range('{1,2,3}',2,3)`	{2}
rb_range_cardinality	roaringbitmap,range_start bigint,range_end bigint	bigint	Return the cardinality of specified range (not include the range_end)	`rb_range_cardinality('{1,2,3}',2,3)`	1
rb_min	roaringbitmap	integer	Return the smallest offset in roaringbitmap. Return NULL if the bitmap is empty	`rb_min('{1,2,3}')`	1
rb_max	roaringbitmap	integer	Return the greatest offset in roaringbitmap. Return NULL if the bitmap is empty	`rb_max('{1,2,3}')`	3
rb_rank	roaringbitmap,integer	bigint	Return the number of elements that are smaller or equal to the specified offset	`rb_rank('{1,2,3}',3)`	3
rb_jaccard_dist	roaringbitmap,roaringbitmap	double precision	Return the jaccard distance(or the Jaccard similarity coefficient) of two bitmaps	`rb_jaccard_dist('{1,2,3}','{3,4}')`	0.25
rb_select	roaringbitmap,bitset_limit bigint,bitset_offset bigint=0,reverse boolean=false,range_start bigint=0,range_end bigint=4294967296	roaringbitmap	Return subset [bitset_offset,bitset_offset+bitset_limit) of bitmap between range [range_start,range_end)	`rb_select('{1,2,3,4,5,6,7,8,9}',5,2)`	{3,4,5,6,7}
rb_to_array	roaringbitmap	integer[]	Convert roaringbitmap to integer array	`rb_to_array(roaringbitmap('{1,2,3}'))`	{1,2,3}
rb_iterate	roaringbitmap	SET of integer	Return set of integer from a roaringbitmap data.	`SELECT rb_iterate(rb_build('{1,2,3}'))`	1 2 3

聚合函数名	输入	输出	描述	示例	结果
rb_build_agg	integer	roaringbitmap	Build a roaringbitmap from a integer set	`select rb_build_agg(id) from (values (1),(2),(3)) t(id)`	{1,2,3}
rb_or_agg	roaringbitmap	roaringbitmap	AND Aggregate calculations from a roaringbitmap set	`select rb_or_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	{1,2,3,4}
rb_and_agg	roaringbitmap	roaringbitmap	AND Aggregate calculations from a roaringbitmap set	`select rb_and_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	{2,3}
rb_xor_agg	roaringbitmap	roaringbitmap	XOR Aggregate calculations from a roaringbitmap set	`select rb_xor_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	{1,4}
rb_or_cardinality_agg	roaringbitmap	bigint	OR Aggregate calculations from a roaringbitmap set, return cardinality.	`select rb_or_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')),(roaringbitmap('{2,3,4}')) ) t(bitmap)`	4
rb_and_cardinality_agg	roaringbitmap	bigint	AND Aggregate calculations from a roaringbitmap set, return cardinality	`select rb_and_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}'))) t(bitmap)`	2
rb_xor_cardinality_agg	roaringbitmap	bigint	XOR Aggregate calculations from a roaringbitmap set, return cardinality	`select rb_xor_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)`	2

tencent cloud

注册

登录

Recent Pages

位图计算 pg_roaringbitmap 插件

前提条件

背景信息

操作步骤

功能函数列表

聚合函数列表

本页内容是否解决了您的问题？

本页内容是否解决了您的问题？