AI 助理
备案控制台
文档
输入文档关键字查找
首页 云原生数据库 PolarDB 云原生数据库PolarDB PostgreSQL版 插件指南 pg_roaringbitmap（压缩位图）

pg_roaringbitmap（压缩位图）

更新时间：
产品详情
我的收藏

RoaringBitmap是一种压缩位图，其性能优于WAH、EWAH、Concise等传统压缩位图。在特定场景下，RoaringBitmap可以在提供良好压缩性能的条件下，仍然具备快于传统压缩位图近百倍的索引性能，甚至性能可以超过未采用压缩的位图。

创建插件

CREATE EXTENSION if NOT EXISTS roaringbitmap;

创建插件成功后可通过以下SQL语句查看版本：

SELECT extname,extversion FROM pg_extension WHERE extname = 'roaringbitmap';

返回结果如下：

    extname    | extversion 
---------------+------------
 roaringbitmap | 0.5
(1 row)

输入和输出格式

PolarDB目前仅支持array和bytea两种输入输出格式。

  • 输入格式

    • array

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

    • bytea

      SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
                 roaringbitmap                  
------------------------------------------------
 \x3a30000001000000000002001000000001000a006400
(1 row)

  • 输出格式

    说明

    输出格式默认为bytea，您可以通过roaringbitmap.output_format修改输出格式。

    • array

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

    • bytea

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

使用说明

输入和输出格式

PolarDB目前仅支持array和bytea两种输入输出格式。

  • 输入格式

    • array

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

    • bytea

      SELECT '\x3a30000001000000000002001000000001000a006400'::roaringbitmap;
                 roaringbitmap                  
------------------------------------------------
 \x3a30000001000000000002001000000001000a006400
(1 row)

  • 输出格式

    说明

    输出格式默认为bytea，您可以通过roaringbitmap.output_format修改输出格式。

    • array

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

    • bytea

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

创建表

CREATE TABLE t1 (id integer, bitmap roaringbitmap);

整数集合创建位图

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;

位图计算函数

位图计算函数包含OR、AND、XOR、ANDNOT。

SELECT roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}');
SELECT roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}');

位图聚合函数

位图聚合函数包含OR、AND、XOR、BUILD。

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;

位图基数计算函数

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

位图转换整数集合

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

位图转换整数

支持以下两种方式将位图转换为整数。

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

操作列表

操作符

输入

输出

描述

示例

结果

&

roaringbitmap,roaringbitmap

roaringbitmap

位元且运算。

roaringbitmap('{1,2,3}') & roaringbitmap('{3,4,5}')

{3}

|

roaringbitmap,roaringbitmap

roaringbitmap

位元或运算。

roaringbitmap('{1,2,3}') | roaringbitmap('{3,4,5}')

{1,2,3,4,5}

|

roaringbitmap,integer

roaringbitmap

将元素添加到RoaringBitmap中。

roaringbitmap('{1,2,3}') | 6

{1,2,3,6}

|

integer,roaringbitmap

roaringbitmap

将元素添加到RoaringBitmap中。

6 | roaringbitmap('{1,2,3}')

{1,2,3,6}

#

roaringbitmap,roaringbitmap

roaringbitmap

位元互斥或运算。

roaringbitmap('{1,2,3}') # roaringbitmap('{3,4,5}')

{1,2,4,5}

<<

roaringbitmap,bigint

roaringbitmap

按位左移。

roaringbitmap('{1,2,3}') << 2

{0,1}

>>

roaringbitmap,bigint

roaringbitmap

按位右移。

roaringbitmap('{1,2,3}') >> 3

{4,5,6}

-

roaringbitmap,roaringbitmap

roaringbitmap

差异。

roaringbitmap('{1,2,3}') - roaringbitmap('{3,4,5}')

{1,2}

-

roaringbitmap,integer

roaringbitmap

从RoaringBitmap中删除元素。

roaringbitmap('{1,2,3}') - 3

{1,2}

@>

roaringbitmap,roaringbitmap

bool

包含。

roaringbitmap('{1,2,3}') @> roaringbitmap('{3,4,5}')

f

@>

roaringbitmap,integer

bool

包含。

roaringbitmap('{1,2,3,4,5}') @> 3

t

roaringbitmap,roaringbitmap

bool

包含。

roaringbitmap('{1,2,3}') @> 4

f

integer,roaringbitmap

bool

包含。

3 @> roaringbitmap('{1,2,3,4,5}')

t

&&

roaringbitmap,roaringbitmap

bool

重叠。

roaringbitmap('{1,2,3}') && roaringbitmap('{3,4,5}')

t

=

roaringbitmap,roaringbitmap

bool

等于。

roaringbitmap('{1,2,3}') = roaringbitmap('{3,4,5}')

f

<>

roaringbitmap,roaringbitmap

bool

不等于。

roaringbitmap('{1,2,3}') <> roaringbitmap('{3,4,5}')

t

功能函数列表

函数

输入

输出

描述

示例

结果

rb_build

integer[]

roaringbitmap

Create roaringbitmap from integer array

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

{1,2,3,4,5}

rb_index

roaringbitmap,integer

bigint

Return the 0-based index of element in this roaringbitmap, or -1 if do not exsits

rb_index('{1,2,3}',3)

2

rb_cardinality

roaringbitmap

bigint

Return cardinality of the roaringbitmap

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

5

rb_and_cardinality

roaringbitmap,roaringbitmap

bigint

Return cardinality of the AND of two roaringbitmaps

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

1

rb_or_cardinality

roaringbitmap,roaringbitmap

bigint

Return cardinality of the OR of two roaringbitmaps

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

5

rb_xor_cardinality

roaringbitmap,roaringbitmap

bigint

Return cardinality of the XOR of two roaringbitmaps

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

4

rb_andnot_cardinality

roaringbitmap,roaringbitmap

bigint

Return cardinality of the ANDNOT of two roaringbitmaps

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

2

rb_is_empty

roaringbitmap

boolean

Check if roaringbitmap is empty.

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

f

rb_fill

roaringbitmap,range_start bigint,range_end bigint

roaringbitmap

Fill the specified range (not include the range_end)

rb_fill('{1,2,3}',5,7)

{1,2,3,5,6}

rb_clear

roaringbitmap,range_start bigint,range_end bigint

roaringbitmap

Clear the specified range (not include the range_end)

rb_clear('{1,2,3}',2,3)

{1,3}

rb_flip

roaringbitmap,range_start bigint,range_end bigint

roaringbitmap

Negative the specified range (not include the range_end)

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

{1,4,5,6,7,8,9}

rb_range

roaringbitmap,range_start bigint,range_end bigint

roaringbitmap

Return new set with specified range (not include the range_end)

rb_range('{1,2,3}',2,3)

{2}

rb_range_cardinality

roaringbitmap,range_start bigint,range_end bigint

bigint

Return the cardinality of specified range (not include the range_end)

rb_range_cardinality('{1,2,3}',2,3)

1

rb_min

roaringbitmap

integer

Return the smallest offset in roaringbitmap. Return NULL if the bitmap is empty

rb_min('{1,2,3}')

1

rb_max

roaringbitmap

integer

Return the greatest offset in roaringbitmap. Return NULL if the bitmap is empty

rb_max('{1,2,3}')

3

rb_rank

roaringbitmap,integer

bigint

Return the number of elements that are smaller or equal to the specified offset

rb_rank('{1,2,3}',3)

3

rb_jaccard_dist

roaringbitmap,roaringbitmap

double precision

Return the jaccard distance(or the Jaccard similarity coefficient) of two bitmaps

rb_jaccard_dist('{1,2,3}','{3,4}')

0.25

rb_select

roaringbitmap,bitset_limit bigint,bitset_offset bigint=0,reverse boolean=false,range_start bigint=0,range_end bigint=4294967296

roaringbitmap

Return subset [bitset_offset,bitset_offset+bitset_limit) of bitmap between range [range_start,range_end)

rb_select('{1,2,3,4,5,6,7,8,9}',5,2)

{3,4,5,6,7}

rb_to_array

roaringbitmap

integer[]

Convert roaringbitmap to integer array

rb_to_array(roaringbitmap('{1,2,3}'))

{1,2,3}

rb_iterate

roaringbitmap

SET of integer

Return set of integer from a roaringbitmap data.

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

123

聚合函数列表

函数

输入

输出

描述

示例

结果

rb_build_agg

integer

roaringbitmap

Build a roaringbitmap from a integer set

select rb_build_agg(id) from (values (1),(2),(3)) t(id)

{1,2,3}

rb_or_agg

roaringbitmap

roaringbitmap

AND Aggregate calculations from a roaringbitmap set

select rb_or_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

{1,2,3,4}

rb_and_agg

roaringbitmap

roaringbitmap

AND Aggregate calculations from a roaringbitmap set

select rb_and_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

{2,3}

rb_xor_agg

roaringbitmap

roaringbitmap

XOR Aggregate calculations from a roaringbitmap set

select rb_xor_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

{1,4}

rb_or_cardinality_agg

roaringbitmap

bigint

OR Aggregate calculations from a roaringbitmap set, return cardinality.

select rb_or_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

4

rb_and_cardinality_agg

roaringbitmap

bigint

AND Aggregate calculations from a roaringbitmap set, return cardinality

select rb_and_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

2

rb_xor_cardinality_agg

roaringbitmap

bigint

XOR Aggregate calculations from a roaringbitmap set, return cardinality

select rb_xor_cardinality_agg(bitmap) from (values (roaringbitmap('{1,2,3}')), (roaringbitmap('{2,3,4}')) ) t(bitmap)

2

上一篇：pldebugger（调试存储过程）下一篇：高维向量检索（PASE）
文档推荐