All Products
Search

This Product

    ApsaraDB RDS:Use the roaringbitmap extension

    Document Center

    ApsaraDB RDS:Use the roaringbitmap extension

    Last Updated:Dec 04, 2024

    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.

    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

    Function

    Input

    Output

    Description

    Example

    rb_build

    integer[]

    roaringbitmap

    Creates a roaring bitmap from an integer array. 

    rb_build('{1,2,3,4,5}')

    rb_and

    roaringbitmap,roaringbitmap

    roaringbitmap

    Performs an AND operation. 

    rb_and(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_or

    roaringbitmap,roaringbitmap

    roaringbitmap

    Performs an OR operation. 

    rb_or(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_xor

    roaringbitmap,roaringbitmap

    roaringbitmap

    Performs an XOR operation. 

    rb_xor(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_andnot

    roaringbitmap,roaringbitmap

    roaringbitmap

    Performs an ANDNOT operation. 

    rb_andnot(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_cardinality

    roaringbitmap

    integer

    Calculates the cardinality. 

    rb_cardinality(rb_build('{1,2,3,4,5}'))

    rb_and_cardinality

    roaringbitmap,roaringbitmap

    integer

    Calculates 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_cardinality

    roaringbitmap,roaringbitmap

    integer

    Calculates 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_cardinality

    roaringbitmap,roaringbitmap

    integer

    Calculates 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_cardinality

    roaringbitmap,roaringbitmap

    integer

    Calculates 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_empty

    roaringbitmap

    boolean

    Checks whether a roaring bitmap is empty. 

    rb_is_empty(rb_build('{1,2,3,4,5}'))

    rb_equals

    roaringbitmap,roaringbitmap

    boolean

    Checks whether two roaring bitmaps are the same. 

    rb_equals(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_intersect

    roaringbitmap,roaringbitmap

    boolean

    Checks whether two roaring bitmaps intersect. 

    rb_intersect(rb_build('{1,2,3}'),rb_build('{3,4,5}'))

    rb_remove

    roaringbitmap,integer

    roaringbitmap

    Removes a specific offset from a roaring bitmap. 

    rb_remove(rb_build('{1,2,3}'),3)

    rb_flip

    roaringbitmap,integer,integer

    roaringbitmap

    Flips specific offsets in a roaring bitmap. 

    rb_flip(rb_build('{1,2,3}'),2,3)

    rb_minimum

    roaringbitmap

    integer

    Returns the smallest offset in a roaring bitmap. If the roaring bitmap is empty, -1 is returned. 

    rb_minimum(rb_build('{1,2,3}'))

    rb_maximum

    roaringbitmap

    integer

    Returns the largest offset in a roaring bitmap. If the roaring bitmap is empty, 0 is returned. 

    rb_maximum(rb_build('{1,2,3}'))

    rb_rank

    roaringbitmap,integer

    integer

    Returns 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_iterate

    roaringbitmap

    setof integer

    Returns a list of offsets from a roaring bitmap. 

    rb_iterate(rb_build('{1,2,3}'))

    Bitmap aggregate functions

    Function

    Input

    Output

    Description

    Example

    rb_build_agg

    integer

    roaringbitmap

    Creates a roaring bitmap from a group of offsets. 

    rb_build_agg(1)

    rb_or_agg

    roaringbitmap

    roaringbitmap

    Performs an OR aggregate operation. 

    rb_or_agg(rb_build('{1,2,3}'))

    rb_and_agg

    roaringbitmap

    roaringbitmap

    Performs an AND aggregate operation. 

    rb_and_agg(rb_build('{1,2,3}'))

    rb_xor_agg

    roaringbitmap

    roaringbitmap

    Performs an XOR aggregate operation. 

    rb_xor_agg(rb_build('{1,2,3}'))

    rb_or_cardinality_agg

    roaringbitmap

    integer

    Calculates the cardinality from an OR aggregate operation on two roaring bitmaps. 

    rb_or_cardinality_agg(rb_build('{1,2,3}'))

    rb_and_cardinality_agg

    roaringbitmap

    integer

    Calculates the cardinality from an AND aggregate operation on two roaring bitmaps. 

    rb_and_cardinality_agg(rb_build('{1,2,3}'))

    rb_xor_cardinality_agg

    roaringbitmap

    integer

    Calculates the cardinality from an XOR aggregate operation on two roaring bitmaps. 

    rb_xor_cardinality_agg(rb_build('{1,2,3}'))