kNN Search
k-nearest-neighbour (kNN) search functionality is a way to find entries based on their distance to a specific vector.
Function syntax
The general function syntax for kNN is: distance(<target column>, <vector to compare with>, <metric> [, <weights>])
.
KNN syntax
To express a k-Nearest-Neighbour search in SQL, you can combine the distance
function with a LIMIT
and ORDER BY
clause.
Examples
SELECT id, distance(arraycolumn, ARRAY[...], 'L2') as dist FROM tablewitharraycolumn ORDER BY dist ASC LIMIT 100;
SELECT id, distance(arraycolumn, ARRAY[...], 'L2', ARRAY[...]) as dist FROM tablewitharraycolumn ORDER BY dist ASC LIMIT 100;
Supported metrics
Currently the following metrics are supported:
- L1
- L2
- L2 squared
- Cosine
- ChiSquared