MogDB&openGauss中的Bitmap Index Scan_opengauss bitmap-CSDN博客

本文链接：https://blog.csdn.net/robinson1988/article/details/138399557

本文对MogDB 5.0.6数据库进行测试，涉及test01、test02表结构和索引结构。分析了四种SQL场景下Bitmap Index的性能，指出场景一、二用Bitmap Index Scan可能性能高，场景三可用组合索引代替BitmapAnd，场景四需具体分析。还提及limit对Bitmap Index Scan性能的影响及相关参数设置。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试数据库版本MogDB5.0.6

orcl=> select version();
                                                                       version                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------
 (MogDB 5.0.6 build 8b0a6ca8) compiled at 2024-03-27 11:05:29 commit 0 last mr 1804  on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

测试表test01，test02表结构和索引结构

orcl=> \d test01
                    Table "scott.test01"
     Column     |              Type              | Modifiers 
----------------+--------------------------------+-----------
 owner          | character varying(30)          | 
 object_name    | character varying(128)         | 
 subobject_name | character varying(30)          | 
 object_id      | numeric                        | 
 data_object_id | numeric                        | 
 object_type    | character varying(19)          | 
 created        | timestamp(0) without time zone | 
 last_ddl_time  | timestamp(0) without time zone | 
 timestamp      | character varying(19)          | 
 status         | character varying(7)           | 
 temporary      | character varying(1)           | 
 generated      | character varying(1)           | 
 secondary      | character varying(1)           | 
 namespace      | numeric                        | 
 edition_name   | character varying(30)          | 
Indexes:
    "idx_test01_name" btree (object_name) TABLESPACE pg_default
    "idx_test01_objectid" btree (object_id) TABLESPACE pg_default
    "idx_test01_objectype" btree (object_type) TABLESPACE pg_default
    "idx_test01_owner" btree (owner) TABLESPACE pg_default

orcl=> \d test02
                    Table "scott.test02"
     Column     |              Type              | Modifiers 
----------------+--------------------------------+-----------
 owner          | character varying(30)          | 
 object_name    | character varying(128)         | 
 subobject_name | character varying(30)          | 
 object_id      | numeric                        | 
 data_object_id | numeric                        | 
 object_type    | character varying(19)          | 
 created        | timestamp(0) without time zone | 
 last_ddl_time  | timestamp(0) without time zone | 
 timestamp      | character varying(19)          | 
 status         | character varying(7)           | 
 temporary      | character varying(1)           | 
 generated      | character varying(1)           | 
 secondary      | character varying(1)           | 
 namespace      | numeric                        | 
 edition_name   | character varying(30)          | 
Indexes:
    "idx_test02_objectype" btree (object_type) TABLESPACE pg_default
    "idx_test02_owner" btree (owner) TABLESPACE pg_default

数据量

orcl=> select count(*) from test01;
  count   
----------
 44537344
(1 row)

orcl=> select count(*) from test02;
 count 
-------
 86987
(1 row)

场景一：

where (col1,col2) in ((),(),())

orcl=> explain analyze select * from test01 where (object_id,owner)