BTree Index

BTree index builds a logical B-tree structure over SST files, enabling efficient point lookups and range queries on scalar columns. Use it for selective predicates on scalar columns such as identifiers, timestamps, categories with many distinct values, or other columns frequently used in WHERE clauses.

Supported predicate shapes include:

Predicate	Example
Equality	`name = 'a200'`
IN	`name IN ('a200', 'a300')`
Range	`price >= 10 AND price < 100`
Null checks	`name IS NOT NULL`
AND / OR combinations	`name = 'a200' OR name = 'a300'`

LIKE, startsWith, contains, and NOT IN predicates may still need broader index file reads. Use btree-index.fallback-scan-max-size to cap fallback range and string scans by the total candidate index file size. For keyword-style text retrieval, use Full-Text Index instead.

For table prerequisites, see Global Index. For refresh, coverage modes, and shared build options, see Manage Global Indexes.

Build BTree Index

Use the populated shared example table. It includes the indexed column and the dt partition key used in the examples below.

SQL
Python SDK

-- Create BTree index on 'name' column
CALL sys.create_global_index(
    table => 'db.my_table',
    index_column => 'name',
    index_type => 'btree'
);

You can build only selected partitions:

CALL sys.create_global_index(
    table => 'db.my_table',
    index_column => 'name',
    index_type => 'btree',
    partitions => 'dt=2026-06-18;dt=2026-06-19'
);

table = catalog.get_table("db.my_table")

# Create BTree index on 'name' column.
added_files = table.create_global_index("name", index_type="btree")
print(added_files)

The API returns the number of committed index files. You can pass BTree build options and restrict the build to selected partitions:

added_files = table.create_global_index(
    "name",
    index_type="btree",
    partitions=[{"dt": "2026-06-18"}, {"dt": "2026-06-19"}],
    options={"sorted-index.records-per-file": "25000000"},
)
print(added_files)

Query with BTree Index

Once a BTree index is built, it is automatically used during scan when a filter predicate matches the indexed column.

SQL
Python SDK

SELECT * FROM my_table WHERE name IN ('a200', 'a300');

from pypaimon.common.predicate_builder import PredicateBuilder

table = catalog.get_table("db.my_table")

read_builder = table.new_read_builder()
read_builder = read_builder.with_filter(
    PredicateBuilder(table.fields)
    .is_in("name", ["a200", "a300"])
)

scan = read_builder.new_scan()
read = read_builder.new_read()
pa_table = read.to_arrow(scan.plan().splits())
print(pa_table)

Composite BTree Indexes

Spark and Flink can create a composite index by listing columns in key order:

CALL sys.create_global_index(
    `table` => 'db.my_table',
    index_column => 'category,item_number',
    index_type => 'btree'
);

SELECT * FROM my_table
WHERE category = 'category-a'
  AND item_number = 107;

The index stores typed tuples and one row-ID posting list for each distinct tuple. Equality conditions on every key column use a composite point lookup, regardless of their order in the SQL predicate. This avoids expanding each column's posting list before intersecting them. Additional predicates remain row filters, and OR branches can each select a composite lookup.

Composite indexes also support a prefix restricted by equality, IN or IS NULL, followed by a range on the next key column. For an index on (category, item_number), the following conditions can use the index:

Condition	Index scan
`category = 'category-a'`	All tuples with this category prefix
`category = 'category-a' AND item_number > 7`	A range within the category prefix
`category = 'category-a' AND item_number BETWEEN 7 AND 9`	A range with inclusive endpoints within the prefix
`category > 'category-a' AND item_number = 7`	A category range; the item-number condition remains a data filter
`category IN ('category-a', 'category-b') AND item_number = 7`	A point probe for each category
`category IN ('category-a', 'category-b') AND item_number > 7`	A separate range within each category prefix
`category IS NULL AND item_number > 7`	A range within the NULL category prefix
`category = 'category-a' AND item_number IS NULL`	A point probe with a NULL second component
`category IS NOT NULL AND item_number = 7`	A non-NULL category range; the item-number condition remains a data filter

The range may use >, >=, <, <=, BETWEEN or IS NOT NULL. Conditions after the first range column remain data filters. A condition only on item_number cannot use this composite index's left prefix.

When several composite definitions match, selection prefers more bounded key columns, then more columns restricted by equality, IN or IS NULL, then fewer total key columns. If only the leading column is bounded, a dedicated single-column index is preferred when its coverage includes the composite definition's coverage.

Eligible composite queries honor global-index.query-in-reader.enabled. With reader evaluation enabled, supported index conditions narrow the candidate rows and remaining conditions are evaluated against the data.

Composite indexes can coexist with single-column indexes on the same columns. Restrictions by equality, IN or IS NULL on every key column use point probes. Repeated IN values are deduplicated, and NULL entries in IN lists do not match NULL keys. IS NULL explicitly selects NULL keys; equality to NULL still matches no rows. The planner expands at most 256 tuple intervals after deduplication and intersection of conditions on each column. Queries exceeding this limit fall back to other eligible index paths or an ordinary scan.

Prefix and range scans use btree-index.fallback-scan-max-size, applied to the selected files in each index group. If any group exceeds the budget, the whole composite definition is declined and planning uses other indexes or an ordinary scan. Point probes are unaffected by this budget. A file shared by several intervals is counted only once. Vector and full-text search pre-filters use single-column indexes or their data fallback.

In full and detail modes, rows outside the selected composite index's coverage remain eligible for data filtering. Calling the procedure again with the same ordered column list fills missing index ranges. Under the IGNORE column-update policy, updates to any key column invalidate the affected composite index ranges; calling the procedure again rebuilds those ranges. To drop the composite definition, pass the same ordered column list to sys.drop_global_index; independent single-column definitions remain in place.

BTree Options

Option	Default	Description
`sorted-index.records-per-file`	`25000000`	Expected number of records per sorted global index file for BTree, Bitmap, and Multivalue builds.
`sorted-index.build.max-parallelism`	`4096`	Maximum Flink or Spark parallelism for building sorted global indexes.
`btree-index.block-size`	`64 kb`	Block size used by BTree index files.
`btree-index.bloom-filter.enabled`	`false`	Whether to write a Bloom filter to accelerate BTree equality and `IN` lookups.
`btree-index.cache-size`	`128 mb`	Cache size used by BTree index readers.
`btree-index.high-priority-pool-ratio`	`0.1`	Fraction of `btree-index.cache-size` reserved for high-priority data such as index blocks; the rest caches data blocks. Must be in `[0, 1)`.
`btree-index.fallback-scan-max-size`	`256 mb`	Maximum total size of candidate BTree global index files to allow fallback index scans. Set to `0 b` to disable fallback scans.
`btree-index.compression`	`none`	Compression algorithm used by BTree index blocks.
`btree-index.compression-level`	`1`	Compression level used by codecs that support levels, such as `zstd`.

The legacy btree-index.records-per-range and btree-index.build.max-parallelism keys are still recognized as fallback keys.

Drop BTree Index

SQL
Python SDK

CALL sys.drop_global_index(
    table => 'db.my_table',
    index_column => 'name',
    index_type => 'btree'
);

table = catalog.get_table("db.my_table")

dropped_files = table.drop_global_index("name", index_type="btree")
print(dropped_files)

You can also restrict the drop to selected partitions, or count matched files without committing:

matched_files = table.drop_global_index(
    "name",
    index_type="btree",
    partitions=[{"dt": "2026-06-18"}, {"dt": "2026-06-19"}],
    dry_run=True,
)
print(matched_files)

Build BTree Index​

Query with BTree Index​

Composite BTree Indexes​

BTree Options​

Drop BTree Index​

Build BTree Index

Query with BTree Index

Composite BTree Indexes

BTree Options

Drop BTree Index