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目前仅支持arraybytea两种输入输出格式。

  • 输入格式
    • array
      postgres=# select roaringbitmap('{1,100,10}');
                       roaringbitmap                  
      ------------------------------------------------
       \x3a30000001000000000002001000000001000a006400
      (1 row)
    • bytea
      postgres=# select '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
                       roaringbitmap                  
      ------------------------------------------------
       \x3a30000001000000000002001000000001000a006400
      (1 row)
  • 输出格式
    说明 输出格式默认为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