indexBloat

read-onlyany connectionsweeps instancesweeps replication groupsweeps group

Synopsis

indexBloat(index, schema)

Description

Return physical statistics for one index, from whichever pgstattuple function matches its access method: pgstatindex for btree (tree level, leaf/internal/empty/deleted pages, average leaf density, leaf fragmentation), pgstatginindex for GIN (pending list pages and tuples, alongside the fastupdate setting and pending list limit that bound them), pgstathashindex for hash (bucket/overflow/bitmap/unused pages, live and dead items, free percent).

The access method is resolved from the catalog, so the caller does not need to know it; gist, spgist and brin are reported as unsupported by name, since pgstattuple ships no pgstat*index function for them. Metrics are deliberately NOT normalized across access methods -- 'access_method' says which set came back. Index size and idx_scan travel with the metrics, because a fragmented index nothing has scanned is a candidate for dropping rather than REINDEX. btree and hash read the whole index; GIN reads only the metapage and is always cheap.

Parameters

index requiredstring: the index's own name, not the name of the table it is on
schema requiredstring

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

Output

Physical statistics for one index, per access method. An absent extension or a missing grant is reported as {error, hint} instead.

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": "indexBloat",
    "arguments": {
      "schema": "shop",
      "index": "orders_pkey"
    }
  }
}

Result

{
  "schema": "shop",
  "index": "orders_pkey",
  "table": "orders",
  "access_method": "btree",
  "index_size": 27025408,
  "idx_scan": 9120004,
  "tree_level": 2,
  "leaf_pages": 3280,
  "internal_pages": 18,
  "empty_pages": 0,
  "deleted_pages": 12,
  "avg_leaf_density": 88.4,
  "leaf_fragmentation": 3.2
}

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.