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.

Tune an IVFFlat index

Tuning the values you set for the lists and theivfflat.probes parameters can help optimize application performance:

Tuning parameter Description Parameter type
lists The number of lists created during index building. The starting point for setting this value is (rows)/1000 for up to one million rows, and sqrt(rows) for more than one million rows. Index creation
ivfflat.probes The number of nearest lists to explore during search. The starting point for this value is
sqrt(lists).		 Query runtime

Before you build an IVFFlat index, make sure that your database's max_parallel_maintenance_workers flag is set to a value sufficient to expedite the index creation on large tables.

Consider the following example that shows an IVFFlat index with the tuning parameters set:

SET LOCAL ivfflat.probes = 10;

CREATE INDEX my_ivfflat_index ON my_table
  USING ivfflat (vector_column cosine)
  WITH (lists = 100);

Handle DML invalidations due to acceleration with columnar engine

If you chose to accelerate your vector searches with the columnar engine, be aware that DML and DDL invalidations on the base tables can impact vector query performance. In case of high DML throughput, consider tuning the google_columnar_engine.refresh_threshold_percentage database flag or manually refreshing the index using the google_columnar_engine_refresh_index command.

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

What's next