Vector Functions

  • Operational Insights
  • reference
Learn more about the vector search functions available in {sqlppc}.

Vector functions compute a distance, or similarity, value between a stored vector field and a query vector. SQL++ for Operational Insights Cloud provides 2 kinds of vector functions:

  • K-nearest neighbor (KNN) functions compute the exact distance between a pair of vectors. A query that calls one without a matching VTREE index applies it to every record in the collection in turn, which scans the whole collection. Use KNN functions when no compatible index exists, or when you need an exact result rather than an approximation.

  • Approximate nearest neighbor (ANN) functions use a VTREE vector index to prune the search to a subset of clusters, trading a small amount of recall for query speed. For more information about creating a VTREE index, see CREATE a Vector Index.

The examples in this topic use an Entertainment.Movie collection, containing movie records with an embedding field. This example field is a 384-dimensional array of floating point numbers derived by embedding each movie’s title and overview.

cosine_similarity

  • Syntax:

    cosine_similarity(vector1, vector2)
  • Computes the exact cosine similarity between vector1 and vector2. Unlike a distance function, a greater cosine_similarity value indicates a closer match. cosine_similarity is not eligible for a VTREE index. Use ann_distance(vector_field, query_vector, "cosine") for an indexed search, or cosine_distance for an exact ranking.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number between -1 and 1 representing the cosine similarity between vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

      • vector1 or vector2 has a magnitude of 0.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET sim = cosine_similarity(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, sim
    ORDER BY sim DESC
    LIMIT 50;

cosine_distance

  • Syntax:

    cosine_distance(vector1, vector2)
  • Computes 1 minus the cosine similarity between vector1 and vector2, giving the angular distance between their directions. A value of 0 means the vectors point in the same direction, and a value of 2 means they point in opposite directions.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number between 0 and 2 representing the cosine distance between vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

      • vector1 or vector2 has a magnitude of 0.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = cosine_distance(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

dot_product

  • Syntax:

    dot_product(vector1, vector2)
  • Computes the dot product between vector1 and vector2. Unlike a distance function, a greater dot_product value indicates a closer match. dot_product is not eligible for a VTREE index. Use ann_distance(vector_field, query_vector, "dot") for an indexed search, or dot_distance for an exact ranking.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number representing the dot product of vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET sim = dot_product(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, sim
    ORDER BY sim DESC
    LIMIT 50;

dot_distance

  • Syntax:

    dot_distance(vector1, vector2)
  • Computes the negated dot product between vector1 and vector2, so that a smaller value indicates a closer match.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number representing the negated dot product of vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = dot_distance(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

euclidean_distance

  • Syntax:

    euclidean_distance(vector1, vector2)
  • Computes the Euclidean, or L2, distance between vector1 and vector2. L2_DISTANCE is an alias for EUCLIDEAN_DISTANCE.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number representing the Euclidean distance between vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = euclidean_distance(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

euclidean_squared_distance

  • Syntax:

    euclidean_squared_distance(vector1, vector2)
  • Computes the squared Euclidean, or L2 squared, distance between vector1 and vector2. Squared Euclidean distance omits the final square root calculation, so it’s cheaper to compute than euclidean_distance while preserving the same distance ordering. L2_SQUARED_DISTANCE is an alias for EUCLIDEAN_SQUARED_DISTANCE.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

  • Return Value:

    • A floating point number representing the squared Euclidean distance between vector1 and vector2.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

  • Example:

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = euclidean_squared_distance(mov.embedding, qvec)
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

vector_distance

  • Syntax:

    vector_distance(vector1, vector2, distance_function)
  • Computes the exact distance between vector1 and vector2. Use vector_distance for an exact K-nearest neighbor (KNN) result, or when no applicable VTREE index exists.

  • Arguments:

    • vector1 is the first vector, expressed as an array of floating point numbers.

    • vector2 is the second vector, expressed as an array of floating point numbers.

    • distance_function is a string literal specifying the distance metric: cosine, dot, l2 or euclidean, or l2_squared or euclidean_squared. For cosine, the result is 1 minus the cosine similarity. For dot, the result is the negated dot product. For every metric, a smaller value indicates a closer match.

  • Return Value:

    • A floating point number representing the exact distance between vector1 and vector2, according to distance_function.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • An error at compile time, if distance_function is not a valid string literal.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

  • Example:

    Find the exact 50 nearest movies to a given query vector by cosine distance, without using an index.

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = vector_distance(mov.embedding, qvec, "cosine")
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

ann_distance

  • Syntax:

    ann_distance(vector_field, query_vector, distance_function)
    ann_distance(vector_field, query_vector, distance_function, min_probe_fraction)
    ann_distance(vector_field, query_vector, distance_function, min_probe_fraction, k_multiplier)
  • Computes the approximate distance between vector_field and query_vector when an eligible VTREE index exists and applies to the query. Otherwise, computes the exact distance. APPROX_VECTOR_DISTANCE is an alias for ANN_DISTANCE.

  • Arguments:

    • vector_field is a reference to the vector field to search. It must match the vector field declared when the index was created.

    • query_vector is the query vector, expressed as an array of floating point numbers.

    • distance_function is a string literal specifying the distance metric. It must match the similarity parameter used when a VTREE index was created in order for the index to be used. Otherwise, the query falls back to exact KNN evaluation. SQL++ for Operational Insights Cloud supports dot similarity only for normalized embeddings in this release. For cosine, the result is 1 minus the cosine similarity. For dot, the result is the negated dot product. For every metric, a smaller value indicates a closer match.

    • min_probe_fraction is optional. It must be a numeric literal. It’s a float ranging from 0.0 to 1.0 that sets the fraction of the epsilon-narrowed shortlist of leaf-level candidates to probe. If you omit this argument, or pass 0, Operational Insights uses the index’s default probe behavior.

      Default: 0.75

    • k_multiplier is optional. It must be a numeric literal. It’s an integer that sets how many candidates, expressed as k times this multiplier, the ANN operator retrieves from each storage partition before final ranking. If you omit this argument, Operational Insights uses the default multiplier.

      Default: 1

  • Return Value:

    • A floating point number representing the distance between vector_field and query_vector, according to distance_function.

    • missing, if either vector argument is missing.

    • null, if either vector argument is null.

    • An error at compile time, if distance_function is not a valid string literal.

    • null, with a warning, if any of the following is true:

      • Either argument is not an array.

      • The 2 arrays have different lengths.

      • Either array is empty.

      • Either array contains a non-numeric element.

    • SQL++ for Operational Insights Cloud uses the VTREE index only when all of the following are true:

      • An eligible VTREE index exists on vector_field.

      • distance_function matches the index’s similarity.

      • The query is a top-k query: it uses ORDER BY on the distance in ascending order, with no additional sort key other than NULLS LAST, followed by LIMIT k.

      • LIMIT k has no OFFSET.

      • If present, a WHERE predicate references only fields in the index’s INCLUDE list and contains no non-deterministic function, such as random().

      • No UNNEST appears between the collection and the ORDER BY.

        Even when every condition above holds, the cost-based optimizer can still choose the exact scan if it estimates that to be cheaper.

        If query_vector is a constant whose length differs from the index’s dimension, SQL++ for Operational Insights Cloud raises a warning and does not use the index.

        Otherwise, SQL++ for Operational Insights Cloud evaluates the query as an exact KNN scan.

  • Example:

    Find the 50 movies with an embedding closest to a given query vector, using cosine distance and the index’s default probe settings.

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = ann_distance(mov.embedding, qvec, "cosine")
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

    Trade off slower performance for higher recall by using a probe fraction greater than the default of 0.75. This also doubles the number of candidates retrieved from each storage partition before the final ranking. The probe fraction is a minimum: the search keeps probing until it has k results.

    USE Entertainment;
    LET qvec = [ /* 384-dimensional float array */ ]
    FROM Movie mov
    LET dist = ann_distance(mov.embedding, qvec, "cosine", 0.9, 2)
    SELECT mov.id, mov.title, mov.release_date, dist
    ORDER BY dist
    LIMIT 50;

approx_vector_distance

  • Syntax:

    approx_vector_distance(vector_field, query_vector, distance_function)
    approx_vector_distance(vector_field, query_vector, distance_function, min_probe_fraction)
    approx_vector_distance(vector_field, query_vector, distance_function, min_probe_fraction, k_multiplier)
  • An alias for ann_distance. Both names accept the same arguments and return the same result.

normalize_vector

  • Syntax:

    normalize_vector(vec)
  • Returns vec scaled to a length of 1, keeping the same direction. Use normalize_vector to compute the unit-length (L2-normalized) form of a vector required by the cosine and dot metrics, without performing the division yourself. NORMALISE_VECTOR, VECTOR_NORMALISE, and VECTOR_NORMALIZE are aliases for NORMALIZE_VECTOR.

  • Arguments:

    • vec is the vector to normalize, expressed as an array of floating point numbers.

  • Return Value:

    • An array of floating point numbers the same length as vec, scaled so its L2 norm is 1. Each integer element in the result becomes a floating point number.

    • missing, if vec is missing.

    • null, if vec is null.

    • null, with a warning, if any of the following is true:

      • vec is not an array.

      • vec is an empty array.

      • vec contains a non-numeric element.

      • vec contains an infinite element.

      • vec has a magnitude of 0.

  • Example:

    normalize_vector([3, 4])      -- [0.6, 0.8]
    normalize_vector([0, 0, 0])   -- null, with a warning
    normalize_vector("x")         -- null, with a warning

isvector

  • Syntax:

    isvector(vec, dimension)
  • Returns whether vec is a usable vector, meaning a non-empty array of dimension numeric elements. A record whose vector field satisfies isvector is eligible to be indexed with a VTREE index. IS_VECTOR is an alias for ISVECTOR.

  • Arguments:

    • vec is any value to test.

    • dimension is an integer specifying the required number of elements.

  • Return Value:

    • true, if vec is an array whose elements are all numeric values, with exactly dimension elements.

    • false, if vec is a non-null, non-missing value that’s not an array, is an empty array, or contains a non-numeric element. false also results when the element count does not match dimension.

    • missing, if either argument is missing.

    • null, if either argument is null.

    • null, with a warning, if any of the following is true:

      • dimension is not an integer.

      • dimension is less than 1.

  • Example:

    isvector([0.2, 0.1, 0.4, 0.3], 4)   -- true
    isvector([0.2, 0.1, 0.3], 4)        -- false, wrong dimension
    isvector([0.2, 0.1, "s", 0.3], 4)   -- false, a string element is not numeric
    isvector(null, 4)                   -- null
    isvector(missing, 4)                -- missing
    isvector([0.2, 0.1, 0.4, 0.3], "4") -- null, with a warning, dimension isn't an integer