Console
    Start free

    Rank search results

    Learn how to rank and rerank your search results for applications using Vertex AI ranking models such as semantic-ranker-default-003 . You can use the ai.rank() function to score documents based on relevance to a query, and improve vector search results by reranking them for better query ordering.

    The Vertex AI ranking API takes a list of documents and ranks those documents based on how relevant the documents are to a given query (a search string). When you use the ai.rank() function, it returns scores for how well a document answers a given query.

    To use instructions on this page, you must have an understanding of AlloyDB for PostgreSQL and be familiar with generative AI concepts.

    AlloyDB reserves, and creates, the ai schema.

    Before you begin

    Before you rank search results, do the following:

    Enable the Discovery Engine API

    Console

    1. Enable the API
    2. In the Confirm project step, click Next to confirm the name of the project you are going to make changes to.
    3. In the Enable APIs step, click Enable to enable the Discovery Engine API. If you already enabled this API, you won't see it listed here.

    gcloud

    To use ranking models, you must enable the Discovery Engine API.
    Replace PROJECT_ID with your Google Cloud project ID and PROJECT_NUMBER with your corresponding project number.

     # 
Enable  
Discovery  
Engine  
API gcloud services enable discoveryengine.googleapis.com --project= PROJECT_ID 
 
 gcloud projects add-iam-policy-binding PROJECT_ID 
\ 
 --member="serviceAccount:service- PROJECT_NUMBER 
@gcp-sa-alloydb.iam.gserviceaccount.com" \ 
 --role="roles/discoveryengine.viewer"

    Model registration for ranking isn't required for Vertex AI models. You can use the Vertex AI model name as the model_id , which is shown in the following example.

      
SELECT  
index,  
score  
FROM  
ai.rank ( 
  
 model_id 
  
 = 
>  
 'semantic-ranker-default-003' 
,  
 search_string 
  
 = 
>  
 'Affordable family-friendly vacation spots in Southeast Asia?' 
,  
 documents 
  
 = 
>  
ARRAY [ 
  
 'Luxury resorts in South Korea' 
,  
 'Family vacation packages for Vietnam: Ha Long Bay and Hoi An' 
,  
 'Budget-friendly beaches in Thailand perfect for families' 
,  
 'A backpacker guide to solo travel in India' 
 ])

    A common use case for the semantic ranker is to rerank the results returned by vector search for better query ordering. The following example shows how to use the semantic ranking model for this use case. The example retrieves an initial result set for the query personal fitness equipment using vector search. These results are then re-ranked to return the top five results.

      
WITH  
initial_ranking  
AS  
 ( 
  
SELECT  
id,  
description,  
ROW_NUMBER () 
  
OVER  
 () 
  
AS  
ref_number  
FROM  
product  
ORDER  
BY  
embedding  
< = 
>  
google_ml.embedding ( 
  
 'gemini-embedding-001' 
,  
 'personal fitness equipment' 
 ) 
::vector  
LIMIT  
 10 
  
 ) 
,  
reranked_results  
AS  
 ( 
  
SELECT  
index,  
score  
FROM  
ai.rank ( 
  
 model_id 
  
 = 
>  
 'semantic-ranker-default-003' 
,  
 search_string 
  
 = 
>  
 'personal fitness equipment' 
,  
 documents 
  
 = 
>  
 ( 
SELECT  
ARRAY_AGG ( 
description  
ORDER  
BY  
ref_number ) 
  
FROM  
initial_ranking ) 
,  
 top_n 
  
 = 
>  
 5 
 ) 
  
 ) 
  
SELECT  
id,  
description  
FROM  
initial_ranking,  
reranked_results  
WHERE  
initial_ranking.ref_number  
 = 
  
reranked_results.index  
ORDER  
BY  
reranked_results.score  
DESC ;

    For a list of available models and use cases, see Supported models .

    Integrate with Vertex AI and install the extension

    1. Configure user access to Vertex AI models .
    2. Verify that the latest version of google_ml_integration is installed.

      1. To check the installed version, run the following command:

          
 SELECT 
  
 extversion 
  
 FROM 
  
 pg_extension 
  
 WHERE 
  
 extname 
  
 = 
  
 'google_ml_integration' 
 ; 
  
 extversion 
  
  
 
         
  
 1 
 . 
 5 
 . 
 2 
  
 ( 
 1 
  
 row 
 )

      2. If the extension isn't installed or if the installed version is earlier than 1.5.2, update the extension.

          
 CREATE 
  
 EXTENSION 
  
 IF 
  
 NOT 
  
 EXISTS 
  
 google_ml_integration 
 ; 
  
 ALTER 
  
 EXTENSION 
  
 google_ml_integration 
  
 UPDATE 
 ;

        If you experience issues when you run the preceding commands, or if the extension isn't updated to version 1.5.2 after you run the preceding commands, contact Google Cloud support .

    3. To use the AlloyDB AI query engine functionality, set the google_ml_integration.enable_ai_query_engine flag to true .

      SQL

      1. Enable the AI query engine for the current session.
          
 SET 
  
 google_ml_integration 
 . 
 enable_ai_query_engine 
  
 = 
  
 true 
 ;
      2. Enable features for a specific database across sessions.
          
 ALTER 
  
 DATABASE 
  
  DATABASE_NAME 
 
  
 SET 
  
 google_ml_integration 
 . 
 enable_ai_query_engine 
  
 = 
  
 'on' 
 ;
      3. Enable the AI query engine for a specific user across sessions and databases.
          
 ALTER 
  
 ROLE 
  
 postgres 
  
 SET 
  
 google_ml_integration 
 . 
 enable_ai_query_engine 
  
 = 
  
 'on' 
 ;

      Console

      To modify the value of the google_ml_integration.enable_ai_query_engine flag, follow the steps in Configure an instance's database flags .

      gcloud

      To use the gcloud CLI, you can install and initialize the Google Cloud CLI, or you can use Cloud Shell .

      You can modify the value of the google_ml_integration.enable_ai_query_engine flag. For more information, see Configure an instance's database flags .

       gcloud alloydb instances update INSTANCE_ID 
\ 
 --database-flags google_ml_integration.enable_ai_query_engine=on \ 
 --region= REGION_ID 
\ 
 --cluster= CLUSTER_ID 
\ 
 --project= PROJECT_ID

    Required roles

    To get the permissions that you need to use ranking models from Discovery Engine, ask your administrator to grant you the Discovery Engine Viewer ( roles/discoveryengine.viewer ) Identity and Access Management (IAM) role on your project . For more information about granting roles, see Manage access to projects, folders, and organizations .

    You might also be able to get the required permissions through custom roles or other predefined roles .

    Rank your search results

    The following SQL query shows how to rank your search results :

      SELECT 
  
 ai 
 . 
 rank 
 ( 
  
 model_id 
  
 = 
>  
 ' MODEL_ID 
' 
 , 
  
 search_string 
  
 = 
>  
 ' SEARCH_STRING 
' 
 , 
  
 documents 
  
 = 
>  
 ARRAY 
 [ 
 ' DOCUMENT_1 
' 
 , 
  
 ' DOCUMENT_2 
' 
 , 
  
 ' DOCUMENT_3 
' 
 ]);

    Replace the following:

    Parameter Description
    MODEL_ID A unique ID for the model endpoint that you define.
    SEARCH_STRING A search string against which the records are ranked.
    DOCUMENTS A unique string that identifies the record.

    For a list of the supported Vertex AI ranking models, see Supported models .

    Examples

    To rank search results using a Vertex AI ranking model, run the following query:

      SELECT 
  
 index 
 , 
  
 score 
 FROM 
  
 ai 
 . 
 rank 
 ( 
  
 model_id 
  
 = 
>  
 'semantic-ranker-default-003' 
 , 
  
 search_string 
  
 = 
>  
 'AlloyDB is a PostgreSQL compatible AI database that is ready for production.' 
 , 
  
 documents 
  
 = 
>  
 ARRAY 
 [ 
  
 'Alloys are made from combination of metals' 
 , 
  
 'The best enterprise-ready PostgreSQL database.' 
 , 
  
 'You can feel the heat in Alloy apartments.' 
 ]);

    The response is a table that shows each document and the score based on relevance to the search query. The following is the sample response:

       
 index 
  
 | 
  
 score 
 -------+------------ 
  
 2 
  
 | 
  
 0 
 . 
 33 
  
 1 
  
 | 
  
 0 
 . 
 28 
  
 3 
  
 | 
  
 0 
 . 
 16 
 ( 
 3 
  
 rows 
 )

    Consider an example AlloyDB database with a list of review descriptions that are converted to embeddings. The following sample code snippet shows how to use the ranking model to retrieve the name of the top-ranked products based on their review descriptions' semantic similarity to a query.

      WITH 
  
 initial_ranking 
  
 AS 
  
 ( 
  
 SELECT 
  
 product_id 
 , 
  
 name 
 , 
  
 review 
 , 
  
 review_id 
 , 
  
 ROW_NUMBER 
 () 
  
 OVER 
  
 () 
  
 AS 
  
 ref_number 
  
 FROM 
  
 user_reviews 
  
 ORDER 
  
 BY 
  
 review_desc_embedding 
  
< = 
>  
 google_ml 
 . 
 embedding 
 ( 
  
 'gemini-embedding-001' 
 , 
  
 'good desserts' 
 ):: 
 vector 
  
 LIMIT 
  
 10 
  
 ), 
  
 reranked_results 
  
 AS 
  
 ( 
  
 SELECT 
  
 index 
 , 
  
 score 
  
 FROM 
  
 ai 
 . 
 rank 
 ( 
  
 model_id 
  
 = 
>  
 'semantic-ranker-512' 
 , 
  
 search_string 
  
 = 
>  
 'good desserts' 
 , 
  
 documents 
  
 = 
>  
 ( 
 SELECT 
  
 ARRAY_AGG 
 ( 
 review 
  
 ORDER 
  
 BY 
  
 ref_number 
 ) 
  
 FROM 
  
 initial_ranking 
 ), 
  
 top_n 
  
 = 
>  
 5 
 ) 
  
 ) 
 SELECT 
  
 product_id 
 , 
  
 name 
 FROM 
  
 initial_ranking 
 , 
  
 reranked_results 
 WHERE 
  
 initial_ranking 
 . 
 ref_number 
  
 = 
  
 reranked_results 
 . 
 index 
 ORDER 
  
 BY 
  
 reranked_results 
 . 
 score 
  
 DESC 
 ;

    What's next
    Create a Mobile Website
    View Site in Mobile | Classic
    Share by: