tencent cloud

TencentDB for PostgreSQL provides the pg_roaringbitmap extension to use the bitwise operation feature to improve the query performance.

Prerequisites

Your TencentDB for PostgreSQL instance is on v10, 11, 12, or 13.

Background

The roaring bitmap algorithm divides a 32-bit INT value into 216 data chunks, each of which corresponds to the higher 16 bits of an integer and uses a container to store the lower 16 bits.
Roaring bitmap stores the containers in a dynamic array as a level-1 index. Containers are in two different structures: array container for sparse chunks and bitmap container for dense chunks. If a container has less than 4,096 integers, the values are stored in an array container; otherwise, the values are stored in a bitmap container.
By using this storage structure, roaring bitmap can quickly search for a specific value. During bitwise operations (AND, OR, and XOR), roaring bitmap provides the corresponding algorithms to efficiently implement operations between two containers, making it powerful in both storage and computing performance.

Directions

1. Run the following command to create an extension:

   CREATE EXTENSION roaringbitmap;
   

2. Run the following command to create a table with data of roaringbitmap type:

   CREATE TABLE t1 (id integer, bitmap roaringbitmap);
   

3. Run the following command to use the rb_build function to insert the roaringbitmap data:

   -- Set the bit value of the 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 records 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. Run the following command to perform bitwise operations (OR, AND, XOR, and ANDNOT):

   -- Set the bit value of the array to 1.
   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. Run the following command to perform aggregated bitwise operations (OR, AND, XOR, and BUILD) to generate a new Roaring bitmap:

   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. Run the following command to calculate the cardinality, i.e., number of bits set to 1 in the Roaring bitmap:

   SELECT RB_CARDINALITY(bitmap) FROM t1;
   

7. Run the following command to return the subscripts of the bits set to 1 in the Roaring bitmap:

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

Feature Function List

Aggregate Function List