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 extension
CREATE EXTENSION if NOT EXISTS roaringbitmap;Execute the following statement to view the version of the extension:
SELECT extname,extversion FROM pg_extension WHERE extname = 'roaringbitmap';Sample result:
extname | extversion
---------------+------------
roaringbitmap | 0.5
(1 row)Input and output formats
PolarDB supports only array and bytea input and output formats.
Input format
array:
SELECT roaringbitmap('{1,100,10}'); roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)bytea:
SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap; roaringbitmap ------------------------------------------------ \x3a30000001000000000002001000000001000a006400 (1 row)
Output format
NoteThe default output format is
bytea. You can run theroaringbitmap.output_formatcommand to change the 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 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 function
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
Operator | Input | Output | Description | Example | Result |
& | roaringbitmap,roaringbitmap | roaringbitmap | The bitwise AND operator | roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}') | {3} |
| | roaringbitmap,roaringbitmap | roaringbitmap | The bitwise OR operator | roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}') | {1,2,3,4,5} |
| | roaringbitmap,integer | roaringbitmap | Adds an element to a roaring bitmap | roaringbitmap('{1,2,3}') | 6 | {1,2,3,6} |
| | integer,roaringbitmap | roaringbitmap | Adds an element to a roaring bitmap | 6 | roaringbitmap('{1,2,3}') | {1,2,3,6} |
# | roaringbitmap,roaringbitmap | roaringbitmap | The bitwise exclusive OR operator | roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}') | {1,2,4,5} |
<< | roaringbitmap,bigint | roaringbitmap | Bitwise left shift | roaringbitmap('{1,2,3}') << 2 | {0,1} |
>> | roaringbitmap,bigint | roaringbitmap | Bitwise right shift | roaringbitmap('{1,2,3}') >> 3 | {4,5,6} |
- | roaringbitmap,roaringbitmap | roaringbitmap | The offset operator | roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}') | {1,2} |
- | roaringbitmap,integer | roaringbitmap | Removes an element from a roaring bitmap | roaringbitmap('{1,2,3}') - 3 | {1,2} |
@> | roaringbitmap,roaringbitmap | bool | The contains operator | roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}') | f |
@> | roaringbitmap,integer | bool | The contains operator | roaringbitmap('{1,2,3,4,5}') @> 3 | t |
roaringbitmap,roaringbitmap | bool | The contains operator | roaringbitmap('{1,2,3}') @> 4 | f | |
integer,roaringbitmap | bool | The contains operator | 3 @> roaringbitmap('{1,2,3,4,5}') | t | |
&& | roaringbitmap,roaringbitmap | bool | The logical AND operator | roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}') | t |
= | roaringbitmap,roaringbitmap | bool | The equality operator | roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}') | f |
<> | roaringbitmap,roaringbitmap | bool | The not equal operator | roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}') | t |
Functionality functions
Function | Input | Output | Description | Example | Result |
rb_build | integer[] | roaringbitmap | Create a roaring bitmap from an 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 |
Aggregate functions
Function | Input | Output | Description | Example | Result |
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 |