# Vector search in BigQuery

> VECTOR_SEARCH finds the nearest embeddings in a table by Euclidean, cosine or dot product distance. Without an index it compares every row exactly. IVF and TreeAH indexes make it approximate, and on a small table they can cost more than brute force.

Source: https://docs.querylab.io/bigquery/vector-search/

Updated: 2026-10-06

`VECTOR_SEARCH` takes a table with an `ARRAY<FLOAT64>` embedding column and a query vector, and returns the `top_k` rows whose vectors are closest. With no vector index it measures the distance to every row, so the answer is exact and the query reads the whole embedding column. With an IVF or TreeAH index it searches only some of the data, the answer becomes approximate, and whether that is cheaper depends on table size and how you set it up.

## What the function returns

The base table must be a table, not a view or a `WITH` name. The query side can be a table, a query, or a single array through `query_value`. The examples here use two-dimensional vectors so you can check the distances by hand. Real embeddings have hundreds of dimensions.

**two query rows, two neighbors each**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT query.q AS q, base.id AS neighbor, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(
  TABLE animals, 'emb',
  (SELECT 'cat' AS q, [1.0, -1.0] AS emb UNION ALL SELECT 'dog', [1.0, 2.0]),
  top_k => 2)
ORDER BY q, distance
```

Result in BigQuery (4 rows):

| q | neighbor | distance |
| --- | --- | --- |
| cat | lion | 1.803 |
| cat | tiger | 2.236 |
| dog | dog | 0.0 |
| dog | wolf | 2.236 |

The output has a `query` struct (the query row, only for the batch form), a `base` struct (the matched row) and a `distance` column. Google's reference says the order of the output isn't guaranteed, so add `ORDER BY`. `top_k` defaults to 10, and a negative value returns every row:

**top_k left at its default**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

-- top_k defaults to 10, so this returns all 6 rows; negative top_k returns every row too
SELECT base.id AS neighbor, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [0.0, 0.0])
ORDER BY distance, neighbor
```

Result in BigQuery (6 rows):

| neighbor | distance |
| --- | --- |
| dog | 2.236 |
| lion | 3.202 |
| snake | 3.606 |
| tiger | 3.606 |
| wolf | 4.472 |
| whale | 5.099 |

## Distance types

`distance_type` is `EUCLIDEAN` (default), `COSINE` or `DOT_PRODUCT`. Smaller is always closer, which has a consequence for dot product: the function returns the negated dot product, so the best match has the most negative distance.

**the same query under three metrics**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT metric, base.id AS neighbor, distance
FROM (
  SELECT 'EUCLIDEAN' AS metric, * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'EUCLIDEAN')
  UNION ALL
  SELECT 'COSINE', * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'COSINE')
  UNION ALL
  SELECT 'DOT_PRODUCT', * FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, distance_type => 'DOT_PRODUCT')
)
ORDER BY metric, distance, neighbor
```

Result in BigQuery (9 rows):

| metric | neighbor | distance |
| --- | --- | --- |
| COSINE | dog | 0.0 |
| COSINE | wolf | 0.0 |
| COSINE | snake | 0.5038610616431661 |
| DOT_PRODUCT | wolf | -10.0 |
| DOT_PRODUCT | dog | -5.0 |
| DOT_PRODUCT | snake | -4.0 |
| EUCLIDEAN | dog | 0.0 |
| EUCLIDEAN | wolf | 2.23606797749979 |
| EUCLIDEAN | snake | 3.1622776601683795 |

For the query `[1, 2]`, `wolf` is `[2, 4]`, twice as long and in the same direction. Cosine distance puts `dog` and `wolf` at 0. Euclidean puts `wolf` 2.24 away. Dot product ranks `wolf` first, at -10, because it is longer. Which one you want depends on how your embedding model was trained. Cosine ignores length, and most text embedding models are meant to be compared that way.

The same difference is visible without `VECTOR_SEARCH`, with plain functions:

**length changes Euclidean distance, not cosine distance**

```sql
-- magnitude matters to Euclidean distance but not to cosine distance
WITH docs AS (
  SELECT 'short note' AS id, [1.0, 1.0] AS emb
  UNION ALL SELECT 'long report, same topic', [10.0, 10.0]
  UNION ALL SELECT 'other topic', [1.0, 0.0]
)
SELECT id,
  ROUND(EUCLIDEAN_DISTANCE(emb, [2.0, 2.0]), 3) AS euclidean,
  ROUND(COSINE_DISTANCE(emb, [2.0, 2.0]), 3) AS cosine
FROM docs
ORDER BY id
```

Result in BigQuery (3 rows):

| id | euclidean | cosine |
| --- | --- | --- |
| long report, same topic | 11.314 | 0.0 |
| other topic | 2.236 | 0.293 |
| short note | 1.414 | 0.0 |

`EUCLIDEAN_DISTANCE`, `COSINE_DISTANCE` and `ML.DISTANCE(a, b, type)` compute the distance between two arrays in a normal query, for example to re-rank results or compare two columns:

**distance functions on literals**

```sql
SELECT
  EUCLIDEAN_DISTANCE([1.0, 2.0], [4.0, 6.0]) AS euclidean,
  ML.DISTANCE([1.0, 2.0], [4.0, 6.0], 'MANHATTAN') AS manhattan,
  ROUND(COSINE_DISTANCE([1.0, 0.0], [0.0, 1.0]), 3) AS cosine_orthogonal,
  ROUND(COSINE_DISTANCE([1.0, 0.0], [-1.0, 0.0]), 3) AS cosine_opposite,
  ROUND(COSINE_DISTANCE([1.0, 2.0], [10.0, 20.0]), 3) AS cosine_same_direction
```

Result in BigQuery (1 row):

| euclidean | manhattan | cosine_orthogonal | cosine_opposite | cosine_same_direction |
| --- | --- | --- | --- | --- |
| 5.0 | 7.0 | 1.0 | 2.0 | 0.0 |

`ML.DISTANCE` is documented for `EUCLIDEAN`, `MANHATTAN` and `COSINE`. In our test it also accepted `'DOT_PRODUCT'` and returned the positive dot product (16 for `[1, 2]` and `[4, 6]`), unlike `VECTOR_SEARCH`, which negates it. Don't mix the two without checking the sign.

## Filtering before or after the search

If the base table argument is a query, its `WHERE` clause runs before the search. A `WHERE` on the outer query runs after. The two give different answers when the filter removes some of the nearest rows:

**filter inside the base query**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT base.id AS neighbor, base.kind, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(
  (SELECT * FROM animals WHERE kind = 'mammal'), 'emb',
  query_value => [-2.0, 2.0], top_k => 2)
ORDER BY distance
```

Result in BigQuery (2 rows):

| neighbor | kind | distance |
| --- | --- | --- |
| dog | mammal | 3.0 |
| whale | mammal | 4.243 |

**filter outside, after top_k**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

-- the filter runs after the top 2 are chosen: the snake is nearest, a mammal filter leaves one row
SELECT base.id AS neighbor, base.kind, ROUND(distance, 3) AS distance
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [-2.0, 2.0], top_k => 2)
WHERE base.kind = 'mammal'
ORDER BY distance
```

Result in BigQuery (1 row):

| neighbor | kind | distance |
| --- | --- | --- |
| dog | mammal | 3.0 |

The post-filter gets only one row because the top 2 were the snake and the dog, and the snake isn't a mammal. Only `SELECT`, `FROM` and `WHERE` are allowed in the base query, and Google advises against filtering on the embedding column itself. With an index, a pre-filter on columns that are stored in the index (`STORING (...)` in `CREATE VECTOR INDEX`) is applied before the index search. A filter on any other column is applied afterwards, and Google notes the result can then contain fewer than `top_k` rows, even none.

## Bad vectors fail the whole query

Every vector has to have the same length, and the error comes from `ML.DISTANCE`, which `VECTOR_SEARCH` uses underneath:

**a 3 number query against 2 number embeddings**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, [1.0, 2.0] AS emb UNION ALL SELECT 'wolf', [2.0, 4.0];

SELECT base.id
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0, 3.0], top_k => 2)
```

BigQuery rejects this query:

```text
Query error: Array inputs are not equal in length; error in ML.DISTANCE expression at [4:1]
```

A row whose embedding is `NULL` fails in the same way, with the same message about array lengths, not a message about NULL:

**one NULL embedding in the base table**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', [2.0, 4.0]
UNION ALL SELECT 'ghost', CAST(NULL AS ARRAY<FLOAT64>);

SELECT base.id
FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 5)
```

BigQuery rejects this query:

```text
Query error: Array inputs are not equal in length; error in ML.DISTANCE expression at [6:1]
```

A `NULL` element inside an array (`[1.0, NULL]`) can't be stored in a table ("Array cannot have a null element") and is rejected by the distance functions ("Unexpected NULL element in input array"). Filter out failed embeddings before you search, or before you build an index. Cosine distance against a zero vector is an error too ("Cannot compute cosine distance against zero vector"). A single all-zero row in the base table fails a `COSINE` search.

**a NULL inside the array**

```sql
SELECT EUCLIDEAN_DISTANCE([1.0, NULL], [4.0, 6.0]) AS d
```

BigQuery rejects this query:

```text
Unexpected NULL element in input array; error in EUCLIDEAN_DISTANCE expression
```

**cosine distance to a zero vector**

```sql
SELECT COSINE_DISTANCE([1.0, 0.0], [0.0, 0.0]) AS d
```

BigQuery rejects this query:

```text
Cannot compute cosine distance against zero vector. Error in COSINE_DISTANCE expression
```

## Brute force versus an index

Without an index, BigQuery reads the whole embedding column and computes every distance. That is exact. It is the only mode that always gives the true neighbors. Our 200,000 row test below took 2.6 seconds of slot time this way.

`CREATE VECTOR INDEX` offers two types:

```sql
CREATE VECTOR INDEX docs_idx
ON mydataset.docs(embedding)
OPTIONS (index_type = 'IVF', distance_type = 'COSINE', ivf_options = '{"num_lists": 100}');

CREATE VECTOR INDEX docs_idx
ON mydataset.docs(embedding)
OPTIONS (index_type = 'TREE_AH', distance_type = 'COSINE', tree_ah_options = '{"leaf_node_embedding_count": 1000}');
```

IVF clusters the vectors into lists with k-means and searches the lists nearest to the query. TreeAH compresses the vectors with product quantization (ScaNN), finds candidates from the compressed form and re-scores them with the exact vectors. Google recommends IVF for small query batches and TreeAH for batches of hundreds of query vectors. The index is built in the background, and queries run without it until `coverage_percentage` in `INFORMATION_SCHEMA.VECTOR_INDEXES` reaches 100. Indexing needs a table of at least 10 MB. A smaller table gets no index, and the search says `BASE_TABLE_TOO_SMALL`.

### What we measured

We built a table of 200,000 rows with 64-dimensional random vectors (105.6 MB) and ran `top_k => 10` queries with Euclidean distance. The data has no cluster structure, which is the hardest case for an approximate index, so treat the recall numbers as a floor. Recall is the share of the exact top 10 that the index also returned, averaged over 20 queries.

| Setup                               | Bytes processed          | Slot time    | Recall |
| ----------------------------------- | ------------------------ | ------------ | ------ |
| Brute force, 1 query                | 104.0 MB                 | 2.6 s        | 100%   |
| IVF (100 lists), 1 query, default   | 109.7 MB                 | 12.0 s       | 16%    |
| IVF, `fraction_lists_to_search` 0.1 | not measured             | not measured | 52%    |
| IVF, `fraction_lists_to_search` 0.5 | 109.7 MB                 | 28.7 s       | 93%    |
| TreeAH, 1 query                     | 104.0 MB, index not used | 2.3 s        | 100%   |
| TreeAH, batch of 20 queries         | 118.1 MB                 | 51.5 s       | 39%    |
| Brute force, batch of 20            | 104.0 MB                 | 11.4 s       | 100%   |

On a table this size the index doesn't save anything: the indexed queries processed 5 to 14% more bytes than brute force, because on-demand billing counts the base table, the index and the query, and they used more slot time. Google says gains grow with table size, and 200,000 rows is small. The default search width was a bad trade on this data. The IVF default found 16% of the true neighbors, and reading half the lists found 93% at the same bytes. Set `fraction_lists_to_search` and measure recall against a brute-force run on a sample of your own queries. TreeAH ignored the index for a single query. The job statistics said `ESTIMATED_PERFORMANCE_GAIN_TOO_LOW`, and the query ran as brute force.

An index also takes space. The IVF index was 28.9 MB and the TreeAH index 4.1 MB, both billed as active storage.

### Check whether the index was used

`VECTOR_SEARCH` never tells you in the result. Look at the job's `vectorSearchStatistics.indexUsageMode` (`FULLY_USED`, `PARTIALLY_USED` or `UNUSED`) and `indexUnusedReasons`:

```bash
bq show --format=prettyjson -j JOB_ID
```

The reasons we saw: `PENDING_INDEX_CREATION` (the index exists but isn't populated yet), `INDEX_SUPPRESSED_BY_FUNCTION_OPTION` (we passed `use_brute_force`), and `ESTIMATED_PERFORMANCE_GAIN_TOO_LOW`. Passing `use_brute_force` together with `fraction_lists_to_search` is an error:

**two options that contradict each other**

```sql
CREATE TEMP TABLE animals AS
SELECT 'dog' AS id, 'mammal' AS kind, [1.0, 2.0] AS emb
UNION ALL SELECT 'wolf', 'mammal', [2.0, 4.0]
UNION ALL SELECT 'snake', 'reptile', [-2.0, 3.0]
UNION ALL SELECT 'lion', 'mammal', [2.0, -2.5]
UNION ALL SELECT 'tiger', 'mammal', [3.0, -2.0]
UNION ALL SELECT 'whale', 'mammal', [-5.0, -1.0];

SELECT base.id FROM VECTOR_SEARCH(TABLE animals, 'emb', query_value => [1.0, 2.0], top_k => 3, options => '{"use_brute_force": true, "fraction_lists_to_search": 0.5}')
```

BigQuery rejects this query:

```text
Query error: fraction_lists_to_search should not be specified when use_brute_force is set. at [9:1]
```

## What it costs

On-demand pricing bills the bytes of the base table, the index and the query, as for any query. Building and refreshing an index is free up to a per-organization size limit, after which you need your own reservation. Index storage is billed as active storage. Google lists `VECTOR_SEARCH` as not accelerated by BI Engine. The project that runs the query must be the project that holds the base table.

Generating the embeddings is a separate cost: `AI.GENERATE_EMBEDDING` and `AI.EMBED` call a Vertex AI model, billed there. See [AI functions](https://docs.querylab.io/bigquery/ai-functions/). `VECTOR_SEARCH` itself calls no model, so the examples above are free to run. If you want a keyword match instead of a semantic one, that is [SEARCH](https://docs.querylab.io/bigquery/search/).

Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Introduction to embeddings and vector search](https://cloud.google.com/bigquery/docs/vector-search-intro)

- [Manage vector indexes](https://cloud.google.com/bigquery/docs/vector-index)

- [Search functions, VECTOR_SEARCH](https://cloud.google.com/bigquery/docs/reference/standard-sql/search_functions#vector_search)

- [ML.DISTANCE function](https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-distance)