Brute-force kNN

UDTF: cuvs_brute_force_knn

Official cuVS reference: C API

Exact brute-force nearest-neighbor search over dense vectors.

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.

SELECT *
FROM cuvs_brute_force_knn(
  dataset => (SELECT id, d0, d1 FROM dataset_vectors),
  queries => (SELECT id, d0, d1 FROM query_vectors),
  k => 8,
  metric => 'l2_expanded'
);

Inputs

Each relation argument is a parenthesized SELECT subquery that the planner keeps as an operator child. Metadata validation resolves a registered table or view for the same role. 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

Arguments and options

Scalar SQL arguments

Argument	Type	Required	Default	Constraints	Description
k	integer	yes	No default	minimum 1; maximum 4294967295	Number of neighbors returned for each evaluated query row.
metric	string	yes	No default	one of "l2_expanded", "l2_sqrt_expanded", "cosine", "inner_product"	Distance or score metric. Distance metrics rank lower values first; inner product ranks higher values first.

Vector binding shapes

Each relation subquery must project an id field followed by either one or more feature fields of a supported element type (Float32) or one list field of a supported element type, under any name.

For wide vectors, the projection order defines the feature dimensions. A list vector relation must contain no feature field beside id and its list field. Nullable field types are accepted. A null ID, feature value, list row, or list element fails the query.

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.

The output schema depends on the call. Run gpu_validate_call against registered relations to inspect the schema that the validated ID types and literal options produce.

Examples

MerRec

Does a Pokemon lookalike audience find new interest?

The lookalike list contains shoppers whose taste vectors resemble earlier Pokemon buyers. The query checks whether they later bought Pokemon and whether they had already viewed it, against the simple rule of selecting previous Pokemon viewers.

Earlier CTEs construct 64 category-preference features from views before September 1, 2023. Each value is the square root of a shopper's share of views in one of the 64 busiest categories. Eligibility requires 20 views. The seed contains 1,302 eligible July to August Pokemon buyers; candidates excludes them.

The unfolded part of the SQL searches for 20 neighbors per seed buyer, deduplicates the list, splits it by prior Pokemon views and joins September to October purchases. The folded relations build the features in the same query.

), lookalikes AS (
  SELECT DISTINCT neighbor_id AS id
  FROM cuvs_brute_force_knn(
    dataset => (SELECT * FROM candidates ORDER BY id),
    queries => (SELECT t.* FROM taste t JOIN seed s ON s.id = t.id ORDER BY t.id),
    k => 20, metric => 'l2_expanded')
), pokemon_viewers AS (
  SELECT DISTINCT user_id AS id FROM views WHERE brand_id = 17470
), later_buyers AS (
  SELECT DISTINCT user_id AS id FROM pokemon_buys WHERE later = 1
), audiences AS (
  SELECT labels.audience, l.id
  FROM lookalikes l LEFT JOIN pokemon_viewers v ON v.id = l.id
  CROSS JOIN (
    SELECT 'lookalikes' AS audience, 0 AS prior_view
    UNION ALL SELECT 'lookalikes with prior Pokemon views', 1
    UNION ALL SELECT 'lookalikes without prior Pokemon views', 2
  ) labels
  WHERE labels.prior_view = 0
     OR (labels.prior_view = 1 AND v.id IS NOT NULL)
     OR (labels.prior_view = 2 AND v.id IS NULL)
  UNION ALL SELECT 'pokemon viewers', c.id FROM candidates c JOIN pokemon_viewers v ON v.id = c.id
  UNION ALL SELECT 'all active shoppers', c.id FROM candidates c
)
SELECT a.audience, COUNT(*) AS shoppers, COUNT(b.id) AS buyers_sep_oct,
       CAST(COUNT(b.id) AS DOUBLE) / COUNT(*) AS purchase_rate
FROM audiences a LEFT JOIN later_buyers b ON b.id = a.id
GROUP BY a.audience
ORDER BY purchase_rate DESC;

Audience	Shoppers	Later buyers	Purchase rate
All lookalikes	19,124	132	0.690%
Lookalikes with prior views	13,268	130	0.980%
Lookalikes without prior views	5,856	2	0.034%
All previous viewers	250,627	572	0.228%
All eligible non-seed shoppers	2,079,441	688	0.033%

The two prior-view rows partition the lookalike audience. They are not additional shoppers beyond the 19,124 in the first row.

Of the 132 later buyers in the lookalike list, 130 had already viewed Pokemon. The never-viewed group's 0.034% purchase rate is close to the 0.033% eligible baseline. This result supplies little evidence for finding a new audience.

The direct-viewer rule reaches 572 buyers from a much larger list. The campaign list compares the methods at equal list size. Features use the latest retained category attributes.

The capture records the SQL and results used here.

Amazon Reviews

Which travel headphones deserve a catalog review?

The request is "Wireless noise cancelling over ear headphones for long airplane flights." Eligible products cost $20 to $150 in the snapshot, have at least 100 verified reviews in 2022 and have no more than 20% low ratings among those reviews.

SQL defines the eligible products before exact nearest-neighbor search, then joins ranked IDs to titles and review counts. The request and product vectors were encoded before the query. Both are L2 normalized. Squared L2 distance d converts to cosine similarity as 1 - d / 2.

WITH review_history AS (
  SELECT product_id, COUNT(*) AS reviews_2022,
         AVG(CASE WHEN rating <= 2 THEN 1.0 ELSE 0.0 END) AS low_rating_share
  FROM reviews
  WHERE review_time >= TIMESTAMP '2022-01-01'
    AND review_time < TIMESTAMP '2023-01-01' AND verified_purchase
  GROUP BY product_id
), eligible AS (
  SELECT v.id, v.vector
  FROM semantic_products_vectors v
  JOIN products p ON p.product_id = v.id
  JOIN review_history h ON h.product_id = v.id
  WHERE v.sample_rank < 1000000
    AND p.category_l3 = 'Headphones & Earbuds'
    AND p.price BETWEEN 20 AND 150
    AND h.reviews_2022 >= 100 AND h.low_rating_share <= 0.20
)
SELECT n.rank, p.product_id, p.title, p.price,
       h.reviews_2022, h.low_rating_share,
       1.0 - n.distance / 2.0 AS cosine_similarity
FROM cuvs_brute_force_knn(
  dataset => (SELECT id, vector FROM eligible),
  queries => (SELECT id, vector FROM intent_queries WHERE id = 0),
  k => 10, metric => 'l2_expanded') n
JOIN products p ON p.product_id = n.neighbor_id
JOIN review_history h ON h.product_id = p.product_id
ORDER BY n.rank

The first two candidates expose the distinction between passing numeric filters and satisfying the request:

Candidate	Price	Verified reviews	Low-rating share	Similarity
1. Srhythm NC35, over-ear	$69.99	353	16.15%	0.639
2. VELKPRO office headset, on-ear	$29.99	103	5.83%	0.611

The query took 1.98 s.

The VELKPRO title describes office calls and an on-ear design. Its vector ranks second, but product specifications and design allow a reviewer to filter it from the travel shortlist.

A later raw-table check found 179 eligible products, and 117 had vectors in the sampled corpus. The other 62 were outside this search.

The main capture retains all ten candidates.

Which Sony listings should an editor compare?

For catalog maintenance, a separate query retrieves the ten nearest listings for each of 10,000 query products within a one-million-product corpus. An editor can then compare condition, color and accessories before treating them as alternatives.

SELECT n.query_id, q.title AS source_title, n.rank,
       n.neighbor_id, p.title AS candidate_title,
       1.0 - n.distance / 2.0 AS cosine_similarity
FROM cuvs_brute_force_knn(
  dataset => (SELECT * FROM product_corpus_1000000),
  queries => (SELECT * FROM product_queries_10000),
  k => 10, metric => 'l2_expanded') n
JOIN semantic_products_text q ON q.id = n.query_id
JOIN semantic_products_text p ON p.id = n.neighbor_id
ORDER BY n.query_id, n.rank

For a midnight-blue Sony WH-1000XM4, three leading matches are:

Listing difference	Text similarity
Black, renewed	0.951
Black, with case, charger and gym bag	0.905
Silver, with battery bank	0.884

The query took 2.39 s.

The shared model name explains why these are useful comparison candidates. Condition and bundle contents still determine the editing decision. The SQL returns 100,000 matches with their titles already joined. It does not establish that the pairs are duplicate SKUs.

EMBER2024

Which earlier files resemble a new-quarter file?

This EMBER2024 example searches earlier Win32 files for structural references to files in a later test period. The current relations contain the full deduplicated splits. The corpus has 1,560,000 Win32 training files from weeks 0 to 51; the query selects the first 10,000 Win32 test files by ID from weeks 52 to 63. Each file is described by a 512-element normalized byte and byte-entropy histogram.

The exact search returns ten historical references for each test query, 100,000 rows in all. It passes only IDs and vectors to cuvs_brute_force_knn. Labels, family names, weeks and SHA-256 values join after the search. For test file 10021000 from week 52, the first three references have the saved family field cosmu:

Rank	Historical file	Week	Squared-L2 distance
1	2751446	27	0.00007534
2	3779432	37	0.00009656
3	4070733	40	0.00010586

The shared family field is a reason to inspect these references. It does not make the nearest-neighbor distance a calibrated malware score or classify the query file. The full-corpus capture has no independent CPU recall oracle, so it supports retrieval and row counts without a full-scale quality claim.

Exact search

The exact search returned 100,000 rows.

-- Exact squared-L2 reference search over full Win32 train dataset and test queries.
-- Corpus: full deduplicated Win32 training dataset (~1.56M files) across weeks 0-51.
-- Queries: 10,000 held-out Win32 test queries from weeks 52-63.
-- Post-search enrichment joins query and neighbor metadata only after vector search.
WITH train_index AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors_train v
    JOIN ember_metadata_train m ON m.id = v.id
    WHERE m.file_type = 'Win32'
    ORDER BY v.id
), test_queries AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors_test v
    JOIN ember_metadata_test m ON m.id = v.id
    WHERE m.file_type = 'Win32'
    ORDER BY v.id
    LIMIT 10000
)
SELECT n.query_ordinal,
       n.query_id,
       qm.week_id AS query_week,
       qm.label AS query_label,
       n.rank,
       n.neighbor_id,
       tm.week_id AS neighbor_week,
       tm.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_metadata_test qm ON qm.id = n.query_id
JOIN ember_metadata_train tm ON tm.id = n.neighbor_id
ORDER BY n.query_ordinal, n.rank;

IVF-Flat

The IVF-Flat query used 1,024 lists and 32 probes over the same full corpus and 10,000-query batch. It returned 100,000 rows. Its first three references for file 10021000 have the same IDs as the exact result. That one example does not establish recall over all queries.

-- IVF-Flat approximate reference search over the same full Win32 train dataset and test queries.
-- Index is built query-locally with 1024 lists and searched with 32 probes.
-- Corpus: full deduplicated Win32 training dataset (~1.56M files).
-- Queries: 10,000 held-out Win32 test queries from weeks 52-63.
WITH train_index AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors_train v
    JOIN ember_metadata_train m ON m.id = v.id
    WHERE m.file_type = 'Win32'
    ORDER BY v.id
), test_queries AS (
    SELECT v.id, v.histogram AS vector
    FROM ember_research_vectors_test v
    JOIN ember_metadata_test m ON m.id = v.id
    WHERE m.file_type = 'Win32'
    ORDER BY v.id
    LIMIT 10000
)
SELECT n.query_ordinal,
       n.query_id,
       qm.week_id AS query_week,
       qm.label AS query_label,
       n.rank,
       n.neighbor_id,
       tm.week_id AS neighbor_week,
       tm.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 => 1024,
    n_probes => 32
) n
JOIN ember_metadata_test qm ON qm.id = n.query_id
JOIN ember_metadata_train tm ON tm.id = n.neighbor_id
ORDER BY n.query_ordinal, n.rank;

The full-reference capture records the exact, IVF-Flat and failed CAGRA attempts with their query IDs, source result paths and first-query records. The CAGRA attempt ended during index construction and returned no result rows.

Limits

• Builds an exact query-local index over the evaluated dataset relation; it does not persist an ANN index or replace a vector database.
• The evaluated dataset must be non-empty and k must not exceed its row count. An empty query relation may return an empty result with the stable schema.
• Dataset and query vector dimensions must match after both relation children are evaluated.
• Distances repeat between runs; neighbors that tie at the k-th distance can differ, because candidate merge order is not fixed.