MCP Tools Reference: databaseinsights.googleapis.com

Tool: get_index_recommendations

Fetches index advisor suggestions to optimize performance for a requested AlloyDB instance. Supports requesting recommendations for specific databases and a list of query IDs. Returns index recommendations including SQL commands (CREATE INDEX), target schema, relation, and columns, estimated storage size, and predicted query performance improvements (current vs estimated execution duration).

The following sample demonstrate how to use curl to invoke the get_index_recommendations MCP tool.

Curl Request 
                  
curl --location 'https://databaseinsights.googleapis.com/mcp' \
--header 'content-type: application/json' \
--header 'accept: application/json, text/event-stream' \
--data '{
  "method": "tools/call",
  "params": {
    "name": "get_index_recommendations",
    "arguments": {
      // provide these details according to the tool's MCP specification
    }
  },
  "jsonrpc": "2.0",
  "id": 1
}'

Input Schema

Message for requesting index recommendations (including SQL commands, storage size, and query improvements) for specific databases and queries on the requested instance.

BatchQueryIndexRecommendationsRequest

JSON representation 
{
  "parent": string,
  "fullResourceName": string,
  "databaseQueryIds": [
    {
      object (DatabaseQueryIds)
    }
  ]
}
Fields
parent

string

Required. The name of the location where we request recommendations. Format: projects/{project}/locations/{location}
fullResourceName

string

Required. Full resource path of the instance. Example: //alloydb.googleapis.com/clusters/{cluster}/instances/{instance}
databaseQueryIds[]

object (DatabaseQueryIds)

Required. The list of databases and associated query IDs for which recommendations are requested.

DatabaseQueryIds

JSON representation 
{
  "database": string,
  "queryIds": [
    string
  ]
}
Fields
database

string

Required. The database name for which the recommendations are requested.
queryIds[]

string (int64 format)

Optional. The list of query IDs for which the recommendations are requested in this database.

Output Schema

Response message containing index recommendations (SQL commands, schema, relation, columns, estimated storage size) and query performance improvements (current vs estimated execution duration).

BatchQueryIndexRecommendationsResponse

JSON representation 
{
  "fullResourceName": string,
  "databaseIndexRecommendations": [
    {
      object (DatabaseIndexRecommendation)
    }
  ]
}
Fields
fullResourceName

string

The resource identifier from the request.
databaseIndexRecommendations[]

object (DatabaseIndexRecommendation)

Recommendations and improvements grouped by database.

DatabaseIndexRecommendation

JSON representation 
{
  "database": string,
  "indexRecommendations": [
    {
      object (IndexRecommendation)
    }
  ],
  "recommendationTime": string,
  "queryImprovements": {
    string: {
      object (QueryImprovement)
    },
    ...
  }
}
Fields
database

string

The database name.
indexRecommendations[]

object (IndexRecommendation)

The list of suggested indexes for this database.
recommendationTime

string (Timestamp format)

The time at which these recommendations were generated.

Uses RFC 3339, where generated output will always be Z-normalized and use 0, 3, 6 or 9 fractional digits. Offsets other than "Z" are also accepted. Examples: "2014-10-02T15:01:23Z", "2014-10-02T15:01:23.045123456Z" or "2014-10-02T15:01:23+05:30".
queryImprovements

map (key: string (int64 format), value: object (QueryImprovement))

Performance improvements for specific queries, keyed by query_id.

An object containing a list of "key": value pairs. Example: { "name": "wrench", "mass": "1.3kg", "count": "3" }.

IndexRecommendation

JSON representation 
{
  "sqlCommand": string,
  "schema": string,
  "relation": string,
  "columns": [
    string
  ],
  "estimatedStorageSizeBytes": string,
  "impactedQueryIds": [
    string
  ],
  "impactedQueriesCount": integer,
  "indexRecommendationId": string
}
Fields
sqlCommand

string

The SQL command to create the recommended index.
schema

string

The name of the schema for the recommended index.
relation

string

The name of the relation (table) for the recommended index.
columns[]

string

The column names to create the index on.
estimatedStorageSizeBytes

string (int64 format)

The estimated storage size of the recommended index in bytes.
impactedQueryIds[]

string (int64 format)

The list of impacted query IDs for the recommended index.
impactedQueriesCount

integer

The number of impacted queries for the recommended index.
indexRecommendationId

string (int64 format)

A unique identifier for this recommendation, used to map improvements back to it.

Timestamp

JSON representation 
{
  "seconds": string,
  "nanos": integer
}
Fields
seconds

string (int64 format)

Represents seconds of UTC time since Unix epoch 1970-01-01T00:00:00Z. Must be between -62135596800 and 253402300799 inclusive (which corresponds to 0001-01-01T00:00:00Z to 9999-12-31T23:59:59Z).
nanos

integer

Non-negative fractions of a second at nanosecond resolution. This field is the nanosecond portion of the duration, not an alternative to seconds. Negative second values with fractions must still have non-negative nanos values that count forward in time. Must be between 0 and 999,999,999 inclusive.

QueryImprovementsEntry

JSON representation 
{
  "key": string,
  "value": {
    object (QueryImprovement)
  }
}
Fields
key

string (int64 format)
value

object (QueryImprovement)

QueryImprovement

JSON representation 
{
  "queryId": string,
  "indexRecommendationIds": [
    string
  ],
  "currentTotalExecutionDuration": string,
  "estimatedNewTotalExecutionDuration": string
}
Fields
queryId

string (int64 format)

The ID of the query being improved.
indexRecommendationIds[]

string (int64 format)

The IDs of the IndexRecommendations that contribute to this improvement.
currentTotalExecutionDuration

string (Duration format)

The current total execution duration for this query.

A duration in seconds with up to nine fractional digits, ending with 's'. Example: "3.5s".
estimatedNewTotalExecutionDuration

string (Duration format)

The predicted total execution duration after applying the indexes.

A duration in seconds with up to nine fractional digits, ending with 's'. Example: "3.5s".

Duration

JSON representation 
{
  "seconds": string,
  "nanos": integer
}
Fields
seconds

string (int64 format)

Signed seconds of the span of time. Must be from -315,576,000,000 to +315,576,000,000 inclusive. Note: these bounds are computed from: 60 sec/min * 60 min/hr * 24 hr/day * 365.25 days/year * 10000 years
nanos

integer

Signed fractions of a second at nanosecond resolution of the span of time. Durations less than one second are represented with a 0 seconds field and a positive or negative nanos field. For durations of one second or more, a non-zero value for the nanos field must be of the same sign as the seconds field. Must be from -999,999,999 to +999,999,999 inclusive.

Tool Annotations

Destructive Hint: ❌ | Idempotent Hint: ✅ | Read Only Hint: ✅ | Open World Hint: ❌