Tune vector query performance in AlloyDB for PostgreSQL

This page describes how to tune your indexes to achieve faster query performance and better recall in AlloyDB for PostgreSQL.

Before you begin

Before you build a ScaNN index, complete the following:

Create a table with your data.
To avoid out-of-memory issues when you create the ScaNN index, ensure that the maintenance_work_mem and shared_buffers database flags are set to a value less than the total machine memory.
To use four-level indexes, you must first enable the Preview feature for your AlloyDB instance. To enable the Preview feature, choose one of the following two methods:
- Enable the scann.enable_preview_features database flag.
  
  For more information on configuring database flags, see Configure database flags .
- Set the session-level scann.max_allowed_num_levels database flag to 3 .
```
  SET 
  
 scann 
 . 
 max_allowed_num_levels 
  
 = 
  
 3 
 ; 
 
```

Tune a ScaNN index

To determine the number of levels needed for your ScaNN index, see the following table.

Number of vector rows in your table

Number of levels for your ScaNN index

[0..10 million]

Two

[10 million..100 million]

Choose one of the following metrics to prioritize:

Search recall: two
Index build time: three

[100 million..1 billion]

Choose one of the following metrics to prioritize:

Search recall: three
Index build time: four (in Preview )

[1 billion..10 billion]

Four (in Preview )

Use the following ScaNN indexes as examples for tuning parameters for a table with 1 million rows.

Two-level tree index

 SET 
  
 LOCAL 
  
 scann 
 . 
 num_leaves_to_search 
  
 = 
  
 1 
 ; 
 SET 
  
 LOCAL 
  
 scann 
 . 
 pre_reordering_num_neighbors 
 = 
 50 
 ; 
 CREATE 
  
 INDEX 
  
 my_scann_index 
  
 ON 
  
 my_table 
  
 USING 
  
 scann 
  
 ( 
 vector_column 
  
 cosine 
 ) 
 WITH 
  
 ( 
 num_leaves 
  
 = 
  
 [ 
 power 
 ( 
 1000000 
 , 
  
 1 
 / 
 2 
 )]);

Three-level tree index

 SET 
  
 LOCAL 
  
 scann 
 . 
 num_leaves_to_search 
  
 = 
  
 10 
 ; 
 SET 
  
 LOCAL 
  
 scann 
 . 
 pre_reordering_num_neighbors 
 = 
 50 
 ; 
 CREATE 
  
 INDEX 
  
 my_scann_index 
  
 ON 
  
 my_table 
  
 USING 
  
 scann 
  
 ( 
 vector_column 
  
 cosine 
 ) 
 WITH 
  
 ( 
 num_leaves 
  
 = 
  
 [ 
 power 
 ( 
 1000000 
 , 
  
 2 
 / 
 3 
 )], 
  
 max_num_levels 
  
 = 
  
 2 
 );

Four-level tree index

 SET 
  
 LOCAL 
  
 scann 
 . 
 num_leaves_to_search 
  
 = 
  
 100 
 ; 
 SET 
  
 LOCAL 
  
 scann 
 . 
 pre_reordering_num_neighbors 
 = 
 50 
 ; 
 CREATE 
  
 INDEX 
  
 my 
 - 
 scann 
 - 
 index 
  
 ON 
  
 my 
 - 
 table 
  
 USING 
  
 scann 
  
 ( 
 vector_column 
  
 cosine 
 ) 
 WITH 
  
 ( 
 num_leaves 
  
 = 
  
 [ 
 power 
 ( 
 1000000 
 , 
  
 3 
 / 
 4 
 )], 
  
 max_num_levels 
  
 = 
  
 3 
 );

For more information about ScaNN indexes, see the following pages:

Analyze your queries

Use the EXPLAIN ANALYZE command to analyze your query insights as shown in the following example SQL query.

   
 EXPLAIN 
  
 ANALYZE 
  
 SELECT 
  
 result 
 - 
 column 
  
 FROM 
  
 my_table 
  
 ORDER 
  
 BY 
  
 EMBEDDING_COLUMN 
  
< - 
>  
 embedding 
 ( 
 'text-embedding-005' 
 , 
  
 'What is a database?' 
 ):: 
 vector 
  
 LIMIT 
  
 1 
 ;

The example response QUERY PLAN includes information such as the time taken, the number of rows scanned or returned, and the resources used.

 Limit  (cost=0.42..15.27 rows=1 width=32) (actual time=0.106..0.132 rows=1 loops=1)
  ->  Index Scan using my_scann_index on my_table  (cost=0.42..858027.93 rows=100000 width=32) (actual time=0.105..0.129 rows=1 loops=1)
        Order By: (embedding_column <-> embedding('text-embedding-005', 'What is a database?')::vector(768))
        Limit value: 1
Planning Time: 0.354 ms
Execution Time: 0.141 ms

View vector index metrics

You can use vector index metrics to review performance of your vector index, identify areas for improvement, and tune your index based on the metrics, if needed. The pg_stat_ann_indexes view helps you understand the state of index utilization while the pg_stat_ann_index_creation view provides information about rows created at the time of index creation.

To view index utilization metrics, run the following command:

  SELECT 
  
 * 
  
 FROM 
  
 pg_stat_ann_indexes 
 ;

You see output similar to the following:

 -[ RECORD 1 ]----------+---------------------------------------------------------------------------
relid                  | 271236
indexrelid             | 271242
schemaname             | public
relname                | t1
indexrelname           | t1_ix1
indextype              | scann
indexconfig            | {num_leaves=100,max_num_levels=1,quantizer=SQ8}
indexsize              | 832 kB
indexscan              | 0
insertcount            | 250
deletecount            | 0
updatecount            | 0
partitioncount         | 100
distribution           | {"average": 3.54, "maximum": 37, "minimum": 0, "outliers": [37, 12, 11, 10, 10, 9, 9, 9, 9, 9]}
distributionpercentile |{"10": { "num_vectors": 0, "num_partitions": 0 }, "25": { "num_vectors": 0, "num_partitions": 30 }, "50": { "num_vectors": 3, "num_partitions": 30 }, "75": { "num_vectors": 5, "num_partitions": 19 }, "90": { "num_vectors": 7, "num_partitions": 11 }, "95": { "num_vectors": 9, "num_partitions": 5 }, "99": { "num_vectors": 12, "num_partitions": 4 }, "100": { "num_vectors": 37, "num_partitions": 1 }}

To view number of rows created at the time of index creation, run the following command:

  SELECT 
  
 * 
  
 FROM 
  
 pg_stat_ann_index_creation 
 ;