Use the index advisor

Important: The index advisor is only available if you have enabled Gemini in Databases. For more information on enabling Gemini in Databases, see Set up Database Center.

AlloyDB for PostgreSQL offers an index advisor that tracks the queries your database regularly handles. Periodically, it analyzes these queries to recommend new indexes that can increase the queries' performance.

You can view and query its recommendations as a table, or request an on-demand analysis and report at any time.

View the index advisor's recommendations

AlloyDB automatically runs the index advisor's analysis periodically. You can read its results through two table views located in each of your databases:

• google_db_advisor_recommended_indexes lists any recommended new indexes for its database. It also includes estimates of the storage required for each index, and the number of queries that each index can affect.

• google_db_advisor_workload_report lists each query for which the advisor recommends one or more new indexes. Each row summarizes the recommendations for the relevant query.

For example, to see the results of the most recent index-recommendation analysis, formatted as a table, run this query:

SELECT * FROM google_db_advisor_recommended_indexes;

If the index advisor's most recent analysis finds no recommendations, then this query returns a table with no rows.

Because all of these reports exist as ordinary database views, you can write queries that filter or present information in any way you wish. For example, to see a report that pairs recommended indexes with their full associated query, join the google_db_advisor_workload_report and google_db_advisor_workload_statements views on their respective query_id columns:

SELECT DISTINCT recommended_indexes, query
FROM google_db_advisor_workload_report r, google_db_advisor_workload_statements s
WHERE r.query_id = s.query_id;

Manually request an index analysis

Rather than wait for the index advisor's next scheduled analysis, you can request that AlloyDB run an analysis immediately and display its report. To do this, run this SQL function:

SELECT * FROM google_db_advisor_recommend_indexes();

After the analysis finishes, AlloyDB displays a table-formatted report with the description and estimated storage needs of any recommended indexes. If the analysis finds no new indexes to recommend, then the view contains no rows.

Note that the PostgreSQL user role that runs this command can affect the recommendations displayed. If this query is run by postgres or another user with the alloydbsuperuser role, then AlloyDB displays all of the index advisor's current recommendations. Otherwise, AlloyDB limits its display to index recommendations based on queries issued by the current database user.

Apply the index advisor's recommendations

The index column of the google_db_advisor_recommended_indexes view contains, in each row, a complete PostgreSQL CREATE INDEX DDL statement for generating the index recommended in that row.

To apply that row's recommendation, run that DDL statement, exactly as presented. This includes copying it onto your clipboard and pasting it into a psql prompt.

For example, consider this output from manually running an analysis, using the query described in the previous section:

                    index                   | estimated_storage_size_in_mb
--------------------------------------------+------------------------------
 CREATE INDEX ON "School"."Students"("age") |                            3
(1 row)

This report contains a single recommendation: adding a new single-column index on the age column in the School schema's Students table. To apply this advice, enter a DDL query exactly as represented within the report:

CREATE INDEX ON "School"."Students"("age");

View the index advisor's tracked queries

The google_db_advisor_workload_statements view contains a list of all the queries that the index advisor has tracked, as well as important metadata for each one, such as the following metrics:

• The number of times the instance executed this query
• The total time the instance spends processing these queries
• The ID of the database user running these queries

Clear the index advisor's tracked queries

You can reset the index advisor's behavior on an instance by clearing its tracked queries. To do this, run this SQL function:

SELECT google_db_advisor_reset();

AlloyDB immediately empties the index advisor's collection of tracked queries.

Configure the index advisor

While the index advisor is designed to work for most use cases with its default settings, you can fine-tune its behavior by setting various database flags. To learn more, see the Index advisor flags reference page.