MCPg - Production-grade PostgreSQL MCP Server

Overview Schema Source Related Servers Discussions Admin

Recommend index drops

recommend_index_drops

Read-only

Finds unused or rarely scanned PostgreSQL indexes and returns ready-to-run DROP INDEX CONCURRENTLY statements to reduce storage and write overhead.

Instructions

Sibling of recommend_indexes for indexes to remove. Walks pg_stat_user_indexes + pg_stat_user_tables for existing indexes that look like pure cost — large on disk but never (or barely) scanned. Three reason codes, descending strength: never_used (no recorded idx_scans since the last stats reset — candidate for drop, but verify before removal), scan_no_fetch (planner picks it but it returns no rows — usually existence-check pattern), rarely_used (scan rate below low_scan_ratio of the table's total scan activity). Primary-key / unique / exclusion-constraint indexes are excluded (dropping those would be a schema change, not a performance win); indexes below min_index_size_bytes are skipped too. Returns a ready-to-run DROP INDEX CONCURRENTLY statement per candidate. Read-only advisor — execution is on the operator.

Example: recommend_index_drops(schema='public', min_index_size_bytes=1000000, low_scan_ratio=0.01)

Tool call template

This tool declares no required parameters. Add optional parameters from the input schema as needed.

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

Input Schema

Name	Required	Description
`schema`	No
`database`	No	Optional: a database id from list_databases, to target a configured secondary (read-only) database; omit for the primary. Database names that are not configured ids are rejected.
`low_scan_ratio`	No
`min_index_size_bytes`	No

Output Schema

Name	Required	Description	Default
`result`	Yes

Schema Changelog

Changes observed during successful MCP inspections.

Changed1 schema field changedv0.8.4Nov 5, 2026

changedInput schema / properties / database / description

Previous value: -"Optional: target a configured secondary (read-only) database by name; omit for the primary. Call list_databases to see the configured ids."New value: +"Optional: a database id from list_databases, to target a configured secondary (read-only) database; omit for the primary. Database names that are not configured ids are rejected."

First observedv0.6.8Jul 6, 2026

TDQS

A4.6/5.0

Behavior5/5

Does the description disclose side effects, auth requirements, rate limits, or destructive behavior?

Annotations already declare readOnlyHint=true and openWorldHint=false, and the description goes well beyond that: it enumerates the three reason codes with their semantics and relative strength, explains that never_used is only a candidate requiring verification, discloses the constraint-index and size exclusions, and clarifies that it is an advisor that emits DDL text rather than executing it. That is exactly the extra behavioral context an agent needs before recommending a destructive statement.

Agents need to know what a tool does to the world before calling it. Descriptions should go beyond structured annotations to explain consequences.

Conciseness4/5

Is the description appropriately sized, front-loaded, and free of redundancy?

The purpose and sibling distinction are front-loaded, and the reason codes are ordered by descending strength, which is good structure. It is dense with parentheticals and slightly long, but nearly every clause carries substantive information rather than restating the name.

Shorter descriptions cost fewer tokens and are easier for agents to parse. Every sentence should earn its place.

Completeness5/5

Given the tool's complexity, does the description cover enough for an agent to succeed on first attempt?

For a read-only advisor over statistics catalogs, the definition covers method, thresholds, exclusions, ranking of reasons, and output shape, and an output schema exists so return values need no further explanation. An agent has everything needed to call it and to interpret the results responsibly.

Complex tools with many parameters or behaviors need more documentation. Simple tools need less. This dimension scales expectations accordingly.

Parameters4/5

Does the description clarify parameter syntax, constraints, interactions, or defaults beyond what the schema provides?

Schema description coverage is only 25% (only `database` is documented), so the description must compensate, and it largely does: low_scan_ratio is defined as a fraction of the table's total scan activity and min_index_size_bytes as a skip threshold. The `schema` parameter is only conveyed implicitly through the worked example rather than explained, which leaves one small gap.

Input schemas describe structure but not intent. Descriptions should explain non-obvious parameter relationships and valid value ranges.

Purpose5/5

Does the description clearly state what the tool does and how it differs from similar tools?

States a specific verb and resource (recommend indexes to drop) and immediately anchors it against the sibling recommend_indexes ('for indexes to remove'), so the add-vs-drop split is unambiguous. It further names the exact catalogs consulted (pg_stat_user_indexes, pg_stat_user_tables) and the output artifact (DROP INDEX CONCURRENTLY statements).

Agents choose between tools based on descriptions. A clear purpose with a specific verb and resource helps agents select the right tool.

Usage Guidelines4/5

Does the description explain when to use this tool, when not to, or what alternatives exist?

The description gives a clear when-to-use framing: large-but-unscanned indexes that are pure cost, with explicit exclusions (primary-key/unique/exclusion-constraint indexes, indexes under min_index_size_bytes) that tell the agent what this tool will not surface. It does not reference adjacent candidates such as find_unused_objects or analyze_table_bloat, so a small amount of routing inference is left to the agent.

Agents often have multiple tools that could apply. Explicit usage guidance like "use X instead of Y when Z" prevents misuse.

Deploy Server