CREATE INDEX ... USING HNSW. VECTOR_SEARCH is a table‑valued function that returns up to top_k rows from the indexed table that are closest to a target vector according to the distance metric configured on the index.
Read more about Vector Search Indexes in general and how to create vector indexes.
Syntax
Parameters
EF_SEARCH
Theef_search parameter defines the breadth of exploration during query‑time search.
- Higher values improve recall; latency increases approximately with
ef_search. - Set
ef_search >= top_k. While this is not strictly required, it is generally recommended to achieve good recall. - Typical range:
50–200; recommended starting range:64–100. - Tune per workload; you can override per query without rebuilding the index.
Return type
A rowset with the same columns as the indexed base table. Returns up totop_k rows.
The rowset also has a $approx_distance column of type DOUBLE PRECISION: the approximate distance between the row’s quantized embedding and the target vector, measured with the index’s distance metric.
Like other pseudo-columns, $approx_distance is not part of SELECT *; name it explicitly to read it.
The rows are not returned in any particular order, so use ORDER BY $approx_distance to sort them.
Smaller values mean closer rows for every distance metric. For
vector_l2sq_ops, the value is the squared Euclidean distance; use SQRT($approx_distance) to get the result of VECTOR_EUCLIDEAN_DISTANCE.
For vector_ip_ops, the value ranks rows but is not a true distance: it is negative whenever the inner product exceeds 1, so it has no lower bound. For unit-length embeddings it equals the cosine distance. Use 1 - $approx_distance to get the inner product, where larger values mean closer rows.
The index computes the distance on vectors stored with its quantization, so $approx_distance can differ slightly from the expression in the table. Rows from tablets without an index get the exact value of that expression. If the table has its own column named $approx_distance, that column is returned instead.
WHERE condition on $approx_distance filters the top_k rows after the search, so the result can have fewer than top_k rows.
Notes
- When using
load_strategy => 'in_memory'(the default), you might want to adjust your engine configuration to allow the in-memory vector index cache to use more memory usingVECTOR_INDEX_CACHE_MEMORY_FRACTION. Otherwise, vector indexes need to be loaded into memory in every query, which slows queries down. See Performance & observability for more information. - With
load_strategy => 'disk', the cache holds about 25 bytes per row of an index: its upper graph levels, row keys, and what tracks whether each vector and neighbor list is in memory. A search reads the vectors it visits from local disk into a buffer that sharesVECTOR_INDEX_CACHE_MEMORY_FRACTIONwith the cached indexes: it gets the memory they leave, and at least 100 MB, or half the fraction if that is smaller. A larger fraction keeps more vectors in memory between searches. - You must create and populate a vector search index before using
VECTOR_SEARCH. VECTOR_SEARCHuses the distance metric defined on the index (for example,vector_cosine_ops).- If the index was created on a populated table without reindexing, Firebolt combines results from indexed tablets with full scans of unindexed tablets. For best performance, run
VACUUM ( REINDEX = TRUE )on the table after creating the index. - To tune recall at query time, increase
ef_search.
Filtering
Filtering works best onPARTITION BY columns, and well on PRIMARY INDEX columns. With every filter_strategy, a condition on these columns skips the partitions and tablets that cannot match before anything is read or searched, which can rule out most of the data early. A condition only on PARTITION BY columns selects whole partitions, so it needs no further work. A condition on other columns is evaluated row by row on the data that remains.
filter_strategy chooses how a WHERE condition on the result is combined with the search:
'prefilter' pays off when the condition is selective. If most rows pass it, the nearest rows overall would mostly pass it too, and 'prefilter' only adds work: it evaluates the condition on every row of the data that can match, while 'postfilter' evaluates it only on the top_k rows the search returns. For such a condition on columns other than PARTITION BY columns, 'postfilter' can be faster, especially combined with over-searching.
'prefilter' and 'auto', some parts of the condition are still applied only after the search, as with 'postfilter', so the result can have fewer than top_k rows:
- A condition on
$approx_distance. - A condition containing a subquery, such as
id IN (SELECT id FROM allowed). - A nondeterministic condition, such as
random() < 0.1. - A condition that cannot be evaluated on the table’s rows alone, for example one that compares
VECTOR_SEARCHcolumns with columns of another table in a join.
'prefilter' and 'auto' evaluate the condition on the rows of all data that can match, not only on the rows the search returns, so an expression that raises an error for some row (for example, a division by zero) can fail the query even if that row is not among the nearest.
Over-searching with postfilter
With filter_strategy => 'postfilter', the condition removes rows from the top_k the search found, so fewer rows than you need may be left. To compensate, search for more rows than you need, then keep the nearest of the rows that pass the condition with ORDER BY $approx_distance LIMIT:
top_k so that, given the share of rows the condition keeps, enough of the nearest rows are expected to pass it: if about 10% of rows match, top_k => 100 leaves about 10. The result can still have fewer rows than the LIMIT. Raise ef_search along with top_k, since ef_search should be at least top_k. If the condition is selective, 'prefilter' is usually the better choice.
Reranking
$approx_distance is computed on the vectors as the index stores them, with its quantization, so the order of rows close to each other can differ from the order by exact distance, and a row that belongs in the nearest top_k can be missed. To rerank, search for more candidates than you need, compute the exact distance on the stored embedding with the distance function that matches the index’s metric, and keep the nearest rows by that distance:
top_k and ef_search give the reranking more candidates to choose from, at the cost of latency. Reranking combines with filtering and over-searching: add a WHERE condition to the query above.
Examples
Create a table with embeddings, create a vector search index, then runVECTOR_SEARCH() .