suggestIndexes

read-onlyany connectionsweeps instancesweeps replication groupsweeps group

Synopsis

suggestIndexes([forbidden_am], [min_filter], [min_selectivity])

Description

Return index suggestions from pg_qualstats' own index advisor for this database: each is a complete CREATE INDEX statement with the query_ids of the statements it would serve, plus not_indexable, the predicates that filter heavily but use an operator no access method supports, which an index cannot fix. Nothing is built: the advisor only reads pg_qualstats. A suggestion is a hypothesis, not a result -- pass its ddl to evaluateIndex's create, with a statement from explainQuery or statementStats for one of its query_ids, to see whether the planner would actually use it.

On PostgreSQL 14 and 15 a statement recovered from pg_stat_statements keeps its $n placeholders, which only 16's EXPLAIN (GENERIC_PLAN) can plan, so substitute representative values before passing it. The advisor considers filter predicates averaging at least min_filter rows removed per execution and removing at least min_selectivity percent of the rows they see; lower both on a small or quiet database. Constants are never returned, as with predicateStats.

Requires pg_qualstats 2.1 or later, the first whose advisor names the statements each suggestion serves, in shared_preload_libraries, and refuses rather than advise from an empty sample when it is installed without it.

Parameters

forbidden_am optionalarray: access methods never to suggest, e.g. ["hash"]
min_filter optionalinteger: average rows a predicate must remove per execution to be considered. Defaults to 1000
min_selectivity optionalinteger: percentage of evaluated rows a predicate must remove to be considered, 0 to 100. Defaults to 30

Also accepts connection, instance, replication_group, group, role, described once under arguments every tool takes.

Output

Index suggestions from pg_qualstats' index advisor. An absent extension or a missing grant is reported as {error, hint} instead.

Field	Type
indexes	array \| null
not_indexable	array \| null

Scope

The counters here are each server's own, so members of a replication group legitimately disagree and the answer is their sum, not the primary's copy.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "suggestIndexes",
    "arguments": {}
  }
}

Result

{
  "indexes": [
    {
      "ddl": "CREATE INDEX ON shop.orders USING btree (status)",
      "query_ids": [
        "-8811029349218800117",
        "5520184417300021190"
      ]
    }
  ],
  "not_indexable": [
    {
      "predicate": "shop.customers.email ~~* ?",
      "query_ids": [
        "7719203348810222904"
      ]
    }
  ],
  "min_filter": 1000,
  "min_selectivity": 30,
  "preloaded": true,
  "extension_version": "2.1.4"
}

Invented values on a fictional shop database, shaped by and checked against this tool's output schema. Real output is returned as structuredContent to clients that negotiate MCP 2025-06-18 or later.