--- title: "Query design" slug: "dev-vector-search-guides-query-design" description: "Learn how to optimize vector searches in VAST DataBase with SQL queries, distance functions, and Python integration for efficient data retrieval." updated: 2026-10-30T18:19:06Z published: 2026-10-30T18:19:06Z canonical: "docs.vastdata.com/dev-vector-search-guides-query-design" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # 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](/develop/docs/dev-vector-search-operations-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 ![The shape the index needs, in four parts: SELECT the columns you need and the distance function that matches the table's metric, with the indexed column as one argument and a constant query vector of the same dimension, cast to FLOAT[d], as the other; WHERE filters, with only filters and projections between the ORDER BY and the table (no joins, subqueries or aggregations); ORDER BY the distance, one key, ascending; and LIMIT k, required, with no OFFSET. The metric's functions: l2sq, array_distance; cosine, array_cosine_distance; ip, array_negative_inner_product. Shapes that still run and return correct results, but compare the query with every row instead of using the index, with the rewrite for each: OFFSET, ask for LIMIT k times the number of pages and page through the result in your application; ORDER BY the distance descending, order ascending, since smaller is nearer; more than one sort key, order by the distance only; array_inner_product on an ip table, which also returns the least similar rows first, use array_negative_inner_product; a distance function that doesn't match the metric, such as array_cosine_distance on an l2sq table, use the metric's function. ORDER BY without a LIMIT doesn't run: it fails with The operator 'ORDER_BY' type=1 is currently not supported for this usage.](https://cdn.us.document360.io/89b91113-5dc6-49db-86ec-57b16dc8ce56/Images/Documentation/vs-query-shape-v3.svg?sv=2026-02-06&spr=https&st=2026-11-08T08%3A55%3A28Z&se=2026-11-08T09%3A55%3A28Z&sr=c&sp=r&sig=ShiUmy%2BSsBds8CAxrKUYgAhhhfdrRUzTwsCBoyrfBuc%3D) *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[, , ...]::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](/develop/docs/dev-vector-search-guides-query-design#check-the-plan-with-explain). 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. | Index metric | Distance function | Order | | --- | --- | --- | | `l2sq` | `array_distance` | `ASC` | | `cosine` | `array_cosine_distance` | `ASC` | | `ip` | `array_negative_inner_product` | `ASC` | For more on these three functions, see [Vector search](/docs-5-5/docs/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](/docs-5-5/docs/vector-search#filtering-vector-searches)): ``` 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](/develop/docs/dev-vector-search-guides-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](/develop/docs/dev-vector-search-guides-filters#permissions-as-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: | Instead of | Write | Why | | --- | --- | --- | | `cosine_similarity(...) AS sim ... ORDER BY sim DESC`, as in some webinars and slides | `array_cosine_distance(...) AS distance ... ORDER BY distance LIMIT k` | There's no `cosine_similarity` function: the query fails with `Catalog Error: Scalar Function with name cosine_similarity does not exist!` Rewritten with `array_cosine_similarity` (or `1 - array_cosine_distance(...)`) and `DESC`, it runs, but as a full scan: on a test table of 1.2 million rows it took about twice as long, and returned a different top 10 from the index search for about a third of the queries, since the scan is exact and the index approximate. | | `ORDER BY distance LIMIT k OFFSET n` | `LIMIT k * pages`, and page through the result in your application | A query with `OFFSET` doesn't use the index. | | `ORDER BY distance DESC` | The metric's distance function, ascending | Only ascending order uses the index. Order by a distance, not a similarity. | | `ORDER BY distance, id` | `ORDER BY distance` | Only a single sort key uses the index. | | `array_inner_product` on an `ip` table | `array_negative_inner_product` | Only the negative form matches the index, and it puts the most similar rows first. | | A function that doesn't match the table's metric | The function for the table's metric ([table above](/develop/docs/dev-vector-search-guides-query-design#metric-and-function)) | The index answers only its own metric. | | `ORDER BY distance` with no `LIMIT` | Add `LIMIT k` | Without `LIMIT`, the query fails. | 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](/develop/docs/dev-database-reference-sql-select#aggregates). ### 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](/develop/docs/dev-vector-search-operations-performance#what-your-platform-team-can-see)). ## 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](/develop/docs/dev-vector-search-reference-configuration#settings) 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](/develop/docs/dev-vector-search-guides-developer-observability#tune-the-probes). ## 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](/develop/docs/dev-vector-search-operations-setup#which-endpoint-does-what)): ``` 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](/develop/docs/dev-vector-search-integrations-python-sdk#inner-product-indexes-search-with-sql). ## 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](/develop/docs/dev-vector-search-guides-filters) - [Measure recall and latency](/develop/docs/dev-vector-search-guides-developer-observability): time queries, measure recall and tune the probes. - [SQL reference](/develop/docs/dev-vector-search-reference-sql) and [configuration reference](/develop/docs/dev-vector-search-reference-configuration).