Bloom skip index quickstart

Column-oriented (OLAP) table: bloom_filter

Below is a minimal example: a column-oriented table with a primary key and a local bloom_filter index on a column that is frequently used in filters.

CREATE TABLE events (
    id Uint64 NOT NULL,
    resource_id Utf8 NOT NULL,
    payload String,
    message Utf8,
    PRIMARY KEY (id),
    INDEX idx_res LOCAL USING bloom_filter
        ON (resource_id)
        WITH (false_positive_probability = 0.01)
)
WITH (
    STORE = COLUMN
);

Extending the example: n-gram index

You can add bloom_ngram_filter on a string column to the same table from the example above (column-oriented tables):

ALTER TABLE events
  ADD INDEX idx_msg LOCAL USING bloom_ngram_filter
  ON (message)
  WITH (
    ngram_size = 3,
    false_positive_probability = 0.01,
    case_sensitive = true
  );

Queries and the effect

After you load data, selective queries that filter on indexed columns may read less data: while scanning storage, the Bloom skip index skips fragments that cannot contain the requested value (compared to reading the full column without this filter).

Sample data and queries for the column-oriented table above:

INSERT INTO events (id, resource_id, payload, message) VALUES
    (1, "res-1", "{}", "started"),
    (2, "res-42", "{}", "error: timeout"),
    (3, "res-2", "{}", "done");

Equality on the column protected by bloom_filter — the engine can prune irrelevant fragments when reading resource_id and related columns:

SELECT id, message
FROM events
WHERE resource_id = "res-42";

Substring search on the column with bloom_ngram_filter — the n-gram index helps drop fragments that cannot contain matching n-grams in message:

SELECT id, message
FROM events
WHERE message LIKE '%timeout%';

Row-oriented (OLTP) table: prefix bloom filter

On a row-oriented table, a bloom_filter index is built over a left prefix of the primary key. The indexed columns must be a contiguous leading subset of the primary key columns:

CREATE TABLE orders (
    customer_id Utf8 NOT NULL,
    order_id Utf8 NOT NULL,
    amount Decimal(10,2),
    PRIMARY KEY (customer_id, order_id),
    -- Prefix bloom filter on the first key column
    INDEX idx_customer LOCAL USING bloom_filter
        ON (customer_id)
        WITH (false_positive_probability = 0.001),
    -- Prefix bloom filter on the full primary key
    INDEX idx_full_key LOCAL USING bloom_filter
        ON (customer_id, order_id)
);

Point lookups and range scans that constrain the leading key columns can skip irrelevant data fragments. A query that filters on customer_id only uses the prefix bloom filter idx_customer:

SELECT amount FROM orders WHERE customer_id = "cust-42";

A query that filters on the full primary key uses idx_full_key:

SELECT amount FROM orders WHERE customer_id = "cust-42" AND order_id = "ord-1001";

How to verify index effectiveness

To check that the Bloom skip index actually helps, run the same selective query on a table with enough data before and after creating the index and compare execution time.

Further reading:

Previous
Local indexes
Next
min_max index — quick start