Vector Operations

Vector search compares numeric representations of documents or other objects. Use exact distance queries for a correctness baseline and an index-supported plan when approximate retrieval offers a useful latency/recall tradeoff. The vector tutorial combines stored embeddings, search, and RAG.

VECTOR Data Type

VECTOR(N) stores fixed-dimension Float32 values. Every compared vector must have the same dimension and come from a compatible embedding space. Matching dimension alone does not make two embedding models interchangeable.

CREATE TABLE documents (
    id BIGINT,
    content VARCHAR,
    embedding VECTOR(1536)
);

This table is initially empty. Insert content and embeddings before building an index or expecting search results.

Distance Functions

Function	Meaning	Ranking direction
L2_DISTANCE(a, b)	Euclidean distance	Smaller is closer
COSINE_DISTANCE(a, b)	One minus cosine similarity	Smaller is closer
INNER_PRODUCT(a, b)	Dot product	Larger is more similar for an appropriate embedding model
MANHATTAN_DISTANCE(a, b)	Sum of absolute coordinate differences	Smaller is closer

Cosine distance is normally in [0, 2] for nonzero finite vectors. Handle missing, non-finite, and zero vectors deliberately. Do not interpret cosine distance as a probability.

Similarity Search

For an existing populated table with 1536-dimensional embeddings:

SELECT id, content,
       COSINE_DISTANCE(embedding, EMBED('refund policy')) AS distance
FROM documents
ORDER BY distance ASC, id ASC
LIMIT 10;

A tie-breaker makes equal-distance ordering easier to compare. Without an applicable index access path, this is an exact scan. LIMIT 10 limits the result, not necessarily the number of rows scanned or embedded upstream.

Vector Indexing

CREATE VECTOR INDEX

SQL index construction currently builds HNSW indexes. After the tutorial creates and populates its table:

CREATE VECTOR INDEX IF NOT EXISTS tutorial.rag.idx_documents_embedding
ON tutorial.rag.documents (embedding)
USING HNSW
WITH (m = 16, ef_construction = 200, metric = 'cosine');

Match the index metric to the search expression. The construction path defaults to m = 16, ef_construction = 200, and metric l2; the bottom-layer neighbor bound is derived as twice m. For cosine queries, specify metric = 'cosine' explicitly.

Higher connectivity and construction search breadth can improve recall at the cost of build time and memory. There is no universal recall guarantee. Use CREATE OR REPLACE VECTOR INDEX when an intentional rebuild is needed after changing the source or settings.

Managing Indexes

SHOW VECTOR INDEXES;
SHOW VECTOR INDEXES IN tutorial.rag;

The unscoped form lists the authenticated tenant's visible inventory. The qualified form filters by catalog and schema. A schema-only scope requires a current catalog. Check status and source freshness; an index can exist without being ready or current.

DROP VECTOR INDEX IF EXISTS tutorial.rag.idx_documents_embedding;

ALTER VECTOR INDEX SET ef_search

Persist an HNSW search setting in the catalog:

ALTER VECTOR INDEX tutorial.rag.idx_documents_embedding SET ef_search = 128;

A session override is available with SET vector_ef_search = 128. Measure the recall/latency effect on your query distribution rather than assuming a larger value is always worthwhile.

WARMUP VECTOR INDEX

WARMUP VECTOR INDEX tutorial.rag.idx_documents_embedding;

Warm-up requests populate worker shard caches. Inspect reported outcomes; this does not prove that a later query selected the indexed access path.

Inline-filter columns

HNSW construction accepts an attribute_filters string listing source columns, for example attribute_filters = 'category,in_stock'. These attributes can assist predicate-aware candidate selection. Correctness still depends on applying the SQL predicate to final rows; an attribute declaration alone does not guarantee a specific plan or speedup.

Other index types

SQL index creation currently builds HNSW indexes only. An accepted USING token for another index type, such as IVF or product quantization, does not mean that index type is built. This reference's SQL creation examples use HNSW.

Predicate Pushdown for ANN

Use EXPLAIN on the actual query to see whether the planner selects an index, exact scan, or filtering strategy:

EXPLAIN
SELECT doc_id, title
FROM tutorial.rag.documents
ORDER BY COSINE_DISTANCE(embedding, EMBED('refund policy'))
LIMIT 10;

A correct nearest-neighbor result from a small table can come from an exact scan. SHOW VECTOR INDEXES confirms inventory; it does not establish ANN acceleration. Filters can reduce available candidates, and approximate search can trade recall for speed.

Recall measurement

Compare ANN results with an exact scan over the same table snapshot, metric, and filter. Recall@k measures how many exact top-k neighbors are recovered; record latency separately. A high-ef_search ANN run is a useful proxy comparison but is not exact ground truth.

Embedding Generation

EMBED(text) calls Gnok's embedding provider for the authenticated tenant, within your organization's monthly embedding allowance. It doesn't need AI Governance. The default model is text-embedding-3-small; you can also pass a supported model name as a constant argument:

SELECT EMBED('What is the return policy?');
SELECT EMBED('What is the return policy?', 'text-embedding-3-small');

Gnok manages the provider credentials and the permitted models; you don't supply an API key. The current SQL UDF returns a 1536-dimensional Float32 vector and rejects incompatible response dimensions. Setting a different model name does not resize the SQL type.

Use the same provider/model contract for document and query embeddings. Re-embed the corpus when changing to an incompatible space. AI_EMBED is an alias; AI_SIMILARITY composes embedding and cosine distance.

Additional SQL Functions

Function	Meaning
LIST_DOT_PRODUCT(a, b)	Array dot product
LIST_DISTANCE(a, b)	Array L2 distance
LIST_COSINE_SIMILARITY(a, b)	Cosine similarity, where larger is closer
LIST_COSINE_DISTANCE(a, b)	Cosine distance, where smaller is closer
VECTOR_AVG(v)	Element-wise vector average aggregate

A vector average is a centroid, not automatically a normalized embedding. Normalize only when appropriate for the downstream metric.

Semantic search and RAG

A complete workflow creates source rows, embeds them, builds or refreshes the desired index, retrieves candidates, and passes selected text to a generation function. Assess retrieval quality separately from answer quality. Relevant passages do not guarantee that a generated answer is faithful.

Vector search tutorial · BM25 text search · AI scalar functions · Configuration