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

Cloud SQL for PostgreSQL offers a fully managed index advisor that tracks thequeries your database handles regularly. Periodically, the index advisoranalyzes these queries to recommend new indexes that can improve queryperformance. The index advisor lets you detect and fix performanceissues with systems and queries.

How does the index advisor work?

The index advisor helps you improve query processing bydoing the following:

• Recommend a set of indexes with SQL commands to create indexes.
• Provide data to help you evaluate recommended indexes, for example,estimated storage size and impact of indexes on a query.

Index recommendations are encrypted at rest.

Limitations

Cloud SQL for PostgreSQL index advisor has the following limitations:

• The index advisor providesCREATE INDEX recommendations only.
• The index advisor doesn't support instances with the following configurations:
• Cloud SQL Enterprise edition instances
• Read replica instances

Before you begin

To obtain index advisor recommendations,you must use Cloud SQL Enterprise Plus edition and enablequery insights for Cloud SQL Enterprise Plus editionfor your Cloud SQL instance.

Required roles and permissions

To get the permissions that you need to obtain index advisor recommendations, ask your administrator to grant you theCloud SQL Viewer (roles/cloudsql.viewer) IAM role on the project that hosts the Cloud SQL instance. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to obtain index advisor recommendations. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Enable index advisor recommendations

To enable index advisor recommendations, do the following:

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

   .
2. To open theOverview page of an instance, click the instance name..
3. In theConfiguration tile, clickEdit configuration.
4. In theCustomize your instance section, expandQuery insights.
5. Make sureEnable Query insights is enabled.
6. If not already selected, selectEnable Enterprise Plus features.
7. SelectEnable index advisor.
8. ClickSave.

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. In theConfiguration tile, clickEdit configuration.
4. In theCustomize your instance section, expandQuery insights.
5. Clear theEnable index advisor checkbox.
6. ClickSave.

View index advisor recommendations

Cloud SQL automatically runs the index advisor analysisperiodically. To view index advisor recommendations, use the Query insights dashboard.You can also view and query the index advisor recommendations as a table or requestan on-demand analysis and report at any time.

View and filter recommendations in the Query insights dashboard

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights.
4. The index advisor recommendations are displayed intheRecommendation column of theTop queries and tagssection.
5. Optional: To view only the queries withCREATE INDEX recommendations, add afilter forRecommendation: Create Indexes.

View recommendations for a query

To view index recommendations for a specific query, follow thesesteps:

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights.
4. In theTop queries and tags section, clickQueries.
5. To get recommendation details for a query, do one of the following:
   • Click a query to learn more about the recommendations for the selectedquery, including the following information:
     • Performance impact (high, medium, and low): Theestimated query speed after all recommended indexes are created.
     • Recommendations: Create index recommendations.
     • Tables impacted: The number of tables that will beimpacted when indexes are created.
     • Additional estimated storage needed: The estimatedstorage size needed to create all the recommended indexes.
     • Number of impacted queries: The total number ofqueries in the workload impacted by the index recommendations. Anindex can benefit multiple queries.
   • ClickCreate Indexes for a specific query to learn detailedrecommendations about creating indexes to improve query performance.

View recommendations as a database table view

• google_db_advisor_recommended_indexes: lists any recommended newindexes for each database. It also includes estimates of thestorage required for each index, and the number of queries that eachindex can affect.

• google_db_advisor_workload_report: lists each query forwhich the advisor recommends one or more new indexes. Each rowsummarizes the recommendations for the relevant query.

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

SELECT*FROMgoogle_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 canwrite queries that filter or present this information. For example,to see a report that pairs recommended indexes withtheir full associated query, join thegoogle_db_advisor_workload_report andgoogle_db_advisor_workload_statements views on theirrespectivequery_id columns:

SELECTDISTINCTrecommended_indexes,queryFROMgoogle_db_advisor_workload_reportr,google_db_advisor_workload_statementssWHEREr.query_id=s.query_id;

Manually request an index analysis

Rather than wait for the index advisor's next scheduled analysis, youcan request that Cloud SQL for PostgreSQL run an analysis immediately anddisplay its report. For Cloud SQL for PostgreSQL, you need to wait at least15 minutes after enabling index advisor to run a manual analysis.To do this, run this SQL function:

SELECT*FROMgoogle_db_advisor_recommend_indexes();

After the analysis finishes, Cloud SQL for PostgreSQLdisplays a table-formatted report with the description and estimatedstorage needs of any recommended indexes. If the analysis finds no newindexes to recommend, then the view contains no rows.

Note thatthe user rolethat runs this command can affect the recommendations displayed.Cloud SQL for PostgreSQL limits its display to index recommendations based onqueries issued by the current database user.

Create recommended indexes

You can create recommended indexes from the Query insights dashboard orfrom a database table view.

Create a recommended index using the Query insights dashboard

To create a recommended index using the Query insights dashboard, do thefollowing:

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights.
4. In theTop dimensions by database load table, clickQueries.
5. ClickCreate Indexes for a specific query.
6. ClickCopy all index commands. TheCREATE INDEX commands are copiedto your clipboard.
7. Connect to the primary instance on the command line.

8. To create the recommended indexes, run the commands that were copied toyour clipboard, for example:

   CREATEINDEXON"public"."demo_order"("customer_id");

Create a recommended index using a database table view

Theindex column of thegoogle_db_advisor_recommended_indexes viewcontains, in each row, a complete PostgreSQLCREATE INDEX DDLstatement for generating the index recommended in that row.

To apply that row's recommendation, run that DDL statement, exactly aspresented. This includes copying it onto your clipboard and pasting itinto apsql 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-columnindex on theage column in theSchool schema'sStudentstable. To apply this advice, enter a DDL query asrepresented within the report:

CREATEINDEXON"School"."Students"("age");

View impacted queries

1. In the Google Cloud console, go to theCloud SQL Instances page.

   Go to Cloud SQL Instances

2. To open theOverview page of an instance, click the instance name.
3. ClickQuery insights.
4. In theTop dimensions by database load table, clickQueries.
5. ClickCreate indexes for a specific query.
6. ClickShow impacted queries.
7. Click a query to learn details about the impacted query.

View index advisor tracked queries

Thegoogle_db_advisor_workload_statements view contains a list of allthe queries that the index advisor has tracked, as well asimportant 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 byclearing its tracked queries. To do this, run this SQL function:

SELECTgoogle_db_advisor_reset();

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

What's next

• Use query insights