Create a hash index on a .vtr file column

Builds a persistent hash index stored as a .vtri sidecar file alongside the .vtr file. The index maps key hashes to row group indices, so an equality predicate (filter(col == value)) names the row groups that may hold a key without reading any column data.

Usage

create_index(path, column, ci = FALSE)

Arguments

path

Path to a .vtr file.

column

Character vector. Name(s) of column(s) to index.

ci

Logical. Build a case-insensitive index? Default FALSE.

Value

Invisible NULL. The index is written as a .vtri sidecar file.

Details

For composite indexes on multiple columns, pass a character vector. Composite indexes accelerate AND-combined equality predicates (e.g., filter(col1 == "a", col2 == "b")). The columns may be named in any order.

A query opens the index for the column it filters on, so a store can carry an index on each of several columns and a query pays only for the one it uses. explain() reports the index a scan will probe. The index composes with zone-map pruning and binary search on sorted columns.

The index holds one entry per distinct key per row group rather than one per row, so an index over a column with few distinct values stays small however many rows the store holds – which is what keeps a lookup off the size of the store.

Building one is bounded too: the entries are sorted through the streaming memory budget (vectra_mem()) and written in a single pass, so an index over a store larger than memory costs disk rather than RAM.

append_vtr() leaves the existing row groups where they are, so an index stays valid across an append: it takes in the row groups just appended and keeps the rest, reading only the new data rather than the whole store. write_vtr() replaces a store, and removes the indexes of the store it replaces.

An index records the store it was built on, by the store's row and row-group counts and a fingerprint of its bytes. An index left behind by any other change of the store – including a store replaced outside vectra by one of the same shape, such as a download or a file renamed over the old path – is reported as absent by has_index() and ignored by queries rather than pruning row groups that may now hold matching rows. The same goes for one that cannot be read at all: an index only ever saves a scan work, so an unusable one costs speed and never rows. Indexes written by vectra 0.12.3 and earlier are superseded and read as absent; call create_index() again to rebuild them.

A store written by vectra 0.12.3 or earlier carries no digest of its bytes, so its fingerprint covers its layout (row-group sizes, block sizes and column statistics) only. Such a store cannot be told apart from a replacement whose blocks agree with it on all of those; rewriting it with write_vtr() gives it a digest.

Examples

f <- tempfile(fileext = ".vtr")
write_vtr(data.frame(id = letters, val = 1:26, stringsAsFactors = FALSE), f)
create_index(f, "id")
tbl(f) |> filter(id == "m") |> collect()
unlink(c(f, paste0(f, ".id.vtri")))