# Vector Range Search Vector range search allows users to query for vectors which have a similarity score beyond a specified threshold. Vector range search can be a useful alternative to standard top-K vector search when you are looking for all vectors with a suitably high score, and may not know a good value of K to try. Both vector top-K and range searches can use vector ANN indexes to give results faster. As an example, consider a table, `comments`, with the following definition: ```sql CREATE TABLE comments(id INT, comment TEXT, comment_embedding VECTOR(4), category VARCHAR(256)); ``` The following query shows an example of a vector range search on the `comments` table using a `WHERE` clause to filter for embeddings with a `score > 0.7`. The `@query_vec` variable is cast to a `VECTOR` to ensure that `@query_vec` is a valid `VECTOR` and to improve performance. ```sql SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]':>VECTOR(4)); SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE score > 0.7; ``` Vector range search queries can take advantage of a vector index if a vector index is built on the `comment_embedding` column of the `comments` table. In addition, the query vector (`@query_vec` in this example) must be a runtime constant. Refer to [Vector Indexing](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md), [Vector Type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md), [Working with Vector Data](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-vector-data.md), [Hybrid Search - Re-ranking and Blending Searches](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/hybrid-search-re-ranking-and-blending-searches.md) , and [Tuning Vector Indexes and Queries](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/tuning-vector-indexes-and-queries.md), for additional information on vector search in SingleStore. ## Index Options, Hints, and Parameters The index options, hints, and parameters for vector range search are largely the same as those for indexed vector search ([Vector Indexing](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md)), with the following exceptions: * Use `RANGE_SEARCH_OPTIONS` instead of `SEARCH_OPTIONS` to specify search options in queries. * The search parameter `k` is not supported. * The search parameter `nprobe` is not supported when using an `IVF_PQFS` index. ## Create Table and Indexes The following table, index, and data are used in the examples. The statement below creates a `comments` table which represents a set of comments to be searched. The table includes the comments as `TEXT` and embeddings of those comments as a `VECTOR` with 4 elements. This example demonstrates a vector range search query over this table. Refer to [Working with Vector Data](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-vector-data.md) for information about vector embeddings. Next populate the table. The vectors in the following insert statement are represented as a JSON array of numbers for readability. Fictional, low-dimension vectors are used here to keep the example easier to read. ```sql INSERT INTO comments VALUES (1, "The cafeteria in building 35 has a great salad bar",  '[0.45, 0.55, 0.495, 0.5]',     "Food"), (2, "I love the taco bar in the B16 cafeteria.",    '[0.01111, 0.01111, 0.1, 0.999]',     "Food"), (3, "The B24 restaurant salad bar is quite good.",     '[0.1, 0.8, 0.2, 0.555]', "Food"); ``` Next, add a vector index of type `IVF_PQFS` to the comment\_embedding column of the comments table. ```sql ALTER TABLE comments ADD VECTOR INDEX ivf_dp(comment_embedding) INDEX_OPTIONS'{"index_type":"IVF_PQFS"}'; ``` Optionally optimize the table to enhance performance. ```sql OPTIMIZE TABLE comments FLUSH; ``` ## Example 1 - Use Vector Range Search The following example demonstrates the use of vector range search. Suppose that the user likes salad and so the query is "restaurants with good salad." A vector embedding API can be called to obtain a vector for that phrase. Assume the vector returned is: `'[0.44, 0.554, 0.34, 0.62]'`. The following query first creates a query vector (`@query_vec`) using this vector. Next, the vector range search query uses similarity search to look for vectors in the comments table which are similar to the query vector. Finally, the query returns vectors in the comments table which have a similarity score with the query vector of > 0.7, using `DOT_PRODUCT` (`<*>`). The `ORDER BY` clause is included to ensure that the higher scores appear at the top of the results. `EUCLIDEAN_DISTANCE` (`<->`) is also available for use with vector range search. When using the dot product metric, the query must be written to find scores above a threshold; when using the Euclidean distance metric, the query must be written to find scores below a threshold. ```sql SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]':>VECTOR(4)); SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE score > 0.7 ORDER BY score DESC; ``` ```output +------+----------------------------------------------------+----------+--------------------+ | id   | comment                                            | category | score              | +------+----------------------------------------------------+----------+--------------------+ |    1 | The cafeteria in building 35 has a great salad bar | Food     | 0.9810000061988831 | |    3 | The B24 restaurant salad bar is quite good.        | Food     | 0.8993000388145447 | +------+----------------------------------------------------+----------+--------------------+ ``` If a vector index has been added to the `comment_embedding` column in the table, as was done in this example, the vector index will be used in this query. ## Example 2 - Identify the use of Vector Range Search The `EXPLAIN` or `PROFILE` plan for the query can be used to tell if the vector index is being used in the query. For example: ```sql SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]':>VECTOR(4)); EXPLAIN SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE score > 0.7 ORDER BY score DESC; ``` ```output +----------------------------------------------------------------------------+ | EXPLAIN                                                                    | |                                                                            | +----------------------------------------------------------------------------+ | Gather partitions:all alias:remote_0 parallelism_level:segment             | | Project [comments.id, comments.comment, comments.category,                 | |    DOT_PRODUCT(comments.comment_embedding,@query_vec) AS score]            | |                                                                            | | ColumnStoreFilter [DOT_PRODUCT(comments.comment_embedding,@query_vec) >    | |     (.7!:>double NULL) AND INTERNAL_VECTOR_RANGE_SEARCH(0, @query_vec, .7) | |      index]                                                                | |                                                                            | | ColumnStoreScan dbname.comments, SORT KEY __UNORDERED ()          | |        table_type:sharded_columnstore                                      | +----------------------------------------------------------------------------+ ``` The `INTERNAL_VECTOR_RANGE_SEARCH` notation in the `ColumnStoreFilter` row indicates that the plan uses a vector index for range search. ## Example 3 - Use Index Hints The syntax `[USE {INDEX|KEY} ([])]` can be used to specify a specific index to use when there are multiple vector indexes, or to disable the use of an index. The query below specifies the use of the index named `ivf_dp`. If that index is not available, the query returns an error. See [SHOW INDEXES](https://docs.singlestore.com/db/v9.1/reference/sql-reference/show-commands/show-indexes.md) for information on how to list the indexes for a table. ```sql SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]' :>VECTOR(4)); SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE score > 0.7 USE INDEX (ivf_dp); ``` ```output +------+----------------------------------------------------+----------+--------------------+ | id   | comment                                            | category | score              | +------+----------------------------------------------------+----------+--------------------+ |    1 | The cafeteria in building 35 has a great salad bar | Food     | 0.9810000061988831 | |    3 | The B24 restaurant salad bar is quite good.        | Food     | 0.8993000388145447 | +------+----------------------------------------------------+----------+--------------------+ ``` The use of empty parentheses `( )` in an index hint disables the use of vector indexes as is shown in the following query. ```sql SET @query_vec = ('[0.44, 0.554, 0.34, 0.62]' :>VECTOR(4)); SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE score > 0.7 USE INDEX (); ``` ```output +------+----------------------------------------------------+----------+--------------------+ | id   | comment                                            | category | score              | +------+----------------------------------------------------+----------+--------------------+ |    1 | The cafeteria in building 35 has a great salad bar | Food     | 0.9810000061988831 | |    3 | The B24 restaurant salad bar is quite good.        | Food     | 0.8993000388145447 | +------+----------------------------------------------------+----------+--------------------+ ``` As described previously, `EXPLAIN` can be used to verify if a query does or does not use an index. ## Example 4 - Use Index Building Parameters The following query creates a second index, named ivf\_euclid, and demonstrates use of the `index_type`, `metric_type`, and `nlist` index building parameters. ```sql ALTER TABLE comments ADD VECTOR INDEX ivf_euclid(comment_embedding) INDEX_OPTIONS' {"index_type":"IVF_PQFS", "metric_type":"EUCLIDEAN_DISTANCE", "nlist":3}'; ``` ## Example 5 - Use Index Search Parameters Add a `HNSW_FLAT` index to the `comments` table. ```sql ALTER TABLE comments ADD VECTOR INDEX hnsw(comment_embedding) INDEX_OPTIONS'{"index_type":"HNSW_FLAT"}'; ``` The query below specifies that the `hnsw` index with the search parameter `ef` set to `120` should be used to evaluate the filter `score > 0.7`. The `USE_INDEX` clause and the `RANGE_SEARCH_OPTIONS` clause must appear immediately after the filter (`score > 0.7` in this example) and `USE_INDEX` must appear before `RANGE_SEARCH_OPTIONS`. The parentheses in the example below are not necessary, but indicate that the `USE_INDEX` and `RANGE_SEARCH_OPTIONS` clauses are associated with the filter `score > 0.7`. ```sql SELECT id, comment, category, comment_embedding <*> @query_vec AS score FROM comments WHERE (score > 0.7 USE INDEX (hnsw) RANGE_SEARCH_OPTIONS '{"ef" : 120 }'); ``` ```output +------+----------------------------------------------------+----------+--------------------+ | id   | comment                                            | category | score              | +------+----------------------------------------------------+----------+--------------------+ |    1 | The cafeteria in building 35 has a great salad bar | Food     | 0.9810000061988831 | |    3 | The B24 restaurant salad bar is quite good.        | Food     | 0.8993000388145447 | +------+----------------------------------------------------+----------+--------------------+ ``` Refer to [Tuning Vector Indexes and Queries](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/tuning-vector-indexes-and-queries.md) for more information on setting index building and search parameters. ## Related Topics * [Vector Indexing](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-indexing.md) * [Vector Type](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-types/vector-type.md) * [Working with Vector Data](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-vector-data.md) * [Hybrid Search - Re-ranking and Blending Searches](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/hybrid-search-re-ranking-and-blending-searches.md) * [How to Bulk Load Vectors](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/how-to-bulk-load-vectors.md) * [Tuning Vector Indexes and Queries](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/tuning-vector-indexes-and-queries.md) * Blog Post: [Why Your Vector Database Should Not be a Vector Database](https://www.singlestore.com/blog/why-your-vector-database-should-not-be-a-vector-database/) * Blog Post: [Announcing SingleStore Indexed ANN Vector Search](https://www.singlestore.com/blog/singlestore-indexed-ann-vector-search/) *** Modified at: July 24, 2025 Source: [/db/v9.1/reference/sql-reference/vector-functions/vector-range-search/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/vector-functions/vector-range-search/) (An index of the documentation is available at /llms.txt)