This topic describes how to use the roaringbitmap extension provided by ApsaraDB RDS for PostgreSQL to improve query performance.

Prerequisites

The RDS instance runs PostgreSQL 12 or later.

Background information

In a roaring bitmap, 32-bit integers are divided into 216 chunks. The integers in each chunk share the same 16 most significant bits. The 16 least significant bits of integers are stored in a container. A roaring bitmap stores containers in a dynamic array as primary indexes. Two types of containers are available: array containers for sparse chunks and bitmap containers for dense chunks. An array container can store up to 4,096 integers. A bitmap container can store more than 4,096 integers.

Roaring bitmaps can use this storage structure to rapidly retrieve specific values. Additionally, roaring bitmaps provide bitwise operations such as AND, OR, and XOR between the two types of containers. Therefore, roaring bitmaps can deliver excellent storage and computing performance.

Usage notes

To ensure extension stability, we recommend that you update your RDS instance to the latest minor engine version.
Note For more information about how to update the minor engine version of an RDS instance, see Update the minor engine version of an ApsaraDB RDS for MySQL instance.

Procedure

  1. Create the extension. Example:
    CREATE EXTENSION roaringbitmap;
  2. Create a table that is used to store roaringbitmap data. Example:
    CREATE TABLE t1 (id integer, bitmap roaringbitmap);
  3. Call the rb_build function to insert roaringbitmap data. Example:
    -- Set the bit value of an array to 1.
    INSERT INTO t1 SELECT 1,RB_BUILD(ARRAY[1,2,3,4,5,6,7,8,9,200]);
    -- Set the bit values of multiple elements to 1 and aggregate the bit values into a roaring bitmap.  
    INSERT INTO t1 SELECT 2,RB_BUILD_AGG(e) FROM GENERATE_SERIES(1,100) e;
  4. Perform bitmap operations such as OR, AND, XOR, and ANDNOT. Example:
    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;
  5. Perform bitmap aggregate operations such as OR, AND, XOR, and BUILD to generate a new roaring bitmap. Example:
    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;
  6. Calculate the cardinality of the roaring bitmap. The cardinality is the number of bits that are set to 1 in the roaring bitmap. Example:
    SELECT RB_CARDINALITY(bitmap) FROM t1;
  7. Obtain the subscripts of the bits that are set to 1. Example:
    SELECT RB_ITERATE(bitmap) FROM t1 WHERE id = 1;

Bitmap calculation functions

FunctionInputOutputDescriptionExample
rb_buildinteger[]roaringbitmapCreates a roaring bitmap from an integer array.
rb_build('{1,2,3,4,5}')
rb_androaringbitmap,roaringbitmaproaringbitmapPerforms an AND operation.
rb_and(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_orroaringbitmap,roaringbitmaproaringbitmapPerforms an OR operation.
rb_or(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_xorroaringbitmap,roaringbitmaproaringbitmapPerforms an XOR operation.
rb_xor(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnotroaringbitmap,roaringbitmaproaringbitmapPerforms an ANDNOT operation.
rb_andnot(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_cardinalityroaringbitmapintegerCalculates the cardinality.
rb_cardinality(rb_build('{1,2,3,4,5}'))
rb_and_cardinalityroaringbitmap,roaringbitmapintegerCalculates the cardinality from an AND operation on two roaring bitmaps.
rb_and_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_or_cardinalityroaringbitmap,roaringbitmapintegerCalculates the cardinality from an OR operation on two roaring bitmaps.
rb_or_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_xor_cardinalityroaringbitmap,roaringbitmapintegerCalculates the cardinality from an XOR operation on two roaring bitmaps.
rb_xor_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_andnot_cardinalityroaringbitmap,roaringbitmapintegerCalculates the cardinality from an ANDNOT operation on two roaring bitmaps.
rb_andnot_cardinality(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_is_emptyroaringbitmapbooleanChecks whether a roaring bitmap is empty.
rb_is_empty(rb_build('{1,2,3,4,5}'))
rb_equalsroaringbitmap,roaringbitmapbooleanChecks whether two roaring bitmaps are the same.
rb_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_intersectroaringbitmap,roaringbitmapbooleanChecks whether two roaring bitmaps intersect.
rb_intersect(rb_build('{1,2,3}'),rb_build('{3,4,5}'))
rb_removeroaringbitmap,integerroaringbitmapRemoves a specific offset from a roaring bitmap.
rb_remove(rb_build('{1,2,3}'),3)
rb_fliproaringbitmap,integer,integerroaringbitmapFlips specific offsets in a roaring bitmap.
rb_flip(rb_build('{1,2,3}'),2,3)
rb_minimumroaringbitmapintegerReturns the smallest offset in a roaring bitmap. If the roaring bitmap is empty, -1 is returned.
rb_minimum(rb_build('{1,2,3}'))
rb_maximumroaringbitmapintegerReturns the largest offset in a roaring bitmap. If the roaring bitmap is empty, 0 is returned.
rb_maximum(rb_build('{1,2,3}'))
rb_rankroaringbitmap,integerintegerReturns the number of elements that are smaller than or equal to a specified offset in a roaring bitmap.
rb_rank(rb_build('{1,2,3}'),3)
rb_iterateroaringbitmapsetof integerReturns a list of offsets from a roaring bitmap.
rb_iterate(rb_build('{1,2,3}'))

Bitmap aggregate functions

FunctionInputOutputDescriptionExample
rb_build_aggintegerroaringbitmapCreates a roaring bitmap from a group of offsets.
rb_build_agg(1)
rb_or_aggroaringbitmaproaringbitmapPerforms an OR aggregate operation.
rb_or_agg(rb_build('{1,2,3}'))
rb_and_aggroaringbitmaproaringbitmapPerforms an AND aggregate operation.
rb_and_agg(rb_build('{1,2,3}'))
rb_xor_aggroaringbitmaproaringbitmapPerforms an XOR aggregate operation.
rb_xor_agg(rb_build('{1,2,3}'))
rb_or_cardinality_aggroaringbitmapintegerCalculates the cardinality from an OR aggregate operation on two roaring bitmaps.
rb_or_cardinality_agg(rb_build('{1,2,3}'))
rb_and_cardinality_aggroaringbitmapintegerCalculates the cardinality from an AND aggregate operation on two roaring bitmaps.
rb_and_cardinality_agg(rb_build('{1,2,3}'))
rb_xor_cardinality_aggroaringbitmapintegerCalculates the cardinality from an XOR aggregate operation on two roaring bitmaps.
rb_xor_cardinality_agg(rb_build('{1,2,3}'))