# pg_roaringbitmap RoaringBitmap extension for PostgreSQL. It's initial based on https://github.com/zeromax007/gpdb-roaringbitmap. # Introduction Roaring bitmaps are compressed bitmaps which tend to outperform conventional compressed bitmaps such as WAH, EWAH or Concise. In some instances, roaring bitmaps can be hundreds of times faster and they often offer significantly better compression. They can even be faster than uncompressed bitmaps. More information https://github.com/RoaringBitmap/CRoaring . # Build ## Requirements - PostgreSQL(only tested on PostgreSQL 10) - Other Requirements from https://github.com/RoaringBitmap/CRoaring ## Build su - postgres make sudo make install psql -c "create extension roaringbitmap" ## Test make installcheck # Usage ## about roaringbitmap data type Logically, you could think of roaringbitmap data type as `bit(4294967296)`, and it should be noted that the integers added to bitmaps is considered to be unsigned. Within bitmaps, numbers are ordered according to uint32. We order the numbers like 0, 1, ..., 2147483647, -2147483648, -2147483647,..., -1. But we use bigint to reference the range of these integers, that is [0 4294967296). ## input and ouput The input/output syntax for roaringbitmap similar to int array. postgres=# select roaringbitmap('{1,100,10}'); roaringbitmap --------------- {1,10,100} (1 row) or postgres=# select '{1,100,10}'::roaringbitmap; roaringbitmap --------------- {1,10,100} (1 row) ## Create table CREATE TABLE t1 (id integer, bitmap roaringbitmap); ## Build bitmap 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 (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}'); ## Bitmap Aggregate (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; ## Cardinality SELECT rb_cardinality(rb_build('{1,2,3}')); ## Convert bitmap to integer array SELECT rb_to_array(bitmap) FROM t1 WHERE id = 1; ## Opperator List
Opperator Input Output Desc Example Result
& roraingbitmap,roaringbitmap roaringbitmap bitwise AND roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}') {3}
| roraingbitmap,roaringbitmap roaringbitmap bitwise OR roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}') {1,2,3,4,5}
| roraingbitmap,integer roaringbitmap add element to roaringbitmap roaringbitmap('{1,2,3}') | 6 {1,2,3,6}
| integer,roraingbitmap roaringbitmap add element to roaringbitmap 6 | roaringbitmap('{1,2,3}') {1,2,3,6}
# roraingbitmap,roaringbitmap roaringbitmap bitwise XOR roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}') {1,2,4,5}
<< roraingbitmap,bigint roaringbitmap bitwise shift left roaringbitmap('{1,2,3}') << 2 {0,1}
>> roraingbitmap,bigint roaringbitmap bitwise shift right roaringbitmap('{1,2,3}') >> 3 {4,5,6}
- roraingbitmap,roaringbitmap roaringbitmap difference(bitwise ANDNOT) roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}') {1,2}
- roraingbitmap,integer roaringbitmap remove element from roaringbitmap roaringbitmap('{1,2,3}') - 3 {1,2}
@> roraingbitmap,roaringbitmap bool contains roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}') f
@> roraingbitmap,integer bool contains roaringbitmap('{1,2,3,4,5}') @> 3 t
<@ roraingbitmap,roaringbitmap bool is contained by roaringbitmap('{1,2,3}') <@ roaringbitmap('{3,4,5}') f
<@ integer,roaringbitmap bool is contained by 3 <@ roaringbitmap('{3,4,5}') t
&& roraingbitmap,roaringbitmap bool overlap (have elements in common) roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}') t
= roraingbitmap,roaringbitmap bool equal roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}') f
<> roraingbitmap,roaringbitmap bool not equal roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}') t
## Function List
Function Input Output Desc Example Result
rb_build integer[] roaringbitmap Create roaringbitmap from integer array rb_build('{1,2,3,4,5}') {1,2,3,4,5}
rb_index roraingbitmap,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 roraingbitmap bigint Return cardinality of the roaringbitmap rb_cardinality('{1,2,3,4,5}') 5
rb_and_cardinality roraingbitmap,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 roraingbitmap,roaringbitmap bigint Return cardinality of the OR of two roaringbitmaps rb_or_cardinality('{1,2,3}','{3,4,5}') 1
rb_xor_cardinality roraingbitmap,roaringbitmap bigint Return cardinality of the XOR of two roaringbitmaps rb_xor_cardinality('{1,2,3}','{3,4,5}') 4
rb_andnot_cardinality roraingbitmap,roaringbitmap bigint Return cardinality of the ANDNOT of two roaringbitmaps rb_andnot_cardinality('{1,2,3}','{3,4,5}') 2
rb_is_empty roraingbitmap boolean Check if roaringbitmap is empty. rb_is_empty('{1,2,3,4,5}') t
rb_fill roraingbitmap,range_start bigint,range_end bigint roraingbitmap Fill the specified range (not include the range_end) rb_fill('{1,2,3}',5,7) {1,2,3,5,6}
rb_clear roraingbitmap,range_start bigint,range_end bigint roraingbitmap Clear the specified range (not include the range_end) rb_clear('{1,2,3}',2,3) {1,3}
rb_flip roraingbitmap,range_start bigint,range_end bigint roraingbitmap 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 roraingbitmap,range_start bigint,range_end bigint roraingbitmap Return new set with specified range (not include the range_end) rb_range('{1,2,3}',2,3) {2}
rb_range_cardinality roraingbitmap,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 roraingbitmap integer Return the smallest offset in roaringbitmap. Return NULL if the bitmap is empty rb_min('{1,2,3}') 1
rb_max roraingbitmap integer Return the greatest offset in roaringbitmap. Return NULL if the bitmap is empty rb_max('{1,2,3}') 3
rb_rank roraingbitmap,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 roraingbitmap,roraingbitmap 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 roraingbitmap,bitset_limit bigint,bitset_offset bigint=0,reverse boolean=false,range_start bigint=0,range_end bigint=4294967296 roraingbitmap 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 roraingbitmap integer[] Convert roaringbitmap to integer array rb_to_array(roaringbitmap('{1,2,3}')) {1,2,3}
## Aggregation List
Function Input Output Desc Example Result
rb_build_agg integer roraingbitmap 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 roraingbitmap roraingbitmap AND Aggregate calculations from a roraingbitmap 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 roraingbitmap roraingbitmap AND Aggregate calculations from a roraingbitmap set 
select rb_and_agg(bitmap) 
    from (values (roaringbitmap('{1,2,3}')),
                 (roaringbitmap('{2,3,4}'))
          ) t(bitmap)
 {2,3}
rb_xor_agg roraingbitmap roraingbitmap XOR Aggregate calculations from a roraingbitmap set 
select rb_xor_agg(bitmap) 
    from (values (roaringbitmap('{1,2,3}')),
                 (roaringbitmap('{2,3,4}'))
          ) t(bitmap)
 {1,4}
rb_or_cardinality_agg roraingbitmap bigint OR Aggregate calculations from a roraingbitmap 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 roraingbitmap bigint AND Aggregate calculations from a roraingbitmap 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 roraingbitmap bigint XOR Aggregate calculations from a roraingbitmap set, return cardinality. 
select rb_xor_cardinality_agg(bitmap) 
    from (values (roaringbitmap('{1,2,3}')),
                 (roaringbitmap('{2,3,4}'))
          ) t(bitmap)
 2