Query design

Applies to VAST Cluster 5.5. Examples use the VAST DataBase Python SDK 2.1.0.

A vector search on VAST DataBase is a SQL query that orders rows by their distance to a query vector and keeps the first k. The VAST Query Engine uses the table's vector index only when the query has the shape it recognises. This guide shows that shape, which distance function goes with each metric, how to filter, which queries don't use the index and how to rewrite them, and how to search from Python.

Cluster-side tuning is a platform-team topic; see performance.

Run the code blocks in order in one Python session. Below each block is the output you should see.

Set up an example table

Connect, and create two small tables, one with the cosine metric and one with ip, each with a lang column to filter on:

import os
import re
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb
import vastdb

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

def connect():
    return 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,
    )

conn = connect()

def sql(query, connection=None):
    with (connection or conn).cursor() as cur:
        cur.execute(query)
        return cur.fetchall()

def try_sql(query, connection=None):
    try:
        return sql(query, connection)
    except Exception as e:
        msg = str(e).replace('\\"', '"')
        m = (re.search(r'failed with: (.*?)", request_id', msg, re.S)
             or re.search(r'message: "(?:\[req=0x[0-9a-f]+\]: )?(.*?)", request_id', msg, re.S))
        return m.group(1) if m else msg

rows = []
for i in range(1, 21):
    vec = ", ".join(str(round(1 + i * 0.1 + k * 0.01, 3)) for k in range(8))
    rows.append(f"({i}, '{'en' if i % 2 else 'fr'}', 'doc {i}', [{vec}]::FLOAT[8])")

sql(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
for name, metric in (("docs", "cosine"), ("docs_ip", "ip")):
    sql(f"CREATE TABLE IF NOT EXISTS {SCHEMA}.{name} (id BIGINT, lang VARCHAR, title VARCHAR, "
        f"embedding VECTOR(FLOAT, 8)) VECTOR CLUSTER BY embedding (metric = '{metric}')")
    sql(f"INSERT INTO {SCHEMA}.{name} VALUES {', '.join(rows)}")
print(sql(f"SELECT count(*) FROM {SCHEMA}.docs"), sql(f"SELECT count(*) FROM {SCHEMA}.docs_ip"))

[(20,)] [(20,)]

The query shape the index needs

Write the search in this shape; the other shapes still run, but compare every row.

SELECT
    id AS result_id,
    title,
    array_cosine_distance(
        embedding::FLOAT[1024],
        ARRAY[<q0>, <q1>, ...]::FLOAT[1024]
    ) AS distance
FROM "bucket/schema".indexed_table
WHERE language = 'en'
ORDER BY distance ASC
LIMIT 10;

The Query Engine rewrites a query to use the vector index only when all of these hold:

• It ends with ORDER BY <distance> LIMIT <k>, with a single ORDER BY key, in ascending order (the default).
• There's no OFFSET. With an offset, the query still runs, but without the index.
• The distance is computed by the function that matches the index metric (next section).
• One argument of the distance function is the indexed vector column (a cast such as embedding::FLOAT[1024] is fine). The other is a constant query vector of the same dimension, cast to FLOAT[<d>].
• Between the ORDER BY and the table there are only filters (WHERE) and projections: no joins, subqueries or aggregations.

A query that doesn't match still returns correct results, but by comparing the query vector with every row. EXPLAIN shows which you have: a search that uses the index plans a VAST_VEC_INDEX_SCAN step, while one that doesn't plans only TOP_N over a VAST_SCAN of the table. See check the plan.

Some query shapes don't run at all. ORDER BY without LIMIT isn't supported by the Query Engine:

q = "[1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0]::FLOAT[8]"
dist = f"array_cosine_distance(embedding::FLOAT[8], {q})"

print("LIMIT:       ", sql(f"SELECT id FROM {SCHEMA}.docs ORDER BY {dist} LIMIT 3"))
print("LIMIT OFFSET:", sql(f"SELECT id FROM {SCHEMA}.docs ORDER BY {dist} LIMIT 3 OFFSET 1"))
print("no LIMIT:    ", try_sql(f"SELECT id FROM {SCHEMA}.docs ORDER BY {dist}"))

LIMIT:        [(20,), (19,), (18,)]
LIMIT OFFSET: [(19,), (18,), (17,)]
no LIMIT:     The operator 'ORDER_BY' type=1 is currently not supported for this usage.

LIMIT has no default and no cap: searches with LIMIT up to 1,000,000 returned exactly that many rows. A larger LIMIT returns more data, so ask only for the rows you use.

Metric and function

Use the function that matches the metric the table was created with, and order ascending: smaller values are more similar for all three.

For more on these three functions, see Vector search. Write the function name: the operators <-> and <=> compute the same distances, but a search written with them doesn't use the index, and <#> isn't accepted.

For ip tables, use array_negative_inner_product, not array_inner_product. array_inner_product grows as vectors get more similar, so ORDER BY ... ASC returns the least similar rows first, and the index isn't used. The negative form fixes both:

for fn in ("array_inner_product", "array_negative_inner_product"):
    result = sql(f"SELECT id FROM {SCHEMA}.docs_ip ORDER BY {fn}(embedding::FLOAT[8], {q}) LIMIT 3")
    print(f"{fn:30}", [r[0] for r in result])

array_inner_product            [1, 2, 3]
array_negative_inner_product   [20, 19, 18]

The other cast works with the index. The operator form runs and returns the same rows, but as a full scan:

checks = {
    "array_cosine_distance on ::VECTOR(FLOAT, 8)":
        f"SELECT id FROM {SCHEMA}.docs ORDER BY array_cosine_distance(embedding, [1,1,1,1,1,1,1,1]::VECTOR(FLOAT, 8)) LIMIT 3",
    "<=> (cosine)": f"SELECT id FROM {SCHEMA}.docs ORDER BY embedding::FLOAT[8] <=> {q} LIMIT 3",
}
for label, query in checks.items():
    print(f"{label:44}", [r[0] for r in sql(query)])

array_cosine_distance on ::VECTOR(FLOAT, 8)  [20, 19, 18]
<=> (cosine)                                 [20, 19, 18]

Filter with WHERE

WHERE works with vector search, on any column (Vector search):

for row in sql(f"""SELECT id, lang, round({dist}::DOUBLE, 6) AS distance
                   FROM {SCHEMA}.docs
                   WHERE lang = 'en' AND id > 10
                   ORDER BY distance LIMIT 3"""):
    print(row)

(19, 'en', 3e-05)
(17, 'en', 3.5e-05)
(15, 'en', 4.1e-05)

With a filter, the search keeps reading clusters until it has found enough rows that pass the filter, so a very selective filter makes a search read more of the index. See filters.

Row and column security rules, including the row filter by S3 path, hide rows that your user may not see. They aren't a substitute for WHERE: use them for access control, and WHERE for what your query is about. See filters.

Project only what you need

Return a stable ID and the payload columns your application uses. Don't return the embedding column unless you need it: returning it adds the cost of sending every vector back.

Queries that don't use the index

These queries run, but as a comparison with every row, or not at all. Rewrite them like this:

If you aggregate search results in SQL, for example counting categories over the top k, do it outside the search query: aggregations in the query stop it using the index. The Query Engine's aggregate restrictions are in the SQL reference.

Check the plan with EXPLAIN

Put EXPLAIN in front of a search to see whether it uses the index. On a vector-indexed table, the plan of a search that uses the index contains a VAST_VEC_INDEX_SCAN step, which reads the index. A search the index can't answer, such as one with OFFSET or a descending order, plans only a TOP_N over a VAST_SCAN of the whole table. Your platform team can also see index use in the cluster's TopN counters (performance).

Tune the search with session settings

At each level of the index, the search reads a number of clusters, nearest first. Session settings set the minimum, the maximum and a pruning ratio for each level; the configuration reference lists them. Set them as connection options after you connect, with the prefix vast.db.setting.. They aren't accepted as database options in db_kwargs, and the Query Engine doesn't support SET:

tuned = connect()
tuned.adbc_connection.set_options(**{
    "vast.db.setting.vector_search_min_prob": "50,30,4,4",
    "vast.db.setting.vector_search_max_prob": "50,30,4,4",
})
print("tuned:", [r[0] for r in sql(f"SELECT id FROM {SCHEMA}.docs ORDER BY {dist} LIMIT 3", tuned)])
print("SET:  ", try_sql("SET vector_search_min_prob = '50,30,4,4'"))
tuned.close()

try:
    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"],
        "vast.db.setting.vector_search_min_prob": "50,30,4,4"})
except Exception as e:
    print("db_kwargs:", str(e).split(" (key")[0])

tuned: [20, 19, 18]
SET:   The operator 'SET' type=69 is currently not supported.
db_kwargs: INVALID_ARGUMENT: Unknown option for database

The driver accepts a misspelled setting name without an error, so check the spelling: a typo looks like a setting with no effect. To change the settings against measured recall, see measure recall and latency.

Search from Python with vector_search()

The Python SDK builds the query for you. It uses the function the table's metric maps to, adds ORDER BY and LIMIT, and runs it through ADBC in your SDK transaction. The SDK gives the ADBC driver the session's endpoint, so open the session on the Query Engine endpoint (setup):

session = vastdb.connect(endpoint=os.environ["QE_ENDPOINT"])
with session.transaction() as tx:
    table = tx.bucket(BUCKET).schema(SCHEMA_NAME).table("docs")
    result = table.vector_search(
        vec=[1.0] * 8,
        columns=["id", "lang"],
        limit=3,
        predicate=table["id"] > 15,
    ).read_all()
    print(result.select(["id", "lang"]).to_pydict())
    print(result.column_names)

{'id': [20, 19, 18], 'lang': ['fr', 'en', 'fr']}
['id', 'lang', 'vast_pysdk_vector_dist']

The distance comes back in a column named vast_pysdk_vector_dist. vector_search() reads the metric from the table's metadata. If the table object is older than the index (for example, you created it in an earlier transaction), call table.reload_schema() first.

Don't call vector_search() on ip tables with SDK 2.1.0: it returns the least similar rows first. Write the SQL with array_negative_inner_product, as above; see Python SDK.

Clean up

for name in ("docs", "docs_ip"):
    sql(f"DROP TABLE IF EXISTS {SCHEMA}.{name}")
sql(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
print("cleaned up")

cleaned up

Next steps

• Filters
• Measure recall and latency: time queries, measure recall and tune the probes.
• SQL reference and configuration reference.