evaluateIndex

Plan a statement as if the indexes were different, using hypopg.

read-onlyany connectioncan return data values

Synopsis

evaluateIndex(sql, [create], [hide], [plan_as_role], [settings])

Description

Plan a statement as if the indexes were different, using hypopg. 'create' takes CREATE INDEX statements to plan against without building them; 'hide' takes the names of existing indexes to plan without, which is how to ask whether an index is safe to drop. Nothing is built, no lock is taken and no catalog row is written, and the statement is never executed -- hypopg cannot serve EXPLAIN ANALYZE, so this is plan-only and safer than explainQuery with analyze.

Returns the plan and total cost before and after, each carrying a Settings block naming the settings that differ from the built-in default -- both halves are planned in THIS server's session, so a per-role work_mem elsewhere makes both costs answer a different question than the one asked; compare against hostCapacity.overrides. Also reports, for each index, whether the planner actually used it, which is the answer that matters: a proposed index the planner ignores is the common case and a cost figure alone hides it.

The cost is the planner's estimate, not a measurement. For a statement recovered from pg_stat_statements, call explainQuery with its queryid first and pass the sql it echoes back. Reports a clear error with setup instructions if hypopg is not installed.

This operation can return values that came from your data. It never changes anything and never returns rows, but read what reaches the caller before connecting it to a database whose contents are sensitive.

Parameters

sql requiredstring
create optionalarray: CREATE INDEX statements to plan against
hide optionalarray: names of existing indexes to plan without
plan_as_role optionalstring: compare under what this role carries in pg_db_role_setting. Both the before and the after plan use it, which is the point: an environment that does not match production makes both costs answer a different question
settings optionalobject: planner settings to apply for both halves of the comparison, e.g. {"work_mem": "512MB"}. Applied with set_config(is_local) so they revert with the transaction. Allowlisted; an unknown name is refused and nothing is planned

Also accepts connection, described once under arguments every tool takes.

Output

How a statement would plan with different indexes. An absent extension or a missing grant is reported as {error, hint} instead.

Field	Type
baseline	object \| null
cost_ratio	number \| null
hidden	array \| null
hypopg_version	string \| null
hypothetical	object \| null
indexes	array \| null
note	string \| null
statement	string \| null

Scope

A physical replica is byte-identical here, so asking each member of a replication group adds nothing.

Example mocked data

Request

{
  "jsonrpc": "2.0",
  "id": 1,
  "method": "tools/call",
  "params": {
    "name": "evaluateIndex",
    "arguments": {
      "sql": "SELECT id FROM shop.orders WHERE status = 'paid' AND created_at > now() - interval '1 day'",
      "create": [
        "CREATE INDEX ON shop.orders (status, created_at)"
      ]
    }
  }
}

Result

{
  "statement": "SELECT id FROM shop.orders WHERE status = 'paid' AND created_at > now() - interval '1 day'",
  "hypopg_version": "1.4.1",
  "baseline": {
    "total_cost": 18420.5,
    "node_types": [
      "Seq Scan"
    ]
  },
  "hypothetical": {
    "total_cost": 42.18,
    "node_types": [
      "Index Scan"
    ]
  },
  "cost_ratio": 0.0023,
  "indexes": [
    {
      "definition": "CREATE INDEX ON shop.orders USING btree (status, created_at)",
      "used": true
    }
  ],
  "hidden": null,
  "note": "Hypothetical indexes are planned against and never built. The cost is the planner's estimate, not a measurement."
}

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.