RoaringBitmap是一种压缩位图，其性能优于WAH、EWAH、Concise等传统压缩位图。在特定场景下，RoaringBitmap可以在提供良好压缩性能的条件下，仍然具备快于传统压缩位图近百倍的索引性能，甚至性能可以超过未采用压缩的位图。
创建插件
postgres=# CREATE EXTENSION if not exists roaringbitmap;
CREATE EXTENSION查看插件版本
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
roaringbitmap | 0.5 | public | support for Roaring Bitmaps
(2 rows)输入和输出格式
PolarDB目前仅支持array和bytea两种输入输出格式。
- 输入格式
- array
postgres=# select roaringbitmap('{1,100,10}'); roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row) - bytea
postgres=# select '\x3a30000001000000000002001000000001000a006400'::roaringbitmap; roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)
- array
- 输出格式
说明 输出格式默认为
bytea，您可以通过
roaringbitmap.output_format修改输出格式。postgres=# set roaringbitmap.output_format='bytea'; SET postgres=# select '{1}'::roaringbitmap; roaringbitmap ---------------------------------------- \x3a3000000100000000000000100000000100 (1 row) postgres=# set roaringbitmap.output_format='array'; SET postgres=# select '{1}'::roaringbitmap; roaringbitmap --------------- {1} (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}’) | f | |
| integer,roaringbitmap | bool | 包含。 | 3 | 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}’) | 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}’)) | 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 |
