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.
Enable the index advisor
To enable the index advisor, set your primary instance's
google_db_advisor.enabled flag to
on. After you set this flag, your
primary instance automatically restarts. The index advisor then
begins tracking queries that the primary instance receives across
all of the cluster's databases.
For more information about setting flags on AlloyDB instances, see Configure an instance's database flags.
View the index advisor's recommendations
AlloyDB automatically runs the index advisor's analysis periodically, starting one day after you enable the advisor. You can read its results through two table views located in each of your databases:
google_db_advisor_recommended_indexeslists 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_reportlists 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_statements views on their
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
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
Apply the index advisor's recommendations
index column of the
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
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
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
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:
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.