Use the AlloyDB for PostgreSQL index advisor with query insights

This page describes how you can use the AlloyDB for PostgreSQL index advisor with query insights.

You can use the index advisor with query insights to view index advisor recommendations and create recommended indexes to improve query performance. To learn more about the index advisor, see Index advisor overview.

You can use Gemini Cloud Assist to help you monitor and troubleshoot your AlloyDB resources. For more information, see Monitor and troubleshoot with Gemini assistance.

To learn how to use the index advisor to view and query recommendations as a table, or to request an on-demand analysis and report, see Use the index advisor.

Before you begin

To access the query insights dashboard, enable access to AlloyDB in your Google Cloud project.

Required roles

To use the AlloyDB index advisor, you need permissions to do the following:

  • To access index recommendations, you need permissions to access the AlloyDB query insights dashboard.
  • To edit the index advisor settings, you need permissions to update AlloyDB instances.

To get these permissions, ask your administrator to grant you one of the following roles:

  • Basic viewer (roles/viewer)
  • Database Insights viewer (roles/databaseinsights.viewer)

For more information about granting roles, see Manage access.

Disable index advisor recommendations

To disable index advisor recommendations, follow these steps:

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query Insights.

  4. Click Edit settings.

  5. Disable the index advisor recommendations by deselecting Enable recommendations. Index advisor recommendations are enabled by default.

Change the refresh frequency for index advisor recommendations

To change the refresh frequency, follow these steps:

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query insights.

  4. Click Edit settings.

  5. Increase or decrease the refresh frequency for index advisor recommendations. The default refresh frequency is every 24 hours.

View and filter the index advisor recommendations

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query insights. The index advisor recommendations are displayed in the Recommendations column of the Top dimensions by database load table.

  4. (Optional) To view only queries with CREATE INDEX recommendations, add a filter for Recommendation: Create Indexes.

View recommendations for a query

To view details of index recommendations for a specific query, follow these steps:

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query insights.

  4. In the Top dimensions by database load table, click Queries.

  5. To learn recommendation details about a query, do one of the following:

    • Click a query to learn more about recommendations for the selected query, including the following information:
      • Performance impact (high, medium, and low): The estimated query speed after all recommended indexes are created.
      • Recommendations: Create index recommendations.
      • Tables impacted: The number of tables that will be impacted when indexes are created.
      • Additional estimated storage needed: The estimated storage size needed to create all the recommended indexes.
      • Number of impacted queries: The total number of queries in the workload impacted by the index recommendations. An index may benefit multiple queries.
    • Click Create Indexes for a specific query to learn detailed recommendations about creating indexes to improve query performance.

View impacted queries

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query insights.

  4. In the Top dimensions by database load table, click Queries.

  5. Click Create indexes for a specific query.

  6. Click Show impacted queries.

  7. Click a query to learn details about the impacted query.

  1. In the Google Cloud console, go to the Clusters page.

    Go to Clusters

  2. From the list of clusters and instances, click an instance.

  3. Click Query insights.

  4. In the Top dimensions by database load table, click Queries.

  5. Click Create Indexes for a specific query.

  6. Click Copy all index commands. The CREATE INDEX commands are copied to your clipboard.

  7. Connect to the primary instance on the command line.

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

    CREATE INDEX ON "public"."demo_order" ("customer_id");

What's next