IVF-Flat

UDTF: cuvs_ivf_flat

Official cuVS reference: C API

Query-local inverted-file approximate nearest-neighbor search.

Quickstart

The call below expects the registered relations dataset_vectors (the dataset role) and query_vectors (the queries role), each passed as a parenthesized SELECT subquery. Substitute your own relations and column names.

SELECT *
FROM cuvs_ivf_flat(
  dataset => (SELECT id, d0, d1 FROM dataset_vectors),
  queries => (SELECT id, d0, d1 FROM query_vectors),
  k => 8,
  metric => 'l2_expanded',
  n_lists => 16,
  n_probes => 16
)
ORDER BY query_ordinal, rank;

Inputs

Each relation argument is a parenthesized SELECT subquery that the planner keeps as a real child; metadata validation resolves a registered table or view for the same role instead. See Vector Inputs for the relation identity rules and the ID, dense-vector type, null, finite-value, and runtime-dimension contract.

Role	Required	Validation reference	Description
dataset	yes	table	Dense-vector rows indexed for nearest-neighbor search.
queries	yes	table	Dense-vector query rows matched against the evaluated dataset.

Vector element types

Element type	Valid metrics
Float32	l2_expanded, l2_sqrt_expanded, cosine, inner_product
Int8	l2_expanded, l2_sqrt_expanded, cosine, inner_product
UInt8	l2_expanded, l2_sqrt_expanded, cosine, inner_product

Arguments and options

Scalar SQL arguments

Argument	Type	Required	Description
k	integer	yes	Number of neighbors returned for each evaluated query row.
metric	enum ("l2_expanded", "l2_sqrt_expanded", "cosine", "inner_product")	no	Distance or score metric; native default l2_expanded. Distance metrics rank lower values first; inner product ranks higher values first.
n_lists	integer	yes	Number of inverted lists; must not exceed the evaluated dataset row count.
n_probes	integer	no	Lists probed per query; native default 20, and must not exceed n_lists.
kmeans_n_iters	integer	no	Iterations for training list centers; native default 20.
kmeans_trainset_fraction	number	no	Fraction of the dataset used for training centers; native default 0.5.

SQL value argument schemas

Argument	Required	Literal shape	Default	Constraints	Description
k	yes	integer	No default	minimum 1; maximum 4294967295	Number of neighbors returned for each evaluated query row.
kmeans_n_iters	no	integer	20	minimum 1; maximum 4294967295	Iterations for training list centers; native default 20.
kmeans_trainset_fraction	no	number	0.5	greater than 0; maximum 1	Fraction of the dataset used for training centers; native default 0.5.
metric	no	string	"l2_expanded"	one of "l2_expanded", "l2_sqrt_expanded", "cosine", "inner_product"	Distance or score metric; native default l2_expanded. Distance metrics rank lower values first; inner product ranks higher values first. Supported element/metric combinations: Float32: l2_expanded, l2_sqrt_expanded, cosine, inner_product; Int8: l2_expanded, l2_sqrt_expanded, cosine, inner_product; UInt8: l2_expanded, l2_sqrt_expanded, cosine, inner_product.
n_lists	yes	integer	No default	minimum 1; maximum 4294967295	Number of inverted lists; must not exceed the evaluated dataset row count.
n_probes	no	integer	20	minimum 1; maximum 4294967295	Lists probed per query; native default 20, and must not exceed n_lists.

Vector binding shapes

Each relation subquery must project a non-null id field followed by either one or more non-null feature fields of a supported element type (Float32, Int8, UInt8) or one non-null list vector field named vector.

For wide vectors, the projection order defines the feature dimensions. A list vector relation must contain no feature field beside id and vector.

Output

Column	Type	Nullable	Description
query_ordinal	UInt64	no	Zero-based ordinal of the evaluated query row; it disambiguates duplicate query IDs.
query_id	same_as_queries.id	no	Logical ID copied from the queries relation.
neighbor_ordinal	UInt64	no	Zero-based ordinal of the matched dataset row; it disambiguates duplicate dataset IDs.
neighbor_id	same_as_dataset.id	no	Logical ID copied from the matched dataset row.
rank	UInt32	no	One-based neighbor rank within a query. Order consumers explicitly by query_ordinal, rank.
distance	Float32	no	Metric value; smaller is better for distance metrics, while inner_product prefers larger values.

Concrete schemas are call-specific. Run gpu_validate_call against registered relations to inspect the output schema after the actual ID types and literal options are validated.

Examples

This example uses the EMBER2024 demo dataset.

Which earlier Win32 files make useful references for a new quarter?

An analyst reviewing a new Win32 file can retrieve structurally similar files from an earlier period and inspect their historical labels and families. The search corpus contains 15,588 sampled train files from weeks 0-51; the held-out test period contains 3,618 sampled files from weeks 52-63. Both use the same one-percent SHA-256 sample and 512 normalized byte and byte-entropy histogram features. The train and test SHA-256 sets are disjoint. The search UDTFs receive only IDs and histogram vectors. Labels, family names, week IDs, and SHA-256 values are joined after search for review. The query retrieves references; it does not classify malware.

Method	ID recall@10 vs CPU oracle	Known-family match among top 10	Three-run median whole SQL time	Dedicated r3 query ID
Brute-force exact	0.9990	35.39%	0.876 s	73f3ec94-7127-4b7f-95a7-6a949ceeaa98
CAGRA	0.9968	35.37%	1.319 s	c0f13481-511d-4600-815e-b43ab2685418
IVF-Flat	0.9954	35.33%	1.005 s	02f1af3f-270f-4a22-8559-dee13bd59478

The family rates measure agreement with the available family labels. For the 1,575 test queries with a known family, 35.39% of brute-force top-10 reference slots had the same known family; references with no family label count as nonmatches. A uniform random training reference gives a 2.76% expected same-family rate. The search input does not use those labels or families.

For test file 10021055 from week 52, the exact result's three nearest references are from weeks 9, 16, then 10. Their family labels appear only after the search join:

Rank	Reference ID	Train week	Family	Squared-L2 distance
1	935830	9	berbew	0.0053381994
2	1663154	16	berbew	0.0058313087
3	1032030	10	berbew	0.0058773160

At this cohort size brute force had the lowest median whole-statement time. Each time includes per-statement index construction and fetching all 36,180 result rows. The captures ran on the dedicated EMBER server. They are not a controlled performance study, so the timings do not support an ANN speedup claim.

The exhaustive brute-force search uses Float32 squared-L2 arithmetic. Its 0.9990 ID recall against direct Float64 distances reflects changes near the top-10 boundary; the measured maximum distance error was 6.72e-6.

Download the capture JSON, including all three exact SQL statements, run query IDs, CPU-oracle metrics, post-search family measurements, and conditions.

Exact brute force

WITH train_index AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'train' AND m.file_type = 'Win32'
    ORDER BY v.id
), test_queries AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'test' AND m.file_type = 'Win32'
    ORDER BY v.id
)
SELECT n.query_ordinal,
       n.query_id,
       qm.week_id AS query_week,
       q.label AS query_label,
       n.rank,
       n.neighbor_id,
       tm.week_id AS neighbor_week,
       train.label AS neighbor_label,
       tm.sha256 AS neighbor_sha256,
       tm.family AS neighbor_family,
       n.distance
FROM cuvs_brute_force_knn(
    dataset => (SELECT id, vector FROM train_index ORDER BY id),
    queries => (SELECT id, vector FROM test_queries ORDER BY id),
    k => 10,
    metric => 'l2_expanded'
) n
JOIN ember_research_vectors q ON q.id = n.query_id AND q.split = 'test'
JOIN ember_metadata qm ON qm.id = q.id
JOIN ember_research_vectors train ON train.id = n.neighbor_id AND train.split = 'train'
JOIN ember_metadata tm ON tm.id = train.id
ORDER BY n.query_ordinal, n.rank;

Dedicated r3 capture query ID: 73f3ec94-7127-4b7f-95a7-6a949ceeaa98.

CAGRA

-- CAGRA approximate search over the same ordered Win32 train and test cohorts
-- used by 20_cuvs_brute_force_knn.sql.
WITH train_index AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'train' AND m.file_type = 'Win32'
    ORDER BY v.id
), test_queries AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'test' AND m.file_type = 'Win32'
    ORDER BY v.id
)
SELECT n.query_ordinal,
       n.query_id,
       qm.week_id AS query_week,
       q.label AS query_label,
       n.rank,
       n.neighbor_id,
       tm.week_id AS neighbor_week,
       train.label AS neighbor_label,
       tm.sha256 AS neighbor_sha256,
       tm.family AS neighbor_family,
       n.distance
FROM cuvs_cagra(
    dataset => (SELECT id, vector FROM train_index ORDER BY id),
    queries => (SELECT id, vector FROM test_queries ORDER BY id),
    k => 10,
    metric => 'l2_expanded',
    graph_degree => 32,
    intermediate_graph_degree => 64
) n
JOIN ember_research_vectors q ON q.id = n.query_id AND q.split = 'test'
JOIN ember_metadata qm ON qm.id = q.id
JOIN ember_research_vectors train ON train.id = n.neighbor_id AND train.split = 'train'
JOIN ember_metadata tm ON tm.id = train.id
ORDER BY n.query_ordinal, n.rank;

Dedicated r3 capture query ID: c0f13481-511d-4600-815e-b43ab2685418.

IVF-Flat

-- IVF-Flat approximate search with 128 lists and 16 probes. Keep the input
-- cohorts aligned with the exact-search baseline for later recall comparison.
WITH train_index AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'train' AND m.file_type = 'Win32'
    ORDER BY v.id
), test_queries AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors v
    JOIN ember_metadata m ON m.id = v.id
    WHERE v.split = 'test' AND m.file_type = 'Win32'
    ORDER BY v.id
)
SELECT n.query_ordinal,
       n.query_id,
       qm.week_id AS query_week,
       q.label AS query_label,
       n.rank,
       n.neighbor_id,
       tm.week_id AS neighbor_week,
       train.label AS neighbor_label,
       tm.sha256 AS neighbor_sha256,
       tm.family AS neighbor_family,
       n.distance
FROM cuvs_ivf_flat(
    dataset => (SELECT id, vector FROM train_index ORDER BY id),
    queries => (SELECT id, vector FROM test_queries ORDER BY id),
    k => 10,
    metric => 'l2_expanded',
    n_lists => 128,
    n_probes => 16
) n
JOIN ember_research_vectors q ON q.id = n.query_id AND q.split = 'test'
JOIN ember_metadata qm ON qm.id = q.id
JOIN ember_research_vectors train ON train.id = n.neighbor_id AND train.split = 'train'
JOIN ember_metadata tm ON tm.id = train.id
ORDER BY n.query_ordinal, n.rank;

Dedicated r3 capture query ID: 02f1af3f-270f-4a22-8559-dee13bd59478.

Limits

• Validation resolves named tables or views and reads schemas only; it does not execute relation scans or GPU work.
• Execution relation arguments require parenthesized subqueries; dry-run validation accepts registered named relations only.
• Builds and destroys its query-local index in the statement; no index persists across statements.
• Results are approximate unless all lists are probed. Fewer than k neighbors may be returned for a query; missing (query, rank) rows are omitted and valid ranks remain contiguous.
• The evaluated dataset must be non-empty, k and n_lists must not exceed its row count, and n_probes must not exceed n_lists.
• Dataset and query dimensions and element types must match. Cosine requires at least two dimensions.

To dry-run validate relation metadata, column types, and options without execution, see gpu_validate_call.