6.2 Use Semantic Ranking in PostgreSQL

By leveraging the power of semantic ranking, you can improve the accuracy of data retrieval and help ensure the success of your Generative AI applications. In this task, you will create a user-defined function (UDF) in your database that utilizes model inferencing capabilities of the Azure AI extension. Specifically, the azure_ml.invoke() method of the extension will be called from within the UDF to seamlessly invoke a semantic ranking model directly within SQL queries.

Your Semantic Ranker model was deployed to Azure ML.

The deployment of this solution accelerator included executing a post-deployment script to download the BGE-reranker-v2-m3 semantic ranker model and deploy it as an Azure Machine Learning (AML) inference endpoint in your subscription. This model was selected because it is lightweight, easy to deploy, and provides fast inference.

Create a reranking function

To simplify the integration of semantic ranking and invocation of the semantic ranker model from Azure ML, you will create a user-defined function (UDF). The function uses the azure_ai extension to directly make remote calls to the AML inference endpoint from a SQL query. Using the azure_ml.invoke() function within a UDF, you can make calls to the semantic ranker model.

You already configured the extension's connection to Azure ML

Recall that you already configured the Azure AI extension's connection to Azure ML in task 3.2 Configure the Azure AI extension using the following commands:

SELECT azure_ai.set_setting('azure_ml.scoring_endpoint','<endpoint>');
SELECT azure_ai.set_setting('azure_ml.endpoint_key', '<api-key>');

The UDF will be defined within your SQL database, allowing you to seamlessly incorporate semantic ranking into your queries. You will use pgAdmin to create it.

1. Return to the open instance of pgAdmin on your local machine and ensure it is connected to your PostgreSQL database.

2. In the pgAdmin Object Explorer, expand databases under your PostgreSQL server.

3. Right-click the contracts database and select Query Tool from the context menu.

4. Run the following query to create the semantic_reranking function:

   Execute the following SQL command in pgAdmin!

   CREATE OR REPLACE FUNCTION semantic_reranking(query TEXT, vector_search_results TEXT[])
   RETURNS TABLE (content TEXT, relevance NUMERIC) AS $$
   BEGIN
       RETURN QUERY
           WITH
           json_pairs AS(
               SELECT jsonb_build_object(
                       'pairs', 
                       jsonb_agg(
                           jsonb_build_array(query, content_)
                       )
                   ) AS json_pairs_data
               FROM (
                   SELECT a.content as content_
                   FROM unnest(vector_search_results) as a(content)
               )
           ), 
           relevance_scores AS(
               SELECT jsonb_array_elements(invoke.invoke) as relevance_results
               FROM azure_ml.invoke(
                       (SELECT json_pairs_data FROM json_pairs),
                       deployment_name=>'bgev2m3-v1', timeout_ms => 120000)
           ),
           relevance_scores_rn AS (
               SELECT *, ROW_NUMBER() OVER () AS idx
               FROM relevance_scores
           )
           SELECT a.content,
                  (r.relevance_results::TEXT)::NUMERIC AS relevance
               FROM
                   unnest(vector_search_results) WITH ORDINALITY AS a(content, idx2)
               JOIN
                   relevance_scores_rn AS r(relevance_results, idx)
               ON
                   a.idx2 = r.idx;
   END $$ LANGUAGE plpgsql;
   

   How does the `semantic_reranking function work?

   The semantic_reranking function enhances search results by re-ranking them based on their semantic relevance to a given query. Here's a breakdown of how it works:

   Input Parameters

   • query: A text string representing the search query.
   • vector_search_results: An array of text strings representing the initial search results obtained from a vector search.

   Return Value

   • The function returns a table with two columns: content (the original search result content) and relevance (a NUMERIC object representing the relevance score).

   Steps

   • Json Pairs Construction: The function starts by constructing a JSON object that pairs the query with each initial search result.
   • Relevance Scoring: It then calls the azure_ml.invoke function to send the JSON pairs to an Azure Machine Learning endpoint, which computes the relevance scores for each pair. The results are returned as a JSON array.
   • Row Number Assignment: The relevance scores are assigned row numbers to maintain their order.
   • Combining Results: Finally, the function combines the original search results with corresponding relevance scores using the row numbers, ensuring that each result is paired with the correct relevance score.

   This function's overall purpose is to improve the relevance of search results by leveraging a semantic model hosted on Azure ML. This ensures that the results returned are more contextually relevant to the user's query.

Test the UDF

To see how semantic ranking works in your queries, you can execute a test query from pgAdmin. The example below shows how to perform semantic reranking of vector search results for the query "cost management and optimization" over the sow_chunks table using the semantic_reranking UDF you created.

1. In the pgAdmin query window, run the following query and observe the results.

   Execute the following SQL command in pgAdmin!

   WITH vector_results AS (
       SELECT content FROM sow_chunks c
       ORDER BY embedding <=> azure_openai.create_embeddings('embeddings', 'cost management and optimization')::vector
       LIMIT 10
   )
   SELECT content, relevance
   FROM semantic_reranking('cost management and optimization',  ARRAY(SELECT content from vector_results))
   ORDER BY relevance DESC
   LIMIT 3;
   

---

Next, you will update the get_sow_chunks function used by your copilot to use semantic ranking to improve the accuracy and quality of the copilot's responses.