Use index advisor

This page describes the Cloud SQL for PostgreSQL index advisor, and how you can view and apply its index recommendations.

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

You can view and query the index advisor's recommendations as a table, or request an on-demand analysis and report at any time.

Enable index advisor recommendations

To enable index advisor recommendations, set up Gemini in Databases.

Disable index advisor recommendations

To disable index advisor recommendations, remove the cloudsql.enable_index_advisor flag from your Cloud SQL for PostgreSQL instance. For more information about how to remove a flag to your instance, see Configure database flags.

View the index advisor's recommendations

Cloud SQL for PostgreSQL automatically runs the index advisor's analysis periodically.

You can read its results through the following table views located in each of your databases:

  • google_db_advisor_recommended_indexes: lists any recommended new indexes for each 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 this information. 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 Cloud SQL for PostgreSQL run an analysis immediately and display its report. For Cloud SQL for PostgreSQL, you need to wait at least 15 minutes after enabling index advisor to run a manual analysis. To do this, run this SQL function:

SELECT * FROM google_db_advisor_recommend_indexes();

After the analysis finishes, Cloud SQL for PostgreSQL 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 user role that runs this command can affect the recommendations displayed. Cloud SQL for PostgreSQL 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 single-column index on the age column in the School schema's Students table. To apply this advice, enter a DDL query 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 each 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();

Cloud SQL for PostgreSQL empties the index advisor's collection of tracked queries immediately.