RoaringBitmap是一种压缩位图，其性能优于WAH、EWAH、Concise等传统压缩位图。在特定场景下，RoaringBitmap可以在提供良好压缩性能的条件下，仍然具备快于传统压缩位图近百倍的索引性能，甚至性能可以超过未采用压缩的位图。
创建插件
CREATE EXTENSION if NOT EXISTS roaringbitmap;创建插件成功后可通过以下SQL语句查看版本：
SELECT extname,extversion FROM pg_extension WHERE extname = 'roaringbitmap';返回结果如下：
extname | extversion
---------------+------------
roaringbitmap | 0.5
(1 row)输入和输出格式
PolarDB目前仅支持array和bytea两种输入输出格式。
输入格式
array
SELECT roaringbitmap('{1,100,10}'); roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)bytea
SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap; roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)
输出格式
说明输出格式默认为
bytea，您可以通过roaringbitmap.output_format修改输出格式。array
SET roaringbitmap.output_format='array'; SELECT '{1}'::roaringbitmap; roaringbitmap --------------- {1} (1 row)bytea
SET roaringbitmap.output_format='bytea'; SELECT '{1}'::roaringbitmap; roaringbitmap ---------------------------------------- \x3a3000000100000000000000100000000100 (1 row)
使用说明
输入和输出格式
PolarDB目前仅支持array和bytea两种输入输出格式。
输入格式
array
SELECT roaringbitmap('{1,100,10}'); roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)bytea
SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap; roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)
输出格式
说明输出格式默认为
bytea，您可以通过roaringbitmap.output_format修改输出格式。array
SET roaringbitmap.output_format='array'; SELECT '{1}'::roaringbitmap; roaringbitmap --------------- {1} (1 row)bytea
SET roaringbitmap.output_format='bytea'; SELECT '{1}'::roaringbitmap; roaringbitmap ---------------------------------------- \x3a3000000100000000000000100000000100 (1 row)
创建表
CREATE TABLE t1 (id integer, bitmap roaringbitmap);整数集合创建位图
INSERT INTO t1 SELECT 1,rb_build(ARRAY[1,2,3,4,5,6,7,8,9,200]);
INSERT INTO t1 SELECT 2,rb_build_agg(e) FROM generate_series(1,100) e;位图计算函数
位图计算函数包含OR、AND、XOR、ANDNOT。
SELECT roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');位图聚合函数
位图聚合函数包含OR、AND、XOR、BUILD。
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;位图基数计算函数
SELECT rb_cardinality('{1,2,3}');位图转换整数集合
SELECT rb_to_array(bitmap) FROM t1 WHERE id = 1;位图转换整数
支持以下两种方式将位图转换为整数。
SELECT unnest(rb_to_array('{1,2,3}'::roaringbitmap));SELECT rb_iterate('{1,2,3}'::roaringbitmap);
操作列表
操作符 | 输入 | 输出 | 描述 | 示例 | 结果 |
& | roaringbitmap,roaringbitmap | roaringbitmap | 位元且运算。 | roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}') | {3} |
| | roaringbitmap,roaringbitmap | roaringbitmap | 位元或运算。 | roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}') | {1,2,3,4,5} |
| | roaringbitmap,integer | roaringbitmap | 将元素添加到RoaringBitmap中。 | roaringbitmap('{1,2,3}') | 6 | {1,2,3,6} |
| | integer,roaringbitmap | roaringbitmap | 将元素添加到RoaringBitmap中。 | 6 | roaringbitmap('{1,2,3}') | {1,2,3,6} |
# | roaringbitmap,roaringbitmap | roaringbitmap | 位元互斥或运算。 | roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}') | {1,2,4,5} |
<< | roaringbitmap,bigint | roaringbitmap | 按位左移。 | roaringbitmap('{1,2,3}') << 2 | {0,1} |
>> | roaringbitmap,bigint | roaringbitmap | 按位右移。 | roaringbitmap('{1,2,3}') >> 3 | {4,5,6} |
- | roaringbitmap,roaringbitmap | roaringbitmap | 差异。 | roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}') | {1,2} |
- | roaringbitmap,integer | roaringbitmap | 从RoaringBitmap中删除元素。 | roaringbitmap('{1,2,3}') - 3 | {1,2} |
@> | roaringbitmap,roaringbitmap | bool | 包含。 | roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}') | f |
@> | roaringbitmap,integer | bool | 包含。 | roaringbitmap('{1,2,3,4,5}') @> 3 | t |
roaringbitmap,roaringbitmap | bool | 包含。 | roaringbitmap('{1,2,3}') @> 4 | f | |
integer,roaringbitmap | bool | 包含。 | 3 @> roaringbitmap('{1,2,3,4,5}') | t | |
&& | roaringbitmap,roaringbitmap | bool | 重叠。 | roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}') | t |
= | roaringbitmap,roaringbitmap | bool | 等于。 | roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}') | f |
<> | roaringbitmap,roaringbitmap | bool | 不等于。 | roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}') | t |
功能函数列表
函数 | 输入 | 输出 | 描述 | 示例 | 结果 |
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_and_cardinality('{1,2,3}',rb_build('{3,4,5}')) | 1 |
rb_or_cardinality | roaringbitmap,roaringbitmap | bigint | Return cardinality of the OR of two roaringbitmaps | rb_or_cardinality('{1,2,3}','{3,4,5}') | 5 |
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}') | f |
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}')) | 123 |
聚合函数列表
函数 | 输入 | 输出 | 描述 | 示例 | 结果 |
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 |