Vector assist reference

This page provides detailed information about all available vector assist functions, and their required and optional parameters.

vector_assist.define_spec

Defines a new vector specification, or vector spec, and returns recommendations necessary to step up the vector workload. This function applies user-provided specifications to generate the recommendations.

Function

CREATE OR REPLACE FUNCTION vector_assist.define_spec(  spec_id TEXT DEFAULT NULL,  table_name TEXT,  schema_name TEXT DEFAULT NULL,  vector_column_name TEXT DEFAULT NULL,  text_column_name TEXT DEFAULT NULL,  vector_index_type TEXT DEFAULT NULL, -- Allowed: 'hnsw', 'ivfflat'  embeddings_available BOOL DEFAULT NULL,  embedding_model TEXT DEFAULT NULL,  num_vectors INT DEFAULT NULL,  dimensionality INT DEFAULT NULL,  distance_func TEXT DEFAULT NULL, -- Allowed: 'cosine', 'ip', 'l2', 'l1'  quantization TEXT DEFAULT NULL, -- Allowed: 'none', 'halfvec', 'bit'  prefilter_column_names TEXT[] DEFAULT NULL,  memory_budget_kb INT DEFAULT NULL, -- In KB  target_recall FLOAT DEFAULT NULL,  target_top_k INT DEFAULT NULL,  tune_vector_index BOOL DEFAULT FALSE, -- Set to true if target_recall is set ) RETURNS SETOF vector_assist.RECOMMENDATIONS; 

Parameters

Return value

Returns a table of recommendations that must be applied to set up the requested vector workload. Each row is a single recommendation that contains an executable SQL query and a detailed explanation of why to apply the recommendation. The recommendation can be applied using the apply_recommendation() function.

The recommendations table contains the following columns:

vector_assist.list_specs

Returns the current vector spec for a given table and column.

Function

CREATE OR REPLACE FUNCTION vector_assist.list_specs(  table_name TEXT,  column_name TEXT DEFAULT NULL ) RETURNS SETOF vector_assist.VECTOR_SPECS 

Parameters

Return value

Returns a table of specifications for the given table and optionally, column name.

vector_assist.modify_spec

Modifies an existing vector spec. When the vector spec is modified, the associated recommendations are regenerated based on the new specs.

Function

CREATE OR REPLACE FUNCTION vector_assist.modify_spec(  spec_id TEXT,  table_name TEXT DEFAULT NULL,  schema_name TEXT DEFAULT NULL,  vector_column_name TEXT DEFAULT NULL,  text_column_name TEXT DEFAULT NULL,  vector_index_type TEXT DEFAULT NULL,  embeddings_available BOOL DEFAULT NULL,  num_vectors INT DEFAULT NULL,  dimensionality INT DEFAULT NULL,  embedding_model TEXT DEFAULT NULL,  prefilter_column_names TEXT[] DEFAULT NULL,  distance_func TEXT DEFAULT NULL,  quantization TEXT DEFAULT NULL,  memory_budget_kb INT DEFAULT NULL,  target_recall FLOAT DEFAULT NULL,  target_top_k INT DEFAULT NULL,  tune_vector_index BOOL DEFAULT NULL ) RETURNS SETOF vector_assist.RECOMMENDATIONS 

Parameters

Return value

Returns an updated table of recommendations that must be applied to set up the requested vector workload. Each row is a single recommendation that contains an executable SQL query and a detailed explanation of why to apply the recommendation. The recommendation can be applied using the apply_recommendation() function.

The recommendations table contains the following columns:

vector_assist.get_recommendations

Returns recommendations associated with a specific vector spec_id.

Function

CREATE OR REPLACE FUNCTION vector_assist.get_recommendations (  spec_id TEXT ) RETURNS SETOF vector_assist.RECOMMENDATIONS; 

Parameters

Return value

Returns a table of requested recommendations.

The recommendations table contains the following columns:

vector_assist.apply_recommendation

Applies a single recommendation using the recommendation_id.

Function

CREATE OR REPLACE FUNCTION vector_assist.get_recommendations (  spec_id TEXT ) RETURNS SETOF vector_assist.RECOMMENDATIONS; 

Parameters

Return value

Returns a boolean that confirms if a recommendation was successfully applied and update the vector_assist.RECOMMENDATIONS table column applied to TRUE.

If vector assist is unable to apply the recommendation, then it raises an error message on failure.

vector_assist.apply_spec

Applies all recommendations associated with a specific vector spec_id or table name. This function goes through all recommendations and runs the vector_assist.apply_recommendation function on each recommendation.

Must specify either a spec_id or table_name to run function.

Function

CREATE OR REPLACE FUNCTION vector_assist.apply_spec(  spec_id TEXT DEFAULT NULL,  table_name TEXT DEFAULT NULL,  schema_name TEXT DEFAULT NULL,  column_name TEXT DEFAULT NULL -- text_column_name or vector_column_name ) RETURNS BOOLEAN; 

Parameters

Return value

Returns a boolean that confirms if all recommendations were successfully applied and sets the value in the vector_assist.RECOMMENDATIONS table column applied to TRUE for all recommendations.

If vector assist is unable to apply the recommendation, then it raises an error message on failure.

vector_assist.modify_recommendation

Modifies the query for a specific recommendation and sets the modified field in the vector_assist.RECOMMENDATIONS table for the associated recommendation to TRUE.

Function

CREATE OR REPLACE FUNCTION vector_assist.modify_recommendation(  recommendation_id TEXT,  modified_query TEXT ) RETURNS BOOLEAN; 

Parameters

Return value

Returns a boolean that confirms if a recommendation was successfully updated and sets the vector_assist.RECOMMENDATIONS table column modified to TRUE.

If vector assist is unable to update the recommendation, then it raises an error message on failure.

vector_assist.generate_query

Generates an optimized vector search query based on the defined vector spec and search parameters. This function might tune the ef_search value to meet the set target_recall if applicable.

Function

CREATE OR REPLACE FUNCTION vector_assist.generate_query(  spec_id TEXT DEFAULT NULL,  table_name TEXT DEFAULT NULL,  schema_name TEXT DEFAULT NULL,  column_name TEXT DEFAULT NULL, -- text_column_name or vector_column_name of the spec  search_text TEXT DEFAULT NULL,  search_vector VECTOR DEFAULT NULL, -- e.g. '[1.0,2.0,3.0]'::vector  output_column_names TEXT[] DEFAULT NULL, -- Columns to retrieve, NULL for SELECT *  top_k INT DEFAULT NULL,  filter_expressions TEXT[] DEFAULT NULL,  target_recall FLOAT DEFAULT NULL, -- Inherited from spec, can be overridden  iterative_index_search BOOL DEFAULT NULL ) RETURNS TEXT; -- Returns the text representing the search query 

Parameters

Return value

Returns a text containing the vector search query for the given parameters. This query can be executed or saved to run later.

vector_assist.evaluate_query_recall

Evaluates the recall of the vector index for a given query. This function uses the database and index configurations in a JSON string as inputs.

Function

CREATE OR REPLACE FUNCTION vector_assist.evaluate_query_recall(  IN ann_query text,  IN vector_search_configurations json DEFAULT NULL,  OUT recall float,  OUT ann_execution_time float,  OUT ground_truth_execution_time float,  OUT index_type text) RETURNS RECORD 

Parameters

Return value

Returns a table that evaluates the recall of the vector index for the given query.

The table contains the following columns:

Example

The following example evaluates the recall for a HNSW index and overrides the HNSW.ef_search configuration parameter:

SELECT * FROM vector_assist.evaluate_query_recall(  ann_query => 'SELECT id FROM items ORDER BY embedding <=> ''[1,2,3]'' LIMIT 10',  vector_search_configurations => '{"hnsw.ef_search": 30}' );