duplicateIndexes

Return indexes that duplicate or are covered by another index on the same table.

read-onlyany connectionsweeps instancesweeps replication groupsweeps group

Synopsis

duplicateIndexes(schema, [table])

Description

Return indexes that duplicate or are covered by another index on the same table. 'identical' groups indexes whose key columns, operator classes, collations, sort order, INCLUDE columns and partial predicate all match; 'redundant' reports an index whose key columns are a leading prefix of a wider index that also covers its INCLUDE columns. Comparison is by column expression rather than attribute number, so expression indexes and differing sort orders are handled correctly, and a unique index is never called redundant for being a prefix.

Each entry carries size, idx_scan, the backing constraint name, and the replica identity and validity flags, since those decide whether it can be dropped at all. READ counters_since BEFORE idx_scan: it is when this database's statistics were last reset, and idx_scan counts only since then. A zero on a recently reset database says nothing about a monthly or quarterly index. It is a lower bound -- pg_stat_reset_single_table_counters() zeroes one relation without moving it.

Parameters

schema requiredstring
table optionalstring: restrict to one table; omit to check every table in the schema

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

Output

Indexes that duplicate or cover another. An absent extension or a missing grant is reported as {error, hint} instead.

Field	Type
identical	array \| null
redundant	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": "duplicateIndexes",
    "arguments": {
      "schema": "shop"
    }
  }
}

Result

{
  "identical": [],
  "redundant": [
    {
      "table": "shop.orders",
      "index": "orders_customer_id_idx",
      "definition": "CREATE INDEX orders_customer_id_idx ON shop.orders USING btree (customer_id)",
      "size": 33464320,
      "idx_scan": 712113,
      "covered_by": "orders_customer_created_idx",
      "covered_by_definition": "CREATE INDEX orders_customer_created_idx ON shop.orders USING btree (customer_id, created_at)",
      "reason": "a leading prefix of a wider index"
    }
  ]
}

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.