---
metadata:
  - name: generator
    content: Diplodoc Platform v5.56.0
alternate:
  - https://ydb.tech/docs/en/recipes/bloom-skip-indexes/bloom-skip-index-quickstart.md?version=main
  - https://ydb.tech/docs/ru/recipes/bloom-skip-indexes/bloom-skip-index-quickstart.md?version=main
  - href: en/recipes/bloom-skip-indexes/bloom-skip-index-quickstart.md
    type: text/markdown
    title: Markdown version
  - href: ../../llms.txt
    type: text/markdown
    title: llms.txt
sourcePath: en/core/recipes/bloom-skip-indexes/bloom-skip-index-quickstart.md
---
> **Documentation Index:** Fetch the complete configuration index at https://ydb.tech/docs/en/llms.txt

# Bloom skip index quickstart

## Column-oriented (OLAP) table: bloom_filter

Below is a minimal example: a [column-oriented table](https://ydb.tech/docs/en/concepts/glossary.md?version=main#column-oriented-table) with a primary key and a local `bloom_filter` index on a column that is frequently used in filters.

```yql
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):

```yql
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:

```yql
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:

```yql
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`:

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

## Row-oriented (OLTP) table: prefix bloom filter

On a [row-oriented table](https://ydb.tech/docs/en/concepts/glossary.md?version=main#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:

```yql
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`:

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

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

```yql
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:

* Details and limitations: [Bloom skip indexes](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main)
* Parameter tuning: [Parameter tuning](https://ydb.tech/docs/en/dev/bloom-skip-indexes.md?version=main#tuning)
* Full syntax: [ALTER TABLE ADD INDEX](https://ydb.tech/docs/en/yql/reference/syntax/alter_table/indexes.md?version=main#local-bloom)