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
VTREEindex 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
VTREEvector index to prune the search to a subset of clusters, trading a small amount of recall for query speed. For more information about creating aVTREEindex, 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
vector1andvector2. Unlike a distance function, a greatercosine_similarityvalue indicates a closer match.cosine_similarityis not eligible for aVTREEindex. Useann_distance(vector_field, query_vector, "cosine")for an indexed search, orcosine_distancefor an exact ranking. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is 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
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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.
-
vector1orvector2has 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
1minus the cosine similarity betweenvector1andvector2, giving the angular distance between their directions. A value of0means the vectors point in the same direction, and a value of2means they point in opposite directions. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is 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
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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.
-
vector1orvector2has 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
vector1andvector2. Unlike a distance function, a greaterdot_productvalue indicates a closer match.dot_productis not eligible for aVTREEindex. Useann_distance(vector_field, query_vector, "dot")for an indexed search, ordot_distancefor an exact ranking. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is the second vector, expressed as an array of floating point numbers.
-
-
Return Value:
-
A floating point number representing the dot product of
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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
vector1andvector2, so that a smaller value indicates a closer match. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is the second vector, expressed as an array of floating point numbers.
-
-
Return Value:
-
A floating point number representing the negated dot product of
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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
vector1andvector2.L2_DISTANCEis an alias forEUCLIDEAN_DISTANCE. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is the second vector, expressed as an array of floating point numbers.
-
-
Return Value:
-
A floating point number representing the Euclidean distance between
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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
vector1andvector2. Squared Euclidean distance omits the final square root calculation, so it’s cheaper to compute thaneuclidean_distancewhile preserving the same distance ordering.L2_SQUARED_DISTANCEis an alias forEUCLIDEAN_SQUARED_DISTANCE. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is the second vector, expressed as an array of floating point numbers.
-
-
Return Value:
-
A floating point number representing the squared Euclidean distance between
vector1andvector2. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
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
vector1andvector2. Usevector_distancefor an exact K-nearest neighbor (KNN) result, or when no applicableVTREEindex exists. -
Arguments:
-
vector1is the first vector, expressed as an array of floating point numbers. -
vector2is the second vector, expressed as an array of floating point numbers. -
distance_functionis a string literal specifying the distance metric:cosine,dot,l2oreuclidean, orl2_squaredoreuclidean_squared. Forcosine, the result is1minus the cosine similarity. Fordot, 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
vector1andvector2, according todistance_function. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
An error at compile time, if
distance_functionis 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_fieldandquery_vectorwhen an eligibleVTREEindex exists and applies to the query. Otherwise, computes the exact distance.APPROX_VECTOR_DISTANCEis an alias forANN_DISTANCE. -
Arguments:
-
vector_fieldis a reference to the vector field to search. It must match the vector field declared when the index was created. -
query_vectoris the query vector, expressed as an array of floating point numbers. -
distance_functionis a string literal specifying the distance metric. It must match thesimilarityparameter used when aVTREEindex was created in order for the index to be used. Otherwise, the query falls back to exact KNN evaluation. SQL++ for Operational Insights Cloud supportsdotsimilarity only for normalized embeddings in this release. Forcosine, the result is1minus the cosine similarity. Fordot, the result is the negated dot product. For every metric, a smaller value indicates a closer match. -
min_probe_fractionis 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 pass0, Operational Insights uses the index’s default probe behavior.
Default:0.75 -
k_multiplieris optional. It must be a numeric literal. It’s an integer that sets how many candidates, expressed asktimes 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_fieldandquery_vector, according todistance_function. -
missing, if either vector argument ismissing. -
null, if either vector argument isnull. -
An error at compile time, if
distance_functionis 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
VTREEindex only when all of the following are true:-
An eligible
VTREEindex exists onvector_field. -
distance_functionmatches the index’ssimilarity. -
The query is a top-k query: it uses
ORDER BYon the distance in ascending order, with no additional sort key other thanNULLS LAST, followed byLIMIT k. -
LIMIT khas noOFFSET. -
If present, a
WHEREpredicate references only fields in the index’sINCLUDElist and contains no non-deterministic function, such asrandom(). -
No
UNNESTappears between the collection and theORDER 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_vectoris a constant whose length differs from the index’sdimension, 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
vecscaled to a length of 1, keeping the same direction. Usenormalize_vectorto compute the unit-length (L2-normalized) form of a vector required by thecosineanddotmetrics, without performing the division yourself.NORMALISE_VECTOR,VECTOR_NORMALISE, andVECTOR_NORMALIZEare aliases forNORMALIZE_VECTOR. -
Arguments:
-
vecis 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, ifvecismissing. -
null, ifvecisnull. -
null, with a warning, if any of the following is true:-
vecis not an array. -
vecis an empty array. -
veccontains a non-numeric element. -
veccontains an infinite element. -
vechas 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
vecis a usable vector, meaning a non-empty array ofdimensionnumeric elements. A record whose vector field satisfiesisvectoris eligible to be indexed with aVTREEindex.IS_VECTORis an alias forISVECTOR. -
Arguments:
-
vecis any value to test. -
dimensionis an integer specifying the required number of elements.
-
-
Return Value:
-
true, ifvecis an array whose elements are all numeric values, with exactlydimensionelements. -
false, ifvecis a non-null, non-missingvalue that’s not an array, is an empty array, or contains a non-numeric element.falsealso results when the element count does not matchdimension. -
missing, if either argument ismissing. -
null, if either argument isnull. -
null, with a warning, if any of the following is true:-
dimensionis not an integer. -
dimensionis 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