Welcome to the new DOCS.vastdata.com. We've added new menus and a version selector, plus new persona and resource pages to help customers get started. Looking for a previous article? Use the docs search.

Press CTRL+K to open search

array_cosine_distance

Prev Next

Applies to VAST Cluster 5.5.

Functions › Vector search SQL › Distance functions. Returns the cosine distance between two vectors: 1 minus the cosine of the angle between them. It compares directions and ignores length: 0 means the same direction, 1 a right angle, 2 opposite directions. A smaller result means more similar.

Syntax

array_cosine_distance(a, b)
Argument Type Description
a, b FLOAT[n], DOUBLE[n] or HALF[n] fixed-size arrays of the same n, or a VECTOR(FLOAT, n) column The two vectors.

Returns: FLOAT; DOUBLE if either argument is a DOUBLE array.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (ADBC), and work in a schema called docs_sql_fn_vector_array_cosine_distance. Set QE_ENDPOINT, AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY and VASTDB_BUCKET (SQL reference), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the run() and plan() helpers, and creates a table of six documents, each with a 3-dimensional embedding in a VECTOR(FLOAT, 3) column and the same values in a plain list column, raw. Document 2's embedding is all zeros, and document 3 has none:

import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_vector_array_cosine_distance"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line).replace(BUCKET, "<bucket>"))

TABLE = f"{SCHEMA}.docs"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, title VARCHAR, e VECTOR(FLOAT, 3), raw FLOAT[])")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, 'same', [1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]), "
    "(2, 'zero', [0.0, 0.0, 0.0]::FLOAT[3], [0.0, 0.0, 0.0]), "
    "(3, 'none', NULL, NULL), "
    "(4, 'opposite', [-1.0, -2.0, -3.0]::FLOAT[3], [-1.0, -2.0, -3.0]), "
    "(5, 'axis', [1.0, 0.0, 0.0]::FLOAT[3], [1.0, 0.0, 0.0]), "
    "(6, 'three-four', [3.0, 4.0, 0.0]::FLOAT[3], [3.0, 4.0, 0.0])")

[]
[]
[(6,)]

What it does

array_cosine_distance(a, b) is 1 minus (a · b) / (|a| |b|): 1 minus the dot product divided by the product of the two lengths. Only the direction of each vector counts, so a vector and a multiple of it are 0.0 apart:

Call Result
array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]) 0.0 (same direction)
array_cosine_distance([1.0, 0.0]::FLOAT[2], [0.0, 1.0]::FLOAT[2]) 1.0 (a right angle)
array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]) 2.0 (opposite)

Both arguments must be fixed-size arrays of floating-point values with the same number of elements. A VECTOR(FLOAT, n) column is one already, so pass it as it is. Write a query vector as a list literal cast to the array type, [1.0, 1.0, 1.0]::FLOAT[3]. A list column (FLOAT[]) must be cast to FLOAT[n] first.

With two FLOAT arrays the result is a FLOAT. If either argument is a DOUBLE array, the result is a DOUBLE. A HALF array gives a FLOAT.

Zero vectors. A vector of zeros has no direction. array_cosine_distance returns 2.0 when either vector is all zeros, the same as for opposite vectors, so a zero vector is never close to anything, not even to itself.

NULLs and edge cases

Input Result
A NULL vector (either argument) NULL
Two equal vectors 0.0
A zero vector, such as [3.0, 4.0] against [0.0, 0.0], or two zero vectors 2.0
One-element vectors [1.0] and [2.0] 0.0 (same direction)
A NULL element, such as [1.0, NULL, 3.0]::FLOAT[3] Error: array_cosine_distance: left argument can not contain NULL values (or right argument)
Arrays of different sizes Error: Binder Error: array_cosine_distance: Array arguments must be of the same size
An uncast list literal, such as [1.0, 2.0, 3.0] Error: No function matches the given name and argument types 'array_cosine_distance(DECIMAL(2,1)[], DECIMAL(2,1)[])'
array_cosine_distance(NULL, NULL) Error: Could not choose a best candidate function; give the NULL a type, such as NULL::FLOAT[3]

The same cases, as the cluster returns them. They use the table and helpers from Set up the examples:

run("SELECT array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], NULL), array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3]), "
    "array_cosine_distance([3.0, 4.0]::FLOAT[2], [0.0, 0.0]::FLOAT[2]), array_cosine_distance([0.0, 0.0]::FLOAT[2], [0.0, 0.0]::FLOAT[2]), "
    "array_cosine_distance([1.0]::FLOAT[1], [2.0]::FLOAT[1])")
run("SELECT array_cosine_distance([1.0, NULL, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3])")
run("SELECT array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0, 4.0]::FLOAT[4])")
run("SELECT array_cosine_distance([1.0, 2.0, 3.0], [1.0, 2.0, 3.0])")
run("SELECT array_cosine_distance(NULL, NULL)")

[(None, 0.0, 2.0, 2.0, 0.0)]
OSError: array_cosine_distance: left argument can not contain NULL values
InternalError: Binder Error: array_cosine_distance: Array arguments must be of the same size
InternalError: Binder Error: No function matches the given name and argument types 'array_cosine_distance(DECIMAL(2,1)[], DECIMAL(2,1)[])'. You might need to add explicit type casts.
InternalError: Binder Error: Could not choose a best candidate function for the function call "array_cosine_distance("NULL", "NULL")". In order to select one, please add explicit type casts.

Examples

The cosine distance of each document's embedding from a query vector. Document 2 (all zeros) gets 2.0, and document 3 (no embedding) gets NULL:

run(f"SELECT id, title, array_cosine_distance(e, [1.0, 1.0, 1.0]::FLOAT[3]) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 'same', 0.07417988777160645), (2, 'zero', 2.0), (3, 'none', None), (4, 'opposite', 1.9258201122283936), (5, 'axis', 0.4226497411727905), (6, 'three-four', 0.19170963764190674)]

The three documents that point most nearly the same way as the query vector, most similar first. ORDER BY on the distance needs a LIMIT:

run(f"SELECT id, title, array_cosine_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS dist "
    f"FROM {TABLE} WHERE e IS NOT NULL ORDER BY dist LIMIT 3")

[(1, 'same', 0.07417988777160645), (6, 'three-four', 0.19170963764190674), (5, 'axis', 0.4226497411727905)]

The documents within a cosine distance of 0.5 of the query vector:

run(f"SELECT id, title FROM {TABLE} WHERE array_cosine_distance(e, [1.0, 1.0, 1.0]::FLOAT[3]) < 0.5 ORDER BY id LIMIT 10")

[(1, 'same'), (5, 'axis'), (6, 'three-four')]

The same distance from the list column, cast to a fixed-size array, as FLOAT and as DOUBLE:

run(f"SELECT id, array_cosine_distance(raw::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]), "
    f"array_cosine_distance(raw::DOUBLE[3], [1.0, 1.0, 1.0]::DOUBLE[3]) FROM {TABLE} ORDER BY id LIMIT 10")

[(1, 0.07417988777160645, 0.07417990022744858), (2, 2.0, 2.0), (3, None, None), (4, 1.9258201122283936, 1.9258200997725514), (5, 0.4226497411727905, 0.42264973081037416), (6, 0.19170963764190674, 0.191709623134524)]

How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (VAST_SCAN). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column, so array_cosine_distance() is always computed by the Query Engine:

  • In the SELECT list, the scan reads the vector column and the Query Engine computes the distance for each row it returns.
  • In WHERE, the scan sends the vector column for every row, and a FILTER step computes the distance and keeps the rows that match.
  • In ORDER BY ... LIMIT k, a TOP_N step computes the distance for the rows the scan sends and keeps the k smallest.

The plan of the most-similar example shows the TOP_N step above the scan, which reads id and e:

plan(f"SELECT id, array_cosine_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS dist FROM {TABLE} ORDER BY dist LIMIT 3")

┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 3          │
│                           │
│         Order By:         │
│ array_cosine_distance(CAST│
│      (vastdb."<bucket>      │
│/docs_sql_fn_vector_array_c│
│ osine_distance".docs.e AS │
│    FLOAT[3]), CAST(main   │
│ .list_value(1.0, 1.0, 1.0)│
│      AS FLOAT[3])) ASC    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│            dist           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│             e             │
│                           │
└───────────────────────────┘

For tables with a vector index: which index metric goes with array_cosine_distance, and the query shape that uses the index, are in the VAST Vector Search section: distance functions and query shape that uses the index.

Make it faster

  • Search large tables through a vector index. Create the table with a vector index and write the query in the shape the index uses; see query design.
  • Add pushed-down conditions. A comparison of a column with a constant, such as title <> 'zero', can be evaluated in VAST DataBase (pushdowns), so only the rows that pass it reach the distance computation. The plan has no FILTER step for it:
run(f"SELECT id, array_cosine_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS dist "
    f"FROM {TABLE} WHERE title <> 'zero' ORDER BY dist LIMIT 3")
plan(f"SELECT id, array_cosine_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3]) AS dist "
     f"FROM {TABLE} WHERE title <> 'zero' ORDER BY dist LIMIT 3")

[(1, 0.07417988777160645), (6, 0.19170963764190674), (5, 0.4226497411727905)]
┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 3          │
│                           │
│         Order By:         │
│ array_cosine_distance(CAST│
│      (vastdb."<bucket>      │
│/docs_sql_fn_vector_array_c│
│ osine_distance".docs.e AS │
│    FLOAT[3]), CAST(main   │
│ .list_value(1.0, 1.0, 1.0)│
│      AS FLOAT[3])) ASC    │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             id            │
│            dist           │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             id            │
│             e             │
│                           │
└───────────────────────────┘

Limitations and errors

  • Fixed-size arrays only. A list, such as a FLOAT[] column or an uncast literal, fails with No function matches the given name and argument types. Cast it: raw::FLOAT[3].
  • The dimension must match. Arrays of different sizes fail, and so does casting a vector to another size.
  • No NULL elements. A vector with a NULL element fails; a NULL vector gives NULL.
  • Floating-point arrays only. Two integer arrays fail with Binder Error: array_cosine_distance: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE.
  • Zero vectors give 2.0. See What it does. With FLOAT arrays, very small values, such as 1e-30, gave 2.0 too.
  • ORDER BY needs a LIMIT. Without one it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.
  • Other SQL dialects' names aren't available. cosine_distance fails with Scalar Function with name cosine_distance does not exist!.
run(f"SELECT id, array_cosine_distance(raw, [1.0, 1.0, 1.0]::FLOAT[3]) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT array_cosine_distance([1, 2, 3]::INTEGER[3], [1, 2, 3]::INTEGER[3])")
run("SELECT array_cosine_distance([1e-30, 1e-30]::FLOAT[2], [2e-30, 2e-30]::FLOAT[2])")
run(f"SELECT id FROM {TABLE} ORDER BY array_cosine_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3])")
run("SELECT cosine_distance([1.0, 2.0]::FLOAT[2], [1.0, 2.0]::FLOAT[2])")

InternalError: Binder Error: No function matches the given name and argument types 'array_cosine_distance(FLOAT[], FLOAT[3])'. You might need to add explicit type casts.
InternalError: Binder Error: array_cosine_distance: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE
[(2.0,)]
InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
InternalError: Catalog Error: Scalar Function with name cosine_distance does not exist!

Similar functions

Function Measures [1, 2, 3] and [2, 4, 6] (same direction) [1, 2, 3] and [-1, -2, -3] (opposite)
array_cosine_distance the angle between the vectors, not their length 0.0 2.0
array_distance straight-line distance 3.7416574954986572 7.4833149909973145
array_negative_inner_product minus the dot product -28.0 14.0

For all three, a smaller result means closer or more similar.

run("SELECT array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3])")
run("SELECT array_cosine_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_negative_inner_product([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3])")

[(0.0, 3.7416574954986572, -28.0)]
[(2.0, 7.4833149909973145, 14.0)]

Clean up:

run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()

[]
[]

See also

Related articles
  • array_distance
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Vector distance functions
  • array_negative_inner_product
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Vector distance functions
  • acos
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > Numeric and math functions