Search with a stored query vector¶
Store a query vector in a table and use it to search an IVF-FLAT index. This is useful when applications manage search vectors as data instead of embedding a vector literal in every query. Run the following SQL on MatrixOne v4.2.6 or later.
Create three searchable items and one query vector:
DROP DATABASE IF EXISTS scalar_vector_demo;
CREATE DATABASE scalar_vector_demo;
USE scalar_vector_demo;
SET experimental_ivf_index = 1;
SET probe_limit = 1;
CREATE TABLE items(id VARCHAR(32) PRIMARY KEY, v VECF64(3) NOT NULL);
INSERT INTO items VALUES ('a','[0,0,0]'),('b','[1,0,0]'),('c','[3,0,0]');
CREATE INDEX ix USING IVFFLAT ON items(v) LISTS=1 OP_TYPE 'vector_l2_ops';
CREATE TABLE query_vectors(id VARCHAR(32) PRIMARY KEY, v VECF64(3));
INSERT INTO query_vectors VALUES ('q','[1,0,0]');
Search for the two nearest items. The first result is b, followed by a. In the EXPLAIN output, find Scalar Vector Query and ivf_search to confirm the indexed path. Changing the stored query vector to [3,0,0] changes the result order to c, then b:
EXPLAIN SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2;
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2;
UPDATE query_vectors SET v='[3,0,0]' WHERE id='q';
SELECT id FROM items ORDER BY l2_distance(v,(SELECT v FROM query_vectors WHERE id='q')) LIMIT 2;
DROP DATABASE scalar_vector_demo;
The vector column being searched must be NOT NULL, and the scalar subquery must return at most one vector. IVF-FLAT search is approximate; these three rows demonstrate the query shape and result checks, not recall or performance. The same query shape also supports HNSW. See Scalar Vector Query for HNSW setup, PREPARE, NULL providers, and snapshot behavior. The matching MatrixOne regression test covers the source feature.