RoaringBitmap extension for greenplum-db
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.
Roaringbitmap是一种高效的Bitmap压缩算法，目前已被广泛应用在各种语言和各种大数据平台上。
本插件将Roaringbitmap功能集成到Greenplum数据库中，将Roaringbitmap作为一种数据类型提供原生的数据库函数、操作符、聚合等功能支持。
Bitmap位计算非常适合大数据基数计算，常用于去重、标签筛选、时间序列等计算中。
su - gpadmin
make
make install
Make sure all nodes are copied the extension files.
确保插件文件同步到所有节点所对应的目录下。
psql -c "create extension roaringbitmap;"
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;
SELECT RB_OR(a.bitmap,b.bitmap) FROM (SELECT bitmap FROM t1 WHERE id = 1) AS a,(SELECT bitmap FROM t1 WHERE id = 2) AS b;
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(bitmap) FROM t1;
SELECT RB_ITERATE(bitmap) FROM t1 WHERE id = 1;
SELECT RB_ITERATE_DECREMENT(bitmap) FROM t1 WHERE id = 1;
SELECT RB_BUILD('{1,2,3}')::BYTEA;
SELECT '\x3a3000000100000000000000100000000100'::ROARINGBITMAP;
| Function | Input | Output | Desc | Example |
|---|---|---|---|---|
| rb_build | integer[] | roaringbitmap |
Build a roaringbitmap from integer array. 通过数组创建一个Bitmap。 |
rb_build('{1,2,3,4,5}') |
| rb_build | integer integer integer |
roaringbitmap | Build a roaringbitmap from integer range (with step). | rb_build('{1,2,3,4,5}') |
| rb_to_array | roaringbitmap | integer[] |
Bitmap to integer array. Bitmap转数组。 |
rb_to_array(rb_build('{1,2,3,4,5}')) |
| rb_and | roraingbitmap roaringbitmap |
roaringbitmap | Two roaringbitmap and calculation. And计算。 |
rb_and(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_or | roraingbitmap roaringbitmap |
roaringbitmap | Two roaringbitmap or calculation. Or计算。 |
rb_or(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_xor | roraingbitmap roaringbitmap |
roaringbitmap | Two roaringbitmap xor calculation. Xor计算。 |
rb_xor(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_andnot | roraingbitmap roaringbitmap |
roaringbitmap | Two roaringbitmap andnot calculation. AndNot计算 |
rb_andnot(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_cardinality | roraingbitmap | bigint | Retrun roaringbitmap cardinality. 统计基数 |
rb_cardinality(rb_build('{1,2,3,4,5}')) |
| rb_cardinality | roraingbitmap integer integer |
bigint | Retrun roaringbitmap cardinality between integer range. | rb_cardinality(rb_build('{1,2,3,4,5}'),1,4) |
| rb_cardinality | roraingbitmap integer integer integer |
bigint | Retrun roaringbitmap cardinality between integer range with step. | rb_cardinality(rb_build('{1,2,3,4,5}'),1,4,2) |
| rb_cardinality | roraingbitmap integer integer integer integer integer |
bigint | Retrun roaringbitmap cardinality between integer range with step, in offset range. | rb_cardinality(rb_build('{1,2,3,4,5}'),1,4,2,1,3) |
| rb_cardinality | roraingbitmap integer integer integer integer[] |
bigint | Retrun roaringbitmap cardinality between integer range with step, in offset array. | rb_cardinality(rb_build('{1,2,3,4,5}'),1,4,2,'{1,3}') |
| rb_and_cardinality | roraingbitmap roaringbitmap |
bigint | Two roaringbitmap and calculation, return cardinality. And计算并返回基数。 |
rb_and_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_or_cardinality | roraingbitmap roaringbitmap |
bigint | Two roaringbitmap or calculation, return cardinality. Or计算并返回基数。 |
rb_or_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_xor_cardinality | roraingbitmap roaringbitmap |
bigint | Two roaringbitmap xor calculation, return cardinality. Xor计算并返回基数。 |
rb_xor_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_andnot_cardinality | roraingbitmap roaringbitmap |
bigint | Two roaringbitmap andnot calculation, return cardinality. AndNot计算并返回基数。 |
rb_andnot_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_is_empty | roraingbitmap | boolean | Check if roaringbitmap is empty. 判断是否为空的Bitmap。 |
rb_is_empty(rb_build('{1,2,3,4,5}')) |
| rb_equals | roraingbitmap roaringbitmap |
boolean | Check two roaringbitmap are equal. 判断两个Bitmap是否相等。 |
rb_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_not_equals | roraingbitmap roaringbitmap |
boolean | Check two roaringbitmap are not equal. 判断两个Bitmap是否不同。 |
rb_not_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_intersect | roraingbitmap roaringbitmap |
boolean | Check two roaringbitmap are intersect. 判断两个Bitmap是否相交。 |
rb_intersect(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_contains | roraingbitmap roaringbitmap |
boolean | Check roaringbitmap conatins another one. 判断Bitmap是否包含另外一个。 |
rb_contains(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_contains | roraingbitmap integer |
boolean | Check roaringbitmap conatins a specific offset. 判断Bitmap是否包含特定的Offset。 |
rb_contains(rb_build('{1,2,3}'),1) |
| rb_contains | roraingbitmap integer integer |
boolean | Check roaringbitmap conatins a specific offsets range. 判断Bitmap是否包含特定的Offset段。 |
rb_contains(rb_build('{1,2,3}'),1,3) |
| rb_becontained | roraingbitmap roaringbitmap |
boolean | Check roaringbitmap is contained by another one. 判断Bitmap是否被另外一个包含。 |
rb_becontained(rb_build('{1,2,3}'),rb_build('{3,4,5}')) |
| rb_becontained | integer roaringbitmap |
boolean | Check a specific offset is contained by Bitmap. 判断特定的Offset是否被Bitmap包含。 |
rb_becontained(1,rb_build('{3,4,5}')) |
| rb_add | roraingbitmap integer |
roraingbitmap | Add a specific offset to roaringbitmap. 添加特定的Offset到Bitmap。 |
rb_add(rb_build('{1,2,3}'),3) |
| rb_add | roraingbitmap integer integer |
roraingbitmap | Add a specific offsets range to roaringbitmap. 添加特定的Offset段到Bitmap。 |
rb_add(rb_build('{1,2,3}'),3,4) |
| rb_remove | roraingbitmap integer |
roraingbitmap | Remove a specific offset from roaringbitmap. 从Bitmap移除特定的Offset。 |
rb_remove(rb_build('{1,2,3}'),3) |
| rb_remove | roraingbitmap integer integer |
roraingbitmap | Remove a specific offsets rang from roaringbitmap. 从Bitmap移除特定的Offset段。 |
rb_remove(rb_build('{1,2,3}'),2,3) |
| rb_flip | roraingbitmap integer |
roraingbitmap | Flip a specific offset from roaringbitmap. 翻转Bitmap中特定的Offset。 |
rb_flip(rb_build('{1,2,3}'),3) |
| rb_flip | roraingbitmap integer integer |
roraingbitmap | Flip a specific offsets range from roaringbitmap. 翻转Bitmap中特定的Offset段。 |
rb_flip(rb_build('{1,2,3}'),2,3) |
| rb_minimum | roraingbitmap | integer | Return the smallest offset in roaringbitmap. Return -1 if the bitmap is empty. 返回Bitmap中最小的Offset，如果Bitmap为空则返回-1。 | rb_minimum(rb_build('{1,2,3}')) |
| rb_maximum | roraingbitmap | integer | Return the greatest offset in roaringbitmap. Return 0 if the bitmap is empty. 返回Bitmap中最大的Offset，如果Bitmap为空则返回0。 |
rb_maximum(rb_build('{1,2,3}')) |
| rb_rank | roraingbitmap integer |
integer | Return the number of offsets that are smaller or equal to a specific offset. 返回Bitmap中小于等于指定Offset的基数。 |
rb_rank(rb_build('{1,2,3}'),3) |
| rb_jaccard_index | roraingbitmap roraingbitmap |
float8 | Computes the Jaccard index between two bitmaps. 计算两个Bitmap之间的jaccard相似系数。 |
rb_jaccard_index(rb_build('{1,2,3}'),rb_build('{1,2,3，4}')) |
| rb_iterate | roraingbitmap | setof integer | Return offsets List in increasing orders. 返回Offset List （从小到大）。 |
rb_iterate(rb_build('{1,2,3}')) |
| rb_iterate_decrement | roraingbitmap | setof integer | Return offsets List in decreasing orders. 返回Offset List（从大到小）。 |
rb_iterate_decrement(rb_build('{1,2,3}')) |
| Operator | Left | Right | Output | Desc | Example |
|---|---|---|---|---|---|
| & | roraingbitmap | roraingbitmap | roraingbitmap | Two roaringbitmap and calculation. 两个Bitmap And 操作。 |
rb_build('{1,2,3}') & rb_build('{1,2,3}') |
| | | roraingbitmap | roraingbitmap | roraingbitmap | Two roaringbitmap or calculation. 两个Bitmap Or 操作。 |
rb_build('{1,2,3}') | rb_build('{1,2,3}') |
| # | roraingbitmap | roraingbitmap | roraingbitmap | Two roaringbitmap xor calculation. 两个Bitmap Xor 操作。 |
rb_build('{1,2,3}') # rb_build('{1,2,3}') |
| ~ | roraingbitmap | roraingbitmap | roraingbitmap | Two roaringbitmap andnot calculation. 两个Bitmap Andnot 操作。 |
rb_build('{1,2,3}') ~ rb_build('{1,2,3}') |
| + | roraingbitmap | intger | roraingbitmap | Add a specific offset from roaringbitmap. 向Bitmap中添加特定的Offset。 |
rb_build('{1,2,3}') + 4 |
| + | intger | roraingbitmap | roraingbitmap | Add a specific offset from roaringbitmap. 向Bitmap添加特定的Offset。 |
4 + rb_build('{1,2,3}') |
| - | roraingbitmap | intger | roraingbitmap | Remove a specific offset from roaringbitmap. 从Bitmap移除特定的Offset。 |
rb_build('{1,2,3}') - 1 |
| = | roraingbitmap | roraingbitmap | boolean | Check two roaringbitmap are equal. 判断两个Bitmap是否相等。 |
rb_build('{1,2,3}') = rb_build('{3,2,1}') |
| <> | roraingbitmap | roraingbitmap | boolean | Check two roaringbitmap are not equal. 判断两个Bitmap是否不相等。 |
rb_build('{1,2,3}') <> rb_build('{3,2,1}') |
| && | roraingbitmap | roraingbitmap | boolean | Check two roaringbitmaps are intersected. 判断两个Bitmap是否相交。 |
rb_build('{1,2,3}') && rb_build('{3,2,1}') |
| @> | roraingbitmap | roraingbitmap | boolean | Check roaringbitmap conatins another one. 判断Bitmap是否包含另外一个 |
rb_build('{1,2,3}') @> rb_build('{3,1}') |
| @> | roraingbitmap | integer | boolean | Check roaringbitmap conatins a specific offset. 判断Bitmap是否包含特定的Offset。 |
rb_build('{1,2,3}') @> 1 |
| <@ | roraingbitmap | roraingbitmap | boolean | Check roaringbitmap is contained by another one. 判断Bitmap是否被另外一个包含。 |
rb_build('{1,3}') <@ rb_build('{3,2,1}') |
| <@ | integer | roraingbitmap | boolean | Check a specific offset is contained by Bitmap. 判断特定的Offset是否被Bitmap包含。 |
1 <@ rb_build('{1,2,3}') |
| Aggregation | Input | Output | Desc | Example |
|---|---|---|---|---|
| rb_build_agg | integer | roraingbitmap | Build a roaringbitmap from a integer set. 将Offset聚合成bitmap。 |
rb_build_agg(1) |
| rb_or_agg | roraingbitmap | roraingbitmap | Or Aggregate calculations from a roraingbitmap set. Or 聚合计算。 |
rb_or_agg(rb_build('{1,2,3}')) |
| rb_and_agg | roraingbitmap | roraingbitmap | And Aggregate calculations from a roraingbitmap set. And 聚合计算。 |
rb_and_agg(rb_build('{1,2,3}')) |
| rb_xor_agg | roraingbitmap | roraingbitmap | Xor Aggregate calculations from a roraingbitmap set. Xor 聚合计算。 |
rb_xor_agg(rb_build('{1,2,3}')) |
| rb_or_cardinality_agg | roraingbitmap | bigint | Or Aggregate calculations from a roraingbitmap set, return cardinality. Or 聚合计算并返回其基数。 |
rb_or_cardinality_agg(rb_build('{1,2,3}')) |
| rb_and_cardinality_agg | roraingbitmap | bigint | And Aggregate calculations from a roraingbitmap set, return cardinality. And 聚合计算并返回其基数。 |
rb_and_cardinality_agg(rb_build('{1,2,3}')) |
| rb_xor_cardinality_agg | roraingbitmap | bigint | Xor Aggregate calculations from a roraingbitmap set, return cardinality. Xor 聚合计算并返回其基数。 |
rb_xor_cardinality_agg(rb_build('{1,2,3}')) |
