Hologres:Bitmap index

How it works

When you set bitmap_columns on a column, Hologres generates one binary string per distinct value in that column. When a query includes an equality predicate (=) on that column, the query engine uses the binary string to locate matching row numbers directly, skipping unrelated stored files.

This design involves a storage trade-off:

• Low-cardinality columns (few distinct values, such as gender or status) produce compact binary strings and benefit most from bitmap indexes.

• High-cardinality columns (many distinct values) generate one binary string per distinct value, producing a sparse array that consumes significant storage with minimal query benefit.

• Wide tables with bitmap indexes on every column trigger binary string generation for every column on each write, adding overhead that degrades write throughput.

When to use bitmap indexes

Use bitmap indexes on columns that meet both conditions:

• The column appears in equality predicates (=) in frequent queries.

• The column has low cardinality (many duplicate values, such as gender, class, or status).

Avoid bitmap indexes in these situations:

All TEXT columns in a column-oriented table have bitmap indexes enabled by default.

Syntax

CREATE TABLE <table_name> (...)
WITH (bitmap_columns = '[<columnName>{:[on|off]}[,...]]');

BEGIN;
CREATE TABLE <table_name> (...);
CALL set_table_property('<table_name>', 'bitmap_columns', '[<columnName>{:[on|off]}[,...]]');
COMMIT;

-- Disable the bitmap index for column a (affects all columns in the table)
ALTER TABLE tbl SET (bitmap_columns = 'a:off');

-- Modify bitmap_columns for all columns: disable column a, keep column b
CALL set_table_property('tbl', 'bitmap_columns', 'a:off');

-- Modify bitmap_columns for a single column: disable column b, retain others
CALL update_table_property('tbl', 'bitmap_columns', 'b:off');

Limitations

• Bitmap indexes are supported on column-oriented tables and row-column hybrid tables only. Row-oriented tables do not support bitmap indexes.

• bitmap_columns values can be null.

• Bitmap index changes run outside of a transaction block and take effect asynchronously. For details, see ALTER TABLE.

Verify that a query uses a bitmap index

Use EXPLAIN to check whether the query engine uses a bitmap index. If the execution plan contains the Bitmap Filter operator, the query hits the bitmap index. If Bitmap Filter does not appear, the optimizer did not use the bitmap index — check whether the queried column has bitmap_columns set to on and whether the query uses an equality predicate.

Example — Hologres V2.1 and later:

CREATE TABLE bitmap_test (
    uid    int  NOT NULL,
    name   text NOT NULL,
    gender text NOT NULL,
    class  text NOT NULL,
    PRIMARY KEY (uid)
)
WITH (
    bitmap_columns = 'gender,class'
);

INSERT INTO bitmap_test VALUES
    (1, 'Bob',   'Male',   'Class 1'),
    (2, 'Eric',  'Male',   'Class 3'),
    (3, 'Ada',   'Female', 'Class 2'),
    (4, 'Joyce', 'Female', 'Class 2'),
    (5, 'Leo',   'Male',   'Class 2'),
    (6, 'Steve', 'Male',   'Class 3'),
    (7, 'Dora',  'Female', 'Class 1');

EXPLAIN SELECT * FROM bitmap_test WHERE gender = 'Male' AND class = 'Class 1';

Example — all Hologres versions:

BEGIN;
CREATE TABLE bitmap_test (
    uid    int  NOT NULL,
    name   text NOT NULL,
    gender text NOT NULL,
    class  text NOT NULL,
    PRIMARY KEY (uid)
);
CALL set_table_property('bitmap_test', 'bitmap_columns', 'gender,class');
COMMIT;

INSERT INTO bitmap_test VALUES
    (1, 'Bob',   'Male',   'Class 1'),
    (2, 'Eric',  'Male',   'Class 3'),
    (3, 'Ada',   'Female', 'Class 2'),
    (4, 'Joyce', 'Female', 'Class 2'),
    (5, 'Leo',   'Male',   'Class 2'),
    (6, 'Steve', 'Male',   'Class 3'),
    (7, 'Dora',  'Female', 'Class 1');

EXPLAIN SELECT * FROM bitmap_test WHERE gender = 'Male' AND class = 'Class 1';

The Bitmap Filter operator in the execution plan confirms the index is used.

Bitmap indexes and clustering keys

Both bitmap indexes and clustering keys help filter data in stored files, but they work differently and suit different query patterns.

When both are set on the same column, the clustering key takes precedence for queries that follow the leftmost matching principle. Bitmap indexes apply when the clustering key cannot be used.

Example: A table with clustering_key = 'uid,class,date' and bitmap_columns = 'name,class,date'.

-- Hologres V2.1 and later
CREATE TABLE ck_bit_test (
    uid   int  NOT NULL,
    name  text NOT NULL,
    class text NOT NULL,
    date  text NOT NULL,
    PRIMARY KEY (uid)
)
WITH (
    clustering_key = 'uid,class,date',
    bitmap_columns = 'name,class,date'
);

-- All Hologres versions
BEGIN;
CREATE TABLE ck_bit_test (
    uid   int  NOT NULL,
    name  text NOT NULL,
    class text NOT NULL,
    date  text NOT NULL,
    PRIMARY KEY (uid)
);
CALL set_table_property('ck_bit_test', 'clustering_key', 'uid,class,date');
CALL set_table_property('ck_bit_test', 'bitmap_columns', 'name,class,date');
COMMIT;

Scenario 1 — query follows the leftmost matching principle: The query hits the clustering key on uid, class, and date columns following the leftmost matching principle. The clustering key is used exclusively, even though date uses a range predicate.

SELECT * FROM clustering_test WHERE uid = '3' AND class = '2' AND date > '2022-10-17';

The execution plan contains Cluster Filter only — the clustering key handles the entire query.

Scenario 2 — range query breaks the leftmost matching principle: When class uses a range predicate, the leftmost matching principle stops at class. The date column cannot use the clustering key, so the bitmap index on date is used instead.

SELECT * FROM clustering_test WHERE uid = '3' AND class > '2' AND date = '2022-10-17';

The execution plan contains both Cluster Filter (for uid and class) and Bitmap Filter (for date).

What's next

• CREATE TABLE

• CREATE TABLE AS

• CREATE TABLE LIKE

• ALTER TABLE

• DROP TABLE