Vector Top-K

UDTF: cuvs_top_k

Official cuVS reference: C++ API

Select the smallest or largest values in every dense vector row.

Quickstart

The call below expects the registered relation input_vectors (the input role), passed as a parenthesized SELECT subquery.

SELECT *
FROM cuvs_top_k(
  input => (SELECT id, d0, d1 FROM input_vectors),
  k => 1,
  select => 'max'
)
ORDER BY row_ordinal, rank;

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
input	yes	table	Dense-vector rows consumed by this operation.

Vector element types

Feature values must be Float32.

Arguments and options

Scalar SQL arguments

Argument	Type	Required	Default	Constraints	Description
k	integer	yes	No default	minimum 1; maximum 4294967295	Number of values selected from each row.
select	string	yes	No default	one of "min", "max"	Select the minimum or maximum values.

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
row_ordinal	UInt64	no	Zero-based physical ordinal of the evaluated input row; it disambiguates duplicate input IDs. Follows the input's top-level ORDER BY on output columns; unspecified without one.
id	same_as_input.id	no	Logical ID copied from the input relation.
rank	UInt32	no	One-based value rank within an input row. Order consumers explicitly by row_ordinal, rank.
position	Int64	no	Zero-based dimension index of the selected value within the input vector.
value	Float32	no	Selected Float32 input vector value; rank follows ascending values for min and descending values for max.

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

Amazon Reviews

This example reads the raw products and reviews tables from the Amazon Reviews demo dataset. It does not use prepared text embeddings.

Which review themes describe each group of products?

The saved query groups 2,384 products by eight keyword-mention rates in their 2022 low-rating reviews. It returns the three largest average rates for each of six groups. One group has the following profile:

Rank	Issue mentioned	Mean product mention rate
1	Durability	35.99%
2	Sound	18.61%
3	Returns	11.23%

Another group leads with sound at 45.10%, followed by charging at 24.20% and connection at 24.09%. These rates suggest which original reviews an analyst should read. They are averages of product-level rates. A product with ten reviews has the same weight as a product with a thousand.

SQL filters headphones and accessories with at least ten low-rating reviews, calculates eight substring rates for each product, and fits KMeans to those rates. It then averages each group's product profiles. Top-K returns the three largest dimensions within each profile, rather than across all groups. No predefined complaint labels enter this query.

Read the query: review mentions → KMeans → Top-K

WITH complaint_features AS (
    SELECT
        p.product_id AS id,
        COUNT(*) AS negative_reviews,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%battery%' THEN 1.0 ELSE 0.0 END) AS REAL) AS battery,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%connect%' OR LOWER(r.text) LIKE '%bluetooth%' THEN 1.0 ELSE 0.0 END) AS REAL) AS connection,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%sound%' OR LOWER(r.text) LIKE '%volume%' THEN 1.0 ELSE 0.0 END) AS REAL) AS sound,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%comfort%' OR LOWER(r.text) LIKE '%fit%' THEN 1.0 ELSE 0.0 END) AS REAL) AS comfort,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%broke%' OR LOWER(r.text) LIKE '%stopped working%' THEN 1.0 ELSE 0.0 END) AS REAL) AS durability,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%charg%' THEN 1.0 ELSE 0.0 END) AS REAL) AS charging,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%microphone%' OR LOWER(r.text) LIKE '%mic %' THEN 1.0 ELSE 0.0 END) AS REAL) AS microphone,
        CAST(AVG(CASE WHEN LOWER(r.text) LIKE '%return%' OR LOWER(r.text) LIKE '%refund%' THEN 1.0 ELSE 0.0 END) AS REAL) AS returns
    FROM reviews AS r
    JOIN products AS p
        ON r.product_id = p.product_id
    WHERE p.category_l2 = 'Headphones, Earbuds & Accessories'
      AND r.review_time >= TIMESTAMP '2022-01-01 00:00:00'
      AND r.review_time <  TIMESTAMP '2023-01-01 00:00:00'
      AND r.rating <= 2
      AND r.text IS NOT NULL
    GROUP BY p.product_id
    HAVING COUNT(*) >= 10
),

clusters AS (
    SELECT
        id,
        cluster_id
    FROM cuvs_kmeans(
        input => (
            SELECT
                id,
                COALESCE(battery, CAST(0 AS REAL))    AS battery,
                COALESCE(connection, CAST(0 AS REAL)) AS connection,
                COALESCE(sound, CAST(0 AS REAL))      AS sound,
                COALESCE(comfort, CAST(0 AS REAL))    AS comfort,
                COALESCE(durability, CAST(0 AS REAL)) AS durability,
                COALESCE(charging, CAST(0 AS REAL))   AS charging,
                COALESCE(microphone, CAST(0 AS REAL)) AS microphone,
                COALESCE(returns, CAST(0 AS REAL))    AS returns
            FROM complaint_features
            ORDER BY id
        ),
        n_clusters => 6,
        n_init => 5
    )
),

profiles AS (
    SELECT
        CAST(k.cluster_id AS BIGINT) AS id,
        COALESCE(CAST(AVG(c.battery) AS REAL), CAST(0 AS REAL))    AS battery,
        COALESCE(CAST(AVG(c.connection) AS REAL), CAST(0 AS REAL)) AS connection,
        COALESCE(CAST(AVG(c.sound) AS REAL), CAST(0 AS REAL))      AS sound,
        COALESCE(CAST(AVG(c.comfort) AS REAL), CAST(0 AS REAL))    AS comfort,
        COALESCE(CAST(AVG(c.durability) AS REAL), CAST(0 AS REAL)) AS durability,
        COALESCE(CAST(AVG(c.charging) AS REAL), CAST(0 AS REAL))   AS charging,
        COALESCE(CAST(AVG(c.microphone) AS REAL), CAST(0 AS REAL)) AS microphone,
        COALESCE(CAST(AVG(c.returns) AS REAL), CAST(0 AS REAL))    AS returns
    FROM clusters AS k
    JOIN complaint_features AS c
        ON k.id = c.id
    GROUP BY k.cluster_id
)

SELECT
    t.id AS cluster_id,
    t.rank,
    CASE t.position
        WHEN 0 THEN 'battery'
        WHEN 1 THEN 'connection'
        WHEN 2 THEN 'sound'
        WHEN 3 THEN 'comfort'
        WHEN 4 THEN 'durability'
        WHEN 5 THEN 'charging'
        WHEN 6 THEN 'microphone'
        WHEN 7 THEN 'returns'
    END AS issue,
    t.value AS mean_product_mention_rate
FROM cuvs_top_k(
    input => (
        SELECT
            id,
            battery,
            connection,
            sound,
            comfort,
            durability,
            charging,
            microphone,
            returns
        FROM profiles
    ),
    k => 3,
    select => 'max'
) AS t
ORDER BY
    t.id,
    t.rank;

position = 5 means charging because charging is the sixth projected feature.

The saved output contains all six groups.

Each rate counts reviews containing an English substring. Negation is not resolved, and one review can mention several themes. Mentions of returns do not establish a completed return transaction.

EMBER2024

Which high-scoring files should an analyst inspect first?

The query scores all 6,315 challenge files with a pretrained LightGBM model using 2,568 features, then joins the three largest positive feature-block contributions for each file. A review queue built from this result carries both the score and the inputs behind it.

The model is loaded by cuml_forest_predict. CPU LightGBM pred_contrib was run before SQL, with its 2,568 contributions summed into 12 named blocks. cuvs_top_k ranks those prepared values. The query does not compute SHAP.

SELECT p.id, m.sha256, m.file_type, m.week_id, p.prediction,
       t.rank, g.group_name, t.value AS positive_log_odds_contribution
FROM cuml_forest_predict(
  input => (
    SELECT id, vector
    FROM ember_research_vectors_challenge
    ORDER BY id
  ),
  model => 'EMBER2024_all.model',
  model_format => 'lightgbm'
) p
JOIN cuvs_top_k(
  input => (SELECT id, vector FROM ember_attributions ORDER BY id),
  k => 3,
  select => 'max'
) t ON t.id = p.id
JOIN ember_feature_groups g ON g.group_index = t.position
JOIN ember_metadata_challenge m ON m.id = p.id
WHERE p.prediction >= 0.99 AND t.value > 0
ORDER BY p.prediction DESC, p.id, t.rank
LIMIT 30;

The 0.99 threshold includes 1,616 files before the output limit. The returned 30 rows represent ten files in this capture. The highest-ranked file is 20001348, with a score of 0.999851. Its three returned contributions are:

Feature block	Positive log-odds contribution
histogram	+2.504
strings	+2.249
header	+1.235

The query took 0.24 s.

The returned blocks identify model inputs for further inspection. Negative contributions and model bias are absent from the table, and these values do not explain why a file is malicious.

GPU predictions matched CPU LightGBM within 3.0e-8 maximum absolute error across all 6,315 files. Every challenge label is malicious, so this cohort cannot measure false-positive rate or establish score calibration.

The capture retains the full SHA-256 records, all 30 rows, attribution preparation and CPU score comparison.

Limits

• k must not exceed the vector dimension. Empty input returns an empty result with the stable schema.
• Equal selected values are ordered by position; when ties straddle rank k, the selected positions among them are unspecified.