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. Substitute your own relations and column names.

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 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

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")	yes	Distance or score metric. Distance metrics rank lower values first; inner product ranks higher values first.

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.
metric	yes	string	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. Supported element/metric combinations: Float32: l2_expanded, l2_sqrt_expanded, cosine, inner_product.

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) 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

Amazon Reviews

This example uses the Amazon Reviews demo dataset.

Which travel headphones meet our price and review-history criteria?

A merchandising analyst needs a shortlist for long flights. The request is “Wireless noise cancelling over ear headphones for long airplane flights.” The candidate must also cost $20–150 in the catalog snapshot, have at least 100 verified-purchase reviews in 2022, and have at most 20% reviews rated at most two stars in that period.

The query computes those review statistics from the complete reviews table and joins them to the product catalog before nearest-neighbor search. The 117 eligible products become the search dataset. Changing the year or the rating threshold changes that dataset within the statement.

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 query vector is row id = 0 in intent_queries, prepared with BGE-M3 from the request above. Stored product and query vectors are normalized; 1 - squared_L2 / 2 therefore gives cosine similarity. SQL enriches each neighbor with the same review statistics used to admit the candidate.

The first five rows of the 2026-09-28 capture are below. Product names are shortened; the capture metadata and result rows retain their IDs and complete titles.

Rank	Product	Snapshot price	Verified reviews, 2022	Low-rating share	Cosine similarity
1	Srhythm NC35	$69.99	353	16.15%	0.6392
2	VELKPRO office headset	$29.99	103	5.83%	0.6105
3	HROEENOI over-ear ANC headphones	$45.99	354	17.51%	0.6034
4	Soundcore Space Q45	$149.99	179	11.73%	0.5999
5	PurelySound E7	$39.48	310	14.19%	0.5995

The office headset in second place meets the SQL conditions, but its title describes on-ear office use. A catalog reviewer can reject it on that basis while keeping the price and review-history evidence attached to the other candidates. The complete statement took a median 1.54 seconds over three runs; capture conditions include index construction and Flight result transfer.

The shortlist supports catalog review. A high similarity score does not establish wireless connectivity, over-ear fit or effective noise cancellation; those requirements still need product-specification checks. Catalog prices are historical, and the low-rating share is a fraction of reviews, not a failure rate.

Refresh similar-product candidates for a batch of catalog entries

A batch comparison uses one million catalog vectors and 10,000 disjoint query products, each with 1,024 dimensions. This statement returns ten neighbors for each query product:

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;

The output can seed a catalog editor's comparison queue. Matching text does not establish that two listings describe the same item or interchangeable products. The index is built within each statement, including when the input is a view.

The capture returned 100,000 rows in a median 2.58 seconds over three runs. For source product 1248764, a midnight-blue Sony WH-1000XM4 listing, the first candidates illustrate why the result is useful for human comparison:

Rank	Candidate product ID	Shortened title	Cosine similarity
1	1271346	Sony WH-1000XM4, black, renewed	0.9513
2	1548411	Sony WH-1000XM4 bundle with case, charger and gym bag	0.9046
3	1267625	Sony WH-1000XM4, silver, bundle with battery bank	0.8836

The candidates share the headphone model while differing in condition, color or included items. An editor can inspect those differences before merging listings or presenting alternatives.

Float32 l2_expanded can round near-identical vectors to zero distance. In the separate 100,000-neighbor verification, three reported zeros had positive Float64 distances, at most 0.0002202. A zero distance alone does not establish a duplicate listing.

EMBER2024 references

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 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.

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