Model / similarity · All operations

Find similar rows

Row-to-row distance, nearest-neighbour search, vector search.

Status

In arcform

Runs as a SQL step

arcform carries it as a sql: step today.

Shape of the result

one table from two or more

Level

one clause of SQL

The DuckDB step

Cosine similarity between vectors

SQL

CREATE TEMP TABLE result AS
WITH v(id, e) AS (VALUES ('a', [1.0, 0.0, 1.0]), ('b', [0.9, 0.1, 1.0]), ('c', [0.0, 1.0, 0.0]))
SELECT x.id, y.id AS other, round(list_cosine_similarity(x.e, y.e), 4) AS cos FROM v x JOIN v y ON x.id < y.id ORDER BY cos DESC;
SELECT * FROM result;
SELECT CASE WHEN ((SELECT max(cos) FILTER (WHERE id = 'a' AND other = 'c') = 0 AND arg_max(other, cos) FILTER (WHERE id = 'a') = 'b' FROM result)) THEN 'as expected' ELSE error('the nearest vector to a is not b, or two orthogonal vectors do not score 0') END AS verdict;

The statement builds its own data.
It needs nothing installed beyond DuckDB itself.
The statement checks its own result and raises an error when it is wrong: “the nearest vector to a is not b, or two orthogonal vectors do not score 0”.
It passed on DuckDB v2.0.0 (Cyanoptera) d54cbbe3df.

Variants

Measure the distance between two values
Compute a distance, similarity or inner product between two vectors or strings in the same row.
Compute pairwise distances
Compute the distance or similarity between every pair of rows, as a matrix or a table of pairs.

Build a vector index

Build an index such as HNSW or a space-partitioning tree that speeds nearest-neighbour search.

Learn a distance metric

Learn a transform from labelled rows that makes nearest-neighbour matching more accurate.

Drop near-duplicate rows

Remove rows whose embeddings are within a similarity threshold of another row.

Find the nearest neighbours

For each query row or vector, return the k closest rows, or those within a distance.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Alteryx Designer (2)

AB Controlsread 2026-09-27
Match one to ten control units to each test unit on seasonality, trend and other measures
Find Nearest Neighborsread 2026-09-27
Find the k nearest records in a data stream for each query record by Euclidean distance

Apache Hop (1)

pgvector searchread 2026-09-27
Top-k vector similarity search against a PostgreSQL pgvector table

Apache SeaTunnel (1)

Vector Functionsread 2026-09-27
8 functions: VECTOR_DIMS, VECTOR_NORM, INNER_PRODUCT, COSINE_DISTANCE, L1_DISTANCE, …

Duckle (2)

Semantic Deduperead 2026-09-25
drop near-duplicate rows by cosine similarity of an embedding column
Vector Similarity Searchread 2026-09-25
rank rows by array distance to a query vector

Ibis (1)

Levenshteinread 2026-09-27
Edit distance between two strings

KNIME Analytics Platform (2)

KNIME Distance Matrix (extension)read 2026-09-27
Extension outside the base install: 22 current nodes: Aggregated Distance; Bit Vector Distances; Byte Vector Distances; DBSCAN, …
Random Forest Distanceread 2026-09-27
Derive a row-to-row distance measure from the proximities a random forest induces

mlpack (12)

Distancesread 2026-09-27
Distance metrics for the geometric algorithms
KFNread 2026-09-27
Exact or approximate k-furthest-neighbour search over points, using space trees
KNNread 2026-09-27
Exact or approximate k-nearest-neighbour search over points, using space trees
Kernelsread 2026-09-27
Mercer kernels for the kernel-based algorithms
LMNNread 2026-09-27
Large margin nearest neighbour: learn a linear map that improves k-NN on labelled rows
NCAread 2026-09-27
Neighbourhood components analysis: learn a distance that improves k-NN on labelled rows
Treesread 2026-09-27
23 space-partitioning tree types, among them KDTree, BallTree, CoverTree, RTree, Octree
mlpack_approx_kfnread 2026-09-27
Approximate furthest-neighbour search by two strategies; models can be saved and reused
mlpack_fastmksread 2026-09-27
Fast max-kernel search: for each query point, the reference points with the largest kernel value
mlpack_krannread 2026-09-27
Rank-approximate k-nearest-neighbour search with single-tree and dual-tree algorithms
mlpack_lshread 2026-09-27
Approximate k-nearest-neighbour search by locality-sensitive hashing
mlpack_range_searchread 2026-09-27
For each query point, find the reference points within a distance range

RapidMiner Studio (6)

Cross Distancesread 2026-09-27
Distance or similarity from each row of a request table to each row of a reference table
Data to Similarityread 2026-09-27
Compute a similarity measure object between all pairs of rows in a table
Data to Similarity Dataread 2026-09-27
Compute pairwise row similarities and return them as a table of pairs
Retrieve Documents (Milvus)read 2026-09-27
Retrieve the most similar documents from a Milvus collection by embedding and add them as columns
Retrieve Documents (Qdrant)read 2026-09-27
Retrieve the most similar documents from a Qdrant collection by embedding and add them as columns
Similarity to Dataread 2026-09-27
Turn a similarity measure object into a table of pairwise similarities

subtoken (14)

HNSW indexread 2026-09-27
CREATE INDEX … USING HNSW on a FLOAT[n] column; speeds ORDER BY distance LIMIT k; metrics l2sq, cosine, ip
array_cosine_distanceread 2026-09-27
Cosine distance of two fixed-size arrays of equal length; an HNSW index can serve it
array_cosine_similarityread 2026-09-27
Cosine similarity of two fixed-size arrays of equal length
array_distanceread 2026-09-27
Euclidean distance of two fixed-size arrays of equal length; an HNSW index can serve it
array_inner_productread 2026-09-27
Inner product of two fixed-size arrays of equal length
array_negative_inner_productread 2026-09-27
Negative inner product of two fixed-size arrays; an HNSW index can serve it
hnsw_index_scanread 2026-09-27
Internal table function the planner substitutes to scan an HNSW index
list_cosine_distanceread 2026-09-27
Cosine distance of two same-sized lists
list_cosine_similarityread 2026-09-27
Cosine similarity of two same-sized lists; works on subtoken's FLOAT[] with no cast
list_distanceread 2026-09-27
Euclidean distance of two same-sized lists
list_inner_productread 2026-09-27
Inner product of two same-sized lists
list_negative_inner_productread 2026-09-27
Negative inner product of two same-sized lists
vss_joinread 2026-09-27
Table macro joining two tables on the k nearest vectors of each left row, by a chosen metric
vss_matchread 2026-09-27
Table macro for a lateral join that returns each left row's k nearest matches already grouped

Talend (1)

tSemanticMatchread 2026-09-27
Search a vector database for records similar to a query column and split results by score threshold

Yggdrasil Decision Forests (1)

distanceread 2026-09-27
Pairwise distance between rows of two datasets, from how often they share leaves

Background

Nearest neighbour search finds the point in a given set that is closest, or most similar, to a given point.^[1] In nearest neighbour search, closeness is typically expressed by a dissimilarity function, whose values grow larger the less similar two objects are.^[1] A k-nearest neighbour search generalises the problem to finding the k closest points rather than the single closest one.^[1]

When to use it

Nearest neighbour search arises in many fields, among them statistical classification, recommendation systems, spell checking and plagiarism detection.^[1] Vector databases use nearest neighbour lookup to retrieve semantically similar records.^[1] k-nearest neighbour search is commonly used in predictive analytics to estimate or classify a point from the consensus of its neighbours.^[1]

An approximate nearest neighbour algorithm does not guarantee to return the actual nearest neighbour in every case, in return for improved speed or memory savings.^[1] How often an approximate nearest neighbour algorithm finds the true nearest neighbour depends strongly on the dataset being queried.^[1] Where a wrong match costs something, check an approximate search against an exhaustive one on a sample of your own data before trusting it. (Meridian's view)

In high-dimensional spaces, tree indexing structures lose their value, because an increasing share of their nodes has to be examined anyway.^[1] A naive search that compares the query with every point can, on average, outperform space-partitioning approaches on higher-dimensional data.^[1]

The method

Donald Knuth called nearest neighbour search the post-office problem in volume 3 of The Art of Computer Programming (1973).^[1] The simplest exact method, linear search, computes the distance from the query point to every other point, keeping track of the best so far.^[1] Linear search has a running time of O(dN), where N is the number of points in the set and d is their dimensionality.^[1] Only relative distances matter when ranking neighbours, so in geometric coordinates a search can omit the square root from each distance and still give identical results.^[1]

Since the 1970s, branch and bound methods have been applied to nearest neighbour search, among them spatial indexes for Euclidean space.^[1] The k-d tree, perhaps the simplest space-partitioning method, repeatedly bisects the search space into two regions, each holding half the points of its parent.^[1] Locality-sensitive hashing (LSH) groups points into buckets so that points close to each other under the chosen metric land in the same bucket with high probability.^[1] Proximity graph methods such as hierarchical navigable small world (HNSW) graphs are considered the current state of the art for approximate nearest neighbour search.^[1]^[2]

HNSW stores vectors as nodes of a layered graph, in which the upper layers hold fewer nodes and the bottom layer holds every node.^[2] An HNSW search starts in an upper layer, follows links toward nodes closer to the query, and repeats the process in lower layers until it finds a set of likely nearest neighbours.^[2] Because HNSW is approximate, its results are not always identical to a full exact search.^[2]

DuckDB's vector similarity search extension, vss, adds HNSW indexes on columns of DuckDB's fixed-size ARRAY type.^[3] DuckDB's HNSW index accelerates queries that ORDER BY a supported distance function between the indexed column and a constant vector, followed by a LIMIT clause.^[3] DuckDB's HNSW index uses the squared Euclidean metric, l2sq, by default, and a metric option at index creation selects cosine or negative inner product instead.^[3] In DuckDB, an HNSW index can only be created on tables in in-memory databases unless the experimental setting hnsw_enable_experimental_persistence is turned on.^[3] In DuckDB's HNSW index, deleted rows are only marked as deleted, which can leave the index stale over time and harm query quality and performance.^[3] DuckDB's vss_join and vss_match table macros match vectors by brute force and do not use the HNSW index.^[3]

Compared with the alternatives

Fixed-radius near neighbours is a related problem: finding all points within a given fixed distance of a query point.^[1] In cluster analysis, how similarity between data points is measured is one of the fundamental aspects of the method.^[4] Nearest neighbour search answers which rows are closest to this one, while clustering answers which rows belong together; reach for the first when you have a query row in hand. (Meridian's view)

Sources

[1]Nearest neighbor search - Wikipediasecondary source
[2]Hierarchical navigable small world - Wikipediasecondary source
[3]Vector Similarity Search Extension – DuckDBdocumentation
[4]Similarity measure - Wikipediasecondary source