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_distance

Prev Next

Applies to VAST Cluster 5.5.

Functions › Vector search SQL › Distance functions. Returns the Euclidean distance between two vectors: the square root of the sum of the squared differences of their elements. 0 means the vectors are equal; a smaller result means closer.

Syntax

array_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_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 3 has no embedding:

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_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_distance(a, b) is the straight-line (L2) distance between a and b, the square root of (a1 - b1)² + (a2 - b2)² + …. The result isn't squared: [3.0, 4.0] and [0.0, 0.0] are 5.0 apart, not 25.0.

Call Result
array_distance([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3]) 0.0
array_distance([3.0, 4.0]::FLOAT[2], [0.0, 0.0]::FLOAT[2]) 5.0
array_distance([1.0, 0.0]::FLOAT[2], [0.0, 1.0]::FLOAT[2]) 1.4142135381698608 (√2 as a FLOAT)
array_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]) 3.7416574954986572

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 string such as '[1, 2, 3]'::FLOAT[3] works too. 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, computed with more precision. A HALF array gives a FLOAT, and an integer array is accepted as long as the other argument is a floating-point array.

NULLs and edge cases

Input Result
A NULL vector (either argument) NULL
Two equal vectors, or two zero vectors 0.0
Very large FLOAT values, such as 1e38 against -1e38 inf; with DOUBLE arrays, 3.464101615137754e+38
A NULL element, such as [1.0, NULL, 3.0]::FLOAT[3] Error: array_distance: left argument can not contain NULL values (or right argument)
Arrays of different sizes Error: Binder Error: array_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_distance(DECIMAL(2,1)[], DECIMAL(2,1)[])'
array_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_distance([1.0, 2.0, 3.0]::FLOAT[3], NULL), array_distance(NULL::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_distance([0.0, 0.0]::FLOAT[2], [0.0, 0.0]::FLOAT[2])")
run("SELECT array_distance([1e38, 1e38, 1e38]::FLOAT[3], [-1e38, -1e38, -1e38]::FLOAT[3]), "
    "array_distance([1e38, 1e38, 1e38]::DOUBLE[3], [-1e38, -1e38, -1e38]::DOUBLE[3])")
run("SELECT array_distance([1.0, NULL, 3.0]::FLOAT[3], [1.0, 2.0, 3.0]::FLOAT[3])")
run("SELECT array_distance([1.0, 2.0, 3.0]::FLOAT[3], [1.0, 2.0, 3.0, 4.0]::FLOAT[4])")
run("SELECT array_distance([1.0, 2.0, 3.0], [1.0, 2.0, 3.0])")
run("SELECT array_distance(NULL, NULL)")

[(None, None, 0.0, 0.0)]
[(inf, 3.464101615137754e+38)]
OSError: array_distance: left argument can not contain NULL values
InternalError: Binder Error: array_distance: Array arguments must be of the same size
InternalError: Binder Error: No function matches the given name and argument types 'array_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_distance("NULL", "NULL")". In order to select one, please add explicit type casts.

Examples

The distance of each document's embedding from a query vector. Document 3 has no embedding, so its distance is NULL:

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

[(1, 'same', 2.2360680103302), (2, 'zero', 1.7320507764816284), (3, 'none', None), (4, 'opposite', 5.385164737701416), (5, 'axis', 1.4142135381698608), (6, 'three-four', 3.7416574954986572)]

The three documents nearest to the query vector, nearest first. ORDER BY on the distance needs a LIMIT:

run(f"SELECT id, title, array_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")

[(5, 'axis', 1.4142135381698608), (2, 'zero', 1.7320507764816284), (1, 'same', 2.2360680103302)]

The documents within a distance of 2 of the query vector:

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

[(2, 'zero'), (5, 'axis')]

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

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

[(1, 2.2360680103302, 2.23606797749979), (2, 1.7320507764816284, 1.7320508075688772), (3, None, None), (4, 5.385164737701416, 5.385164807134504), (5, 1.4142135381698608, 1.4142135623730951), (6, 3.7416574954986572, 3.7416573867739413)]

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_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 nearest-documents example shows the TOP_N step above the scan, which reads id and e:

plan(f"SELECT id, array_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_distance(CAST(vastdb│
│          ."<bucket>         │
│/docs_sql_fn_vector_array_d│
│ istance".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_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_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_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")

[(5, 1.4142135381698608), (1, 2.2360680103302), (6, 3.7416574954986572)]
┌───────────────────────────┐
│           TOP_N           │
│    ────────────────────   │
│           Top: 3          │
│                           │
│         Order By:         │
│ array_distance(CAST(vastdb│
│          ."<bucket>         │
│/docs_sql_fn_vector_array_d│
│ istance".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             │
│                           │
└───────────────────────────┘
  • Select only the columns you need. The scan reads only the columns the query names, as the Projections of the plan above show.

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]. A table column can't be declared as FLOAT[3] (ARRAY type is not supported in table definitions); use VECTOR(FLOAT, n) or a list column.
  • The dimension must match. Casting a vector or list to another size fails, with Cannot cast array of size 3 to array of size 4 or Cannot cast list with length 3 to array with length 4.
  • 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_distance: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE.
  • FLOAT can overflow. Very large values give inf; cast both arguments to DOUBLE[n].
  • 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. l2_distance, euclidean_distance and array_l2_distance fail with Scalar Function with name ... does not exist!.
run(f"SELECT id, array_distance(raw, [1.0, 1.0, 1.0]::FLOAT[3]) FROM {TABLE} ORDER BY id LIMIT 10")
run(f"SELECT id, array_distance(e::FLOAT[4], [1.0, 1.0, 1.0, 1.0]::FLOAT[4]) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT array_distance([1, 2, 3]::INTEGER[3], [1, 2, 3]::INTEGER[3])")
run(f"SELECT id FROM {TABLE} ORDER BY array_distance(e::FLOAT[3], [1.0, 1.0, 1.0]::FLOAT[3])")
run("SELECT l2_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_distance(FLOAT[], FLOAT[3])'. You might need to add explicit type casts.
OSError: Cannot cast array of size 3 to array of size 4
InternalError: Binder Error: array_distance: Arguments must be arrays of FLOAT16, FLOAT or DOUBLE
InternalError: The operator 'ORDER_BY' type=1 is currently not supported for this usage.
InternalError: Catalog Error: Scalar Function with name l2_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_distance straight-line distance 3.7416574954986572 7.4833149909973145
array_cosine_distance the angle between the vectors (1 minus the cosine), not their length 0.0 2.0
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_distance([1.0, 2.0, 3.0]::FLOAT[3], [2.0, 4.0, 6.0]::FLOAT[3]), "
    "array_cosine_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_distance([1.0, 2.0, 3.0]::FLOAT[3], [-1.0, -2.0, -3.0]::FLOAT[3]), "
    "array_cosine_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])")

[(3.7416574954986572, 0.0, -28.0)]
[(7.4833149909973145, 2.0, 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_cosine_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
  • array_length
    Developer Guide > VAST DataBase > Reference > SQL reference > Functions > List functions