Roaring bitmaps are compressed bitmaps that outperform traditional compressed bitmaps such as WAH, EWAH, and Concise. In some scenarios, roaring bitmaps can deliver excellent compression performance, and provide an indexing speed that is almost hundreds of times faster than that of traditional compressed bitmaps. The indexing speed is even faster than that of uncompressed bitmaps.

Create the pg_roaringbitmap plug-in

postgres=# CREATE EXTENSION if not exists roaringbitmap;
CREATE EXTENSION

Check the pg_roaringbitmap plug-in version

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)

Input and output formats

PolarDB supports only array and bytea input and output formats.

  • Input format
    • array:
      postgres=# select roaringbitmap('{1,100,10}');
                       roaringbitmap                  
      ------------------------------------------------
       \x3a30000001000000000002001000000001000a006400
      (1 row)
    • bytea:
      postgres=# select '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
                       roaringbitmap                  
      ------------------------------------------------
       \x3a30000001000000000002001000000001000a006400
      (1 row)
  • Output format
    Note The default output format is bytea. You can run the roaringbitmap.output_format command to change the 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 a table

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

Create a bitmap from an integer array

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;

Bitmap calculation functions

Bitmap calculation functions include OR, AND, XOR, and 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}');

Bitmap aggregate functions

Bitmap aggregate functions include OR, AND, XOR, and 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;

Bitmap cardinality calculation functions

SELECT rb_cardinality('{1,2,3}');

Convert a bitmap to an integer array

SELECT rb_to_array(bitmap) FROM t1 WHERE id = 1;

Convert a bitmap to a set of integers

SELECT unnest(rb_to_array('{1,2,3}'::roaringbitmap));

Or

SELECT rb_iterate('{1,2,3}'::roaringbitmap);

Operations

OperatorInputOutputDescriptionExampleResult
&roaringbitmap,roaringbitmaproaringbitmapThe bitwise AND operatorroaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}'){3}
|roaringbitmap,roaringbitmaproaringbitmapThe bitwise OR operatorroaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}'){1,2,3,4,5}
|roaringbitmap,integerroaringbitmapAdds an element to a roaring bitmaproaringbitmap('{1,2,3}') | 6{1,2,3,6}
|integer,roaringbitmaproaringbitmapAdds an element to a roaring bitmap6 | roaringbitmap('{1,2,3}'){1,2,3,6}
#roaringbitmap,roaringbitmaproaringbitmapThe bitwise exclusive OR operatorroaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}'){1,2,4,5}
<<roaringbitmap,bigintroaringbitmapBitwise left shiftroaringbitmap('{1,2,3}') << 2{0,1}
>>roaringbitmap,bigintroaringbitmapBitwise right shiftroaringbitmap('{1,2,3}') >> 3{4,5,6}
-roaringbitmap,roaringbitmaproaringbitmapThe offset operatorroaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}'){1,2}
-roaringbitmap,integerroaringbitmapRemoves an element from a roaring bitmaproaringbitmap('{1,2,3}') - 3{1,2}
@>roaringbitmap,roaringbitmapboolThe contains operatorroaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}')f
@>roaringbitmap,integerboolThe contains operatorroaringbitmap('{1,2,3,4,5}') @> 3t
roaringbitmap,roaringbitmapboolThe contains operatorroaringbitmap('{1,2,3}')f
integer,roaringbitmapboolThe contains operator3t
&&roaringbitmap,roaringbitmapboolThe logical AND operatorroaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}')t
=roaringbitmap,roaringbitmapboolThe equality operatorroaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}')f
<>roaringbitmap,roaringbitmapboolThe not equal operatorroaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}')t

Functionality functions

FunctionInputOutputDescriptionExampleResult
rb_buildinteger[]roaringbitmapCreate a roaring bitmap from an 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

Aggregate functions

FunctionInputOutputDescriptionExampleResult
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