PolarDB:pg_roaringbitmap

Install the plug-in

CREATE EXTENSION IF NOT EXISTS roaringbitmap;

Verify the installation:

\dx

Expected output:

                    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)

Quick start

The following walkthrough covers the core usage pattern: create a table, insert bitmap data, run bitmap operations, run aggregate queries, and iterate over results.

1. Create a table with a roaringbitmap column.

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

2. Insert bitmap data.

Build a bitmap from an explicit integer array:

INSERT INTO t1 SELECT 1, rb_build(ARRAY[1,2,3,4,5,6,7,8,9,200]);

Build a bitmap by aggregating a generated series:

INSERT INTO t1 SELECT 2, rb_build_agg(e) FROM generate_series(1,100) e;

3. Run bitmap operations.

All four binary operations are supported: OR, AND, XOR, and ANDNOT.

SELECT roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}');  -- OR:    {1,2,3,4,5}
SELECT roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}');  -- AND:   {3}
SELECT roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}');  -- XOR:   {1,2,4,5}
SELECT roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');  -- ANDNOT:{1,2}

4. Run aggregate queries.

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;

5. Count elements (cardinality).

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

6. Iterate over elements.

Convert to an integer array:

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

Return elements as a set:

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

Input and output formats

PolarDB supports only array and bytea input and output formats.

Input formats

• array:

  SELECT roaringbitmap('{1,100,10}');
  -- \x3a30000001000000000002001000000001000a006400

• bytea:

  SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
  -- \x3a30000001000000000002001000000001000a006400

Output format

The default output format is bytea. Change it with roaringbitmap.output_format:

SET roaringbitmap.output_format = 'bytea';
SELECT '{1}'::roaringbitmap;
-- \x3a3000000100000000000000100000000100

SET roaringbitmap.output_format = 'array';
SELECT '{1}'::roaringbitmap;
-- {1}

Operators

Functions