RoaringBitmap是一种压缩位图,其性能优于WAH、EWAH、Concise等传统压缩位图。在特定场景下,RoaringBitmap可以在提供良好压缩性能的条件下,仍然具备快于传统压缩位图近百倍的索引性能,甚至性能可以超过未采用压缩的位图。

创建插件

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,roaringbitmaproaringbitmap位元且运算。roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}'){3}
|roaringbitmap,roaringbitmaproaringbitmap位元或运算。roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}'){1,2,3,4,5}
|roaringbitmap,integerroaringbitmap将元素添加到RoaringBitmap中。roaringbitmap('{1,2,3}') | 6{1,2,3,6}
|integer,roaringbitmaproaringbitmap将元素添加到RoaringBitmap中。6 | roaringbitmap('{1,2,3}'){1,2,3,6}
#roaringbitmap,roaringbitmaproaringbitmap位元互斥或运算。roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}'){1,2,4,5}
<<roaringbitmap,bigintroaringbitmap按位左移。roaringbitmap('{1,2,3}') << 2{0,1}
>>roaringbitmap,bigintroaringbitmap按位右移。roaringbitmap('{1,2,3}') >> 3{4,5,6}
-roaringbitmap,roaringbitmaproaringbitmap差异。roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}'){1,2}
-roaringbitmap,integerroaringbitmap从RoaringBitmap中删除元素。roaringbitmap('{1,2,3}') - 3{1,2}
@>roaringbitmap,roaringbitmapbool包含。roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}')f
@>roaringbitmap,integerbool包含。roaringbitmap('{1,2,3,4,5}') @> 3t
roaringbitmap,roaringbitmapbool包含。roaringbitmap('{1,2,3}')f
integer,roaringbitmapbool包含。3t
&&roaringbitmap,roaringbitmapbool重叠。roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}')t
=roaringbitmap,roaringbitmapbool等于。roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}')f
<>roaringbitmap,roaringbitmapbool不等于。roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}')t

功能函数列表

函数输入输出描述示例结果
rb_buildinteger[]roaringbitmapCreate roaringbitmap from integer arrayrb_build('{1,2,3,4,5}'){1,2,3,4,5}
rb_indexroaringbitmap,integerbigintReturn the 0-based index of element in this roaringbitmap, or -1 if do not exsitsrb_index('{1,2,3}',3)2
rb_cardinalityroaringbitmapbigintReturn cardinality of the roaringbitmaprb_cardinality('{1,2,3,4,5}')5
rb_and_cardinalityroaringbitmap,roaringbitmapbigintReturn cardinality of the AND of two roaringbitmapsrb_and_cardinality('{1,2,3}',rb_build('{3,4,5}'))1
rb_or_cardinalityroaringbitmap,roaringbitmapbigintReturn cardinality of the OR of two roaringbitmapsrb_or_cardinality('{1,2,3}','{3,4,5}')5
rb_xor_cardinalityroaringbitmap,roaringbitmapbigintReturn cardinality of the XOR of two roaringbitmapsrb_xor_cardinality('{1,2,3}','{3,4,5}')4
rb_andnot_cardinalityroaringbitmap,roaringbitmapbigintReturn cardinality of the ANDNOT of two roaringbitmapsrb_andnot_cardinality('{1,2,3}','{3,4,5}')2
rb_is_emptyroaringbitmapbooleanCheck if roaringbitmap is empty.rb_is_empty('{1,2,3,4,5}')f
rb_fillroaringbitmap,range_start bigint,range_end bigintroaringbitmapFill the specified range (not include the range_end)rb_fill('{1,2,3}',5,7){1,2,3,5,6}
rb_clearroaringbitmap,range_start bigint,range_end bigintroaringbitmapClear the specified range (not include the range_end)rb_clear('{1,2,3}',2,3){1,3}
rb_fliproaringbitmap,range_start bigint,range_end bigintroaringbitmapNegative the specified range (not include the range_end)rb_flip('{1,2,3}',2,10){1,4,5,6,7,8,9}
rb_rangeroaringbitmap,range_start bigint,range_end bigintroaringbitmapReturn new set with specified range (not include the range_end)rb_range('{1,2,3}',2,3){2}
rb_range_cardinalityroaringbitmap,range_start bigint,range_end bigintbigintReturn the cardinality of specified range (not include the range_end)rb_range_cardinality('{1,2,3}',2,3)1
rb_minroaringbitmapintegerReturn the smallest offset in roaringbitmap. Return NULL if the bitmap is emptyrb_min('{1,2,3}')1
rb_maxroaringbitmapintegerReturn the greatest offset in roaringbitmap. Return NULL if the bitmap is emptyrb_max('{1,2,3}')3
rb_rankroaringbitmap,integerbigintReturn the number of elements that are smaller or equal to the specified offsetrb_rank('{1,2,3}',3)3
rb_jaccard_distroaringbitmap,roaringbitmapdouble precisionReturn the jaccard distance(or the Jaccard similarity coefficient) of two bitmapsrb_jaccard_dist('{1,2,3}','{3,4}')0.25
rb_selectroaringbitmap,bitset_limit bigint,bitset_offset bigint=0,reverse boolean=false,range_start bigint=0,range_end bigint=4294967296roaringbitmapReturn 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_arrayroaringbitmapinteger[]Convert roaringbitmap to integer arrayrb_to_array(roaringbitmap('{1,2,3}')){1,2,3}
rb_iterateroaringbitmapSET of integerReturn set of integer from a roaringbitmap data.SELECT rb_iterate (rb_build('{1,2,3}'))123

聚合函数列表

函数输入输出描述示例结果
rb_build_aggintegerroaringbitmapBuild a roaringbitmap from a integer setselect rb_build_agg(id) from (values (1),(2),(3)) t(id){1,2,3}
rb_or_aggroaringbitmaproaringbitmapAND Aggregate calculations from a roaringbitmap setselect rb_or_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap){1,2,3,4}
rb_and_aggroaringbitmaproaringbitmapAND Aggregate calculations from a roaringbitmap setselect rb_and_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap){2,3}
rb_xor_aggroaringbitmaproaringbitmapXOR Aggregate calculations from a roaringbitmap setselect rb_xor_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap){1,4}
rb_or_cardinality_aggroaringbitmapbigintOR 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_aggroaringbitmapbigintAND Aggregate calculations from a roaringbitmap set, return cardinalityselect rb_and_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)2
rb_xor_cardinality_aggroaringbitmapbigintXOR Aggregate calculations from a roaringbitmap set, return cardinalityselect rb_xor_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)2