gpdb-roaringbitmap

RoaringBitmap extension for greenplum-db

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

If $GPHOME is /usr/local/gpdb .

gcc -march=native -O3 -std=c11 -Wall -Wpointer-arith  -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fno-aggressive-loop-optimizations -Wno-unused-but-set-variable -Wno-address -fpic -D_GNU_SOURCE -I/usr/local/gpdb/include/postgresql/server -I/usr/local/gpdb/include/postgresql/internal -c -o roaringbitmap.o roaringbitmap.c

gcc -O3 -std=gnu99 -Wall -Wpointer-arith  -Wendif-labels -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fno-aggressive-loop-optimizations -Wno-unused-but-set-variable -Wno-address  -fpic -shared --enable-new-dtags -o roaringbitmap.so roaringbitmap.o

cp ./roaringbitmap.so /usr/local/gpdb/lib/postgresql/

psql -f ./roaringbitmap.sql

Usage

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 RB_OR(a.bitmap,b.bitmap) FORM (SELECT bitmap FROM t1 WHERE id = 1) AS a,(SELECT bitmap FROM t1 WHERE id = 2) AS b;

Bitmap Aggregate (OR, AND, XOR, BUILD)

SELECT RB_OR_AGG(bitmap) FROM t1;
SELECT RB_AND_AGG(bitmap) FORM t1;
SELECT RB_XOR_AGG(bitmap) FROM t1;
SELECT RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;

Cardinality

SELECT RB_CARDINALITY(bitmap) FROM t1;

Bitmap to SETOF integer

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

Function List

Function	Input	Output	Desc	Example
rb_build	integer[]	roaringbitmap	Build a roaringbitmap from integer array.	rb_build('{1,2,3,4,5}')
rb_add	roaringbitmap, integer[]	roaringbitmap	add integer array into roaringbitmap.	rb_add(rb_build('{1,2,3,4,5}'), array[1,2,3])
rb_and	roraingbitmap,roaringbitmap	roaringbitmap	Two roaringbitmap and calculation.	rb_and(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_or	roraingbitmap,roaringbitmap	roaringbitmap	Two roaringbitmap or calculation.	rb_or(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_xor	roraingbitmap,roaringbitmap	roaringbitmap	Two roaringbitmap xor calculation.	rb_xor(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnot	roraingbitmap,roaringbitmap	roaringbitmap	Two roaringbitmap andnot calculation.	rb_andnot(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_cardinality	roraingbitmap	integer	Retrun roaringbitmap cardinality.	rb_cardinality(rb_build('{1,2,3,4,5}'))
rb_and_cardinality	roraingbitmap,roaringbitmap	integer	Two roaringbitmap and calculation, return cardinality.	rb_and_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_or_cardinality	roraingbitmap,roaringbitmap	integer	Two roaringbitmap or calculation, return cardinality.	rb_or_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_xor_cardinality	roraingbitmap,roaringbitmap	integer	Two roaringbitmap xor calculation, return cardinality.	rb_xor_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnot_cardinality	roraingbitmap,roaringbitmap	integer	Two roaringbitmap andnot calculation, return cardinality.	rb_andnot_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_is_empty	roraingbitmap	boolean	Check if roaringbitmap is empty.	rb_is_empty(rb_build('{1,2,3,4,5}'))
rb_equals	roraingbitmap,roaringbitmap	boolean	Check two roaringbitmap are equal.	rb_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_intersect	roraingbitmap,roaringbitmap	boolean	Check two roaringbitmap are intersect.	rb_intersect(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_remove	roraingbitmap,integer	roraingbitmap	Remove the specified offset from roaringbitmap.	rb_remove(rb_build('{1,2,3}'),3)
rb_flip	roraingbitmap,integer,integer	roraingbitmap	Flip the specified offsets range (not include the end) from roaringbitmap.	rb_flip(rb_build('{1,2,3}'),2,3)
rb_minimum	roraingbitmap	integer	Return the smallest offset in roaringbitmap. Return UINT32_MAX if the bitmap is empty.	rb_minimum(rb_build('{1,2,3}'))
rb_maximum	roraingbitmap	integer	Return the greatest offset in roaringbitmap. Return 0 if the bitmap is empty.	rb_maximum(rb_build('{1,2,3}'))
rb_rank	roraingbitmap,integer	integer	Return the number of offsets that are smaller or equal to the specified offset.	rb_rank(rb_build('{1,2,3}'),3)
rb_is_setid	roraingbitmap,integer	bool	Return the special offset id is or not set.	rb_is_setid(rb_build('{1,2,3}'),3)

Aggregation List

Function	Input	Output	Desc	Example
rb_build_agg	integer	roraingbitmap	Build a roaringbitmap from a integer set.	rb_build_agg(1)
rb_or_agg	roraingbitmap	roraingbitmap	Or Aggregate calculations from a roraingbitmap set.	rb_or_agg(rb_build('{1,2,3}'))
rb_and_agg	roraingbitmap	roraingbitmap	And Aggregate calculations from a roraingbitmap set.	rb_and_agg(rb_build('{1,2,3}'))
rb_xor_agg	roraingbitmap	roraingbitmap	Xor Aggregate calculations from a roraingbitmap set.	rb_xor_agg(rb_build('{1,2,3}'))
rb_or_cardinality_agg	roraingbitmap	integer	Or Aggregate calculations from a roraingbitmap set, return cardinality.	rb_or_cardinality_agg(rb_build('{1,2,3}'))
rb_and_cardinality_agg	roraingbitmap	integer	And Aggregate calculations from a roraingbitmap set, return cardinality.	rb_and_cardinality_agg(rb_build('{1,2,3}'))
rb_xor_cardinality_agg	roraingbitmap	integer	Xor Aggregate calculations from a roraingbitmap set, return cardinality.	rb_xor_cardinality_agg(rb_build('{1,2,3}'))