Vector / Similarity Functions

Pinot provides built-in vector similarity and utility functions for working with float array columns. These functions are useful for nearest-neighbor search, recommendation systems, and any use case involving embedding vectors.

Both input vectors must have the same number of dimensions. Passing null or mismatched-length vectors will result in an error.

cosineDistance

cosineDistance(vector1, vector2)
cosineDistance(vector1, vector2, defaultValue)

Returns the cosine distance between two vectors, defined as 1 - cosine_similarity. The result ranges from 0 (identical direction) to 2 (opposite direction). If either vector has a norm of zero, the two-argument form returns NaN while the three-argument form returns the specified defaultValue.

SELECT cosineDistance(
  embedding,
  ARRAY(0.1, 0.2, 0.3),
  0.0
)
FROM products
WHERE category = 'electronics'
-- Returns 0.0 for identical vectors, up to 2.0 for opposite vectors

innerProduct

innerProduct(vector1, vector2)

Returns the inner product (sum of element-wise products) of two vectors.

SELECT productName,
       innerProduct(embedding, ARRAY(0.5, 0.5, 0.5)) AS score
FROM products
ORDER BY score DESC
LIMIT 10

l1Distance

l1Distance(vector1, vector2)

Returns the L1 distance (Manhattan distance) between two vectors, computed as the sum of absolute differences of their components.

SELECT l1Distance(
  userEmbedding,
  ARRAY(1.0, 2.0, 3.0)
) AS manhattan_dist
FROM users
LIMIT 10

l2Distance

l2Distance(vector1, vector2)

Returns the L2 distance (Euclidean distance with square root) between two vectors.

SELECT l2Distance(
  embedding,
  ARRAY(0.1, 0.2, 0.3)
) AS dist
FROM products
ORDER BY dist ASC
LIMIT 5

euclideanDistance

euclideanDistance(vector1, vector2)

Returns the squared Euclidean distance between two vectors (the sum of squared differences without taking the square root). This is faster than l2Distance when you only need to compare relative distances.

SELECT euclideanDistance(
  embedding,
  ARRAY(0.1, 0.2, 0.3)
) AS squared_dist
FROM products
ORDER BY squared_dist ASC
LIMIT 5

dotProduct

dotProduct(vector1, vector2)

Returns the dot product of two vectors. Functionally equivalent to innerProduct.

SELECT dotProduct(
  queryEmbedding,
  docEmbedding
) AS relevance
FROM documents
ORDER BY relevance DESC
LIMIT 10

vectorDims

vectorDims(vector)

Returns the number of dimensions (length) of a vector.

SELECT vectorDims(embedding) AS dims
FROM products
LIMIT 1
-- Returns 128 for a 128-dimensional embedding

vectorNorm

vectorNorm(vector)

Returns the L2 norm (Euclidean length) of a vector, computed as the square root of the sum of squared components.

SELECT vectorNorm(embedding) AS norm
FROM products
LIMIT 5
-- Returns the magnitude of each embedding vector